php - MySql 在同一列中查询不同日期

Question

请考虑:我正在谈论 MySQL 数据库

我有两个这样的表:

tbl_stock_info
+-----+--------+
| sId | sName  |
+-----+--------+
|   1 | Apple  |
|   2 | Google |
|   3 | Yahoo  |
+-----+--------+

tbl_stock_data
+------------+----------+-------------+
|    date    | stock_id | stock_price |
+------------+----------+-------------+
| 2017-01-25 |        1 | 44.7        |
| 2017-01-25 |        3 | 51          |
| 2017-01-25 |        2 | 71.5        |
| 2017-01-24 |        1 | 44.9        |
| 2017-01-24 |        3 | 51.2        |
| 2017-01-24 |        2 | 71.3        |
+------------+----------+-------------+

我正在寻找的输出是这样的:

+-----+--------+----------+----------+
| sId | sName  | Price_25 | Price_24 |
+-----+--------+----------+----------+
|   1 | Apple  | 44.7     | 44.9     |
|   2 | Google | 71.5     | 71.3     |
|   3 | Yahoo  | 51       | 51.2     |
+-----+--------+----------+----------+

任何帮助将不胜感激。

Answer 1

您可以使用这样的查询来完成此操作。它搜索表中最新的 2 个日期并生成查询，但列名称是固定的。如果您还想更改它们，则必须使用准备好的语句。

SELECT 
    si.*
    ,SUM(if(sd1.`date` = ( SELECT DISTINCT `date` FROM tbl_stock_data ORDER BY `date` DESC LIMIT 0,1), sd1.stock_price ,0)  ) as lastday
    ,SUM(if(sd1.`date` = ( SELECT DISTINCT `date` FROM tbl_stock_data ORDER BY `date` DESC LIMIT 1,1), sd1.stock_price ,0)  ) as daybefore
FROM tbl_stock_info si
LEFT JOIN tbl_stock_data sd1 ON sd1.stockid = si.sId
GROUP BY si.sId;

示例

MariaDB [l]> SELECT * FROM tbl_stock_info;
+-----+--------+
| sId | sNAme  |
+-----+--------+
|   1 | Apple  |
|   2 | Google |
|   3 | Yahoo  |
+-----+--------+
3 rows in set (0.00 sec)

MariaDB [l]> SELECT * FROM tbl_stock_data;
+----+------------+---------+-------------+
| id | date       | stockid | stock_price |
+----+------------+---------+-------------+
|  1 | 2017-01-25 |       1 | 44.70       |
|  2 | 2017-01-25 |       3 | 51.00       |
|  3 | 2017-01-25 |       2 | 71.50       |
|  4 | 2017-01-24 |       1 | 44.90       |
|  5 | 2017-01-24 |       3 | 51.20       |
|  6 | 2017-01-24 |       2 | 71.30       |
|  7 | 2017-01-23 |       1 | 99.00       |
|  8 | 2017-01-22 |       2 | 22.00       |
|  9 | 2017-01-20 |       3 | 33.13       |
+----+------------+---------+-------------+
9 rows in set (0.01 sec)

测试

MariaDB [l]> SELECT
    ->     si.*
    ->     ,SUM(if(sd1.`date` = ( SELECT DISTINCT `date` FROM tbl_stock_data ORDER BY `date` DESC LIMIT 0,1), sd1.stock_price ,0)  ) as lastday
    ->     ,SUM(if(sd1.`date` = ( SELECT DISTINCT `date` FROM tbl_stock_data ORDER BY `date` DESC LIMIT 1,1), sd1.stock_price ,0)  ) as daybefore
    -> FROM tbl_stock_info si
    -> LEFT JOIN tbl_stock_data sd1 ON sd1.stockid = si.sId
    -> GROUP BY si.sId;
+-----+--------+---------+-----------+
| sId | sNAme  | lastday | daybefore |
+-----+--------+---------+-----------+
|   1 | Apple  | 44.70   | 44.90     |
|   2 | Google | 71.50   | 71.30     |
|   3 | Yahoo  | 51.00   | 51.20     |
+-----+--------+---------+-----------+
3 rows in set (0.00 sec)

MariaDB [l]>