|
/*
mysql> select * from Bird;
+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| BlueBird | Joe | Car | f | 1999-03-30 | NULL |
| RedBird | Yin | Bus | m | 1979-04-30 | 0000-00-00 |
| RedBird | Yin | Bus | m | 1998-01-30 | NULL |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)
mysql> SELECT name, birth, death,
-> (YEAR(death)-YEAR(birth)) - (RIGHT(death,5)<RIGHT(birth,5))
-> AS age
-> FROM Bird WHERE death IS NOT NULL ORDER BY age;
+---------+------------+------------+-------+
| name | birth | death | age |
+---------+------------+------------+-------+
| RedBird | 1979-04-30 | 0000-00-00 | -1980 |
+---------+------------+------------+-------+
1 row in set (0.00 sec)
*/
Drop table Bird;
CREATE TABLE Bird (
name VARCHAR(20),
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth DATE,
death DATE
);
INSERT INTO Bird VALUES ('BlueBird','Joe','Car','f','1999-03-30',NULL);
INSERT INTO Bird VALUES ('RedBird','Yin','Bus','m','1979-04-30',1998-01-30);
INSERT INTO Bird VALUES ('RedBird','Yin','Bus','m','1998-01-30',NULL);
select * from Bird;
SELECT name, birth, death,
(YEAR(death)-YEAR(birth)) - (RIGHT(death,5)<RIGHT(birth,5))
AS age
FROM Bird WHERE death IS NOT NULL ORDER BY age;
|