美好的一天親愛的。請幫我查詢資料庫。
圖式
架構 (MySQL v8.0)
create table TEST_TABLE (
DT date,
OBJECT_ID NUMERIC,
PARAM_VALUE NUMERIC
);
insert TEST_TABLE (DT, OBJECT_ID, PARAM_VALUE) values ('2022-01-01',1, 21);
insert TEST_TABLE (DT, OBJECT_ID, PARAM_VALUE) values ('2022-01-02',1, 34);
insert TEST_TABLE (DT, OBJECT_ID, PARAM_VALUE) values ('2022-01-03',1, 78);
insert TEST_TABLE (DT, OBJECT_ID, PARAM_VALUE) values ('2022-01-04',1, 11);
insert TEST_TABLE (DT, OBJECT_ID, PARAM_VALUE) values ('2022-01-05',1, 26);
insert TEST_TABLE (DT, OBJECT_ID, PARAM_VALUE) values ('2022-01-06',1, 11);
insert TEST_TABLE (DT, OBJECT_ID, PARAM_VALUE) values ('2022-01-07',1, 12);
insert TEST_TABLE (DT, OBJECT_ID, PARAM_VALUE) values ('2022-01-08',1, 74);
insert TEST_TABLE (DT, OBJECT_ID, PARAM_VALUE) values ('2022-01-01',2, 18);
insert TEST_TABLE (DT, OBJECT_ID, PARAM_VALUE) values ('2022-01-02',2, 96);
insert TEST_TABLE (DT, OBJECT_ID, PARAM_VALUE) values ('2022-01-03',2, 33);
insert TEST_TABLE (DT, OBJECT_ID, PARAM_VALUE) values ('2022-01-04',2, 77);
insert TEST_TABLE (DT, OBJECT_ID, PARAM_VALUE) values ('2022-01-05',2, 55);
insert TEST_TABLE (DT, OBJECT_ID, PARAM_VALUE) values ('2022-01-06',2, 28);
insert TEST_TABLE (DT, OBJECT_ID, PARAM_VALUE) values ('2022-01-07',2, 54);
insert TEST_TABLE (DT, OBJECT_ID, PARAM_VALUE) values ('2022-01-08',2, 16);
查詢 #1
select * from TEST_TABLE;
| DT | OBJECT_ID | PARAM_VALUE |
|---|---|---|
| 2022-01-01 | 1 | 21 |
| 2022-01-02 | 1 | 34 |
| 2022-01-03 | 1 | 78 |
| 2022-01-04 | 1 | 11 |
| 2022-01-05 | 1 | 26 |
| 2022-01-06 | 1 | 11 |
| 2022-01-07 | 1 | 12 |
| 2022-01-08 | 1 | 74 |
| 2022-01-01 | 2 | 18 |
| 2022-01-02 | 2 | 96 |
| 2022-01-03 | 2 | 33 |
| 2022-01-04 | 2 | 77 |
| 2022-01-05 | 2 | 55 |
| 2022-01-06 | 2 | 28 |
| 2022-01-07 | 2 | 54 |
| 2022-01-08 | 2 | 16 |
查詢 #2
set @TARGET_DATE = '2022-01-08';
select
OBJECT_ID,
group_concat(if(DT = DATE_ADD(@TARGET_DATE, interval -1 day) , PARAM_VALUE, null)) as `dt-1`,
group_concat(if(DT = DATE_ADD(@TARGET_DATE, interval -2 day) , PARAM_VALUE, null)) as `dt-2`
from TEST_TABLE group by OBJECT_ID;
| OBJECT_ID | dt-1 | dt-2 |
|---|---|---|
| 1 | 12 | 11 |
| 2 | 54 | 28 |
我想收到列名中包含日期的結果
| OBJECT_ID | 2022-01-07 | 2022-01-06 |
|---|---|---|
| 1 | 12 | 11 |
| 2 | 54 | 28 |
對不起我的英語
uj5u.com熱心網友回復：
正如Akina所說，準備好的宣告：
SET @TARGET_DATE = '2022-01-08';
SELECT GROUP_CONCAT(DISTINCT CONCAT(
'GROUP_CONCAT(IF(DT="',DT,'", PARAM_VALUE, NULL)) AS "',DT,'"')
ORDER BY DT DESC
SEPARATOR ', \r\n' )
INTO @columns
FROM TEST_TABLE
WHERE DT >= DATE_ADD(@TARGET_DATE, interval -2 day)
AND DT <= DATE_ADD(@TARGET_DATE, interval -1 day);
SELECT CONCAT('SELECT OBJECT_ID,',@columns,'
FROM TEST_TABLE
GROUP BY OBJECT_ID;') INTO @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
對此的最終查詢將與您最初的嘗試不同，但它基于它：
SELECT OBJECT_ID,
GROUP_CONCAT(IF(DT="2022-01-07", PARAM_VALUE, NULL)) AS "2022-01-07",
GROUP_CONCAT(IF(DT="2022-01-06", PARAM_VALUE, NULL)) AS "2022-01-06"
FROM TEST_TABLE
GROUP BY OBJECT_ID;
我故意將創建@columns和最終@sql查詢分開，希望它更容易理解，但是一旦掌握了它，就可以對準備好的陳述句使用單個查詢。
演示小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/417217.html
標籤：
下一篇：為什么程式沒有輸出？