如何将行的值展现到列里？

Question

我很不要脸的来当伸手党了，但是实在卡了一会，我有如下这个表：

+----+--------+--------+-------+
| id | name   | date   | Income |
+----+--------+--------+-------+
|  1 | tom    | 20210331 | 100   |
|  2 | tom    | 20210330 | 200   | 
|  3 | james  | 20210331 | 30    |
|  4 | james  | 20210330 | 90    |
+----+--------+--------+-------+ 

想获得这样的结果,能体现出来对比：

+----+--------+---------+-------------+----------+----------------+
| id | name   | today   | todayIncome |yesterday | yesterdayIncome| 
+----+--------+---------+-------------+----------+----------------+
|  1 | tom    | 20210331| 100         | 20210330 | 200            | 
+----+--------+---------+-------------+----------+----------------+ 
|  2 | james  | 20210331| 30          | 20210330 | 90             | 
+----+--------+---------+-------------+----------+----------------+ 

请问这样的mysql语句怎么写... 先跪谢了...

Answer 1

大概是一条这样的 SQL？

SELECT 
n1.id, n1.name, 
n1.date as today, n1.income as todayIncome,
n2.date as yesterday, n2.income as yesterdayIncome
FROM NewTable1 n1, NewTable1 n2
where DATEDIFF(n1.date, n2.date) = 1
and n1.name = n2.name
and n1.date > n2.date 

用 datadiff(...) 函数来让日期跨度为1天。

id	name	today	todayIncome	yesterday	yesterdayIncome
2	tom	2021-03-31	100.0	2021-03-30	200.0
5	tom	2021-04-01	150.0	2021-03-31	100.0
4	james	2021-03-31	30.0	2021-03-30	90.0