写一段SQL语句要求是能计算李贺何李白的年龄,并且计算出两个值的差
时间: 2023-12-10 10:37:32 浏览: 30
假设有一个名为`poet`的表,其中包含诗人的姓名和出生年份(`birth_year`)字段,可以使用以下SQL语句计算李贺和李白的年龄,并计算两者年龄的差:
```
SELECT
'李贺' AS poet_name,
YEAR(NOW()) - birth_year AS poet_age
FROM poet
WHERE poet_name = '李贺'
UNION
SELECT
'李白' AS poet_name,
YEAR(NOW()) - birth_year AS poet_age
FROM poet
WHERE poet_name = '李白';
```
这将返回一个类似于以下内容的结果:
```
+----------+----------+
| poet_name| poet_age |
+----------+----------+
| 李贺 | 138|
| 李白 | 133|
+----------+----------+
```
要计算两个年龄之间的差,请使用以下SQL语句:
```
SELECT
ABS(p1.poet_age - p2.poet_age) AS age_difference
FROM
(SELECT
YEAR(NOW()) - birth_year AS poet_age
FROM poet
WHERE poet_name = '李贺') AS p1,
(SELECT
YEAR(NOW()) - birth_year AS poet_age
FROM poet
WHERE poet_name = '李白') AS p2;
```
这将返回一个包含两个年龄之间差值的行:
```
+----------------+
| age_difference |
+----------------+
| 5|
+----------------+
```