假設我有下表：
| ID | 用戶身份 | 等級 | 創建日期 |
|---|---|---|---|
| 1 | 1 | 2 | 2019-07-01T18:53:41.000Z |
| 2 | 1 | 3 | 2020-07-01T18:53:41.000Z |
| 3 | 1 | 4 | 2021-07-01T18:53:41.000Z |
| 4 | 2 | 2 | 2020-07-01T18:53:41.000Z |
假設我想level根據創建日期在一行中獲取過去 3 年中每一年的用戶。
就像是：
SELECT user_id, (some query here) AS "level_2019"
, (some query here) AS "level_2020", (some query here) AS "level_2021"
FROM table
這對于單個選擇陳述句來說是可能的嗎？如果他們那一年沒有水平，我也希望那一年的水平為空。
謝謝！
uj5u.com熱心網友回復：
由于動態別名，您需要 dynamuc sql 進行此類查詢
CREATE TABLE table1 ( `id` INTEGER, `user_id` INTEGER, `level` INTEGER, `date_created` varchaR(24) );
INSERT INTO table1 (`id`, `user_id`, `level`, `date_created`) VALUES ('1', '1', '2', '2019-07-01T18:53:41.000Z'), ('2', '1', '3', '2020-07-01T18:53:41.000Z'), ('3', '1', '4', '2021-07-01T18:53:41.000Z'), ('4', '2', '2', '2020-07-01T18:53:41.000Z');
SET @column_alias1 := CONCAT('level_',YEAR(NOW() - INTERVAL 3 Year)); SET @column_alias2 := CONCAT('level_',YEAR(NOW() - INTERVAL 2 Year)); SET @column_alias3 := CONCAT('level_',YEAR(NOW() - INTERVAL 1 Year)); SET @query := CONCAT('SELECT `user_id`, MIN( IF (YEAR(CAST(`date_created` AS DATETIME)) = YEAR( NOW() - INTERVAL 3 YEAR), `level`,NULL)) as `', @column_alias1, '` ,MIN( IF (YEAR(CAST(`date_created` AS DATETIME)) = YEAR( NOW() - INTERVAL 2 YEAR), `level`,NULL)) as `', @column_alias2, '` ,MIN( IF (YEAR(CAST(`date_created` AS DATETIME)) = YEAR( NOW() - INTERVAL 1 YEAR), `level`,NULL)) as `', @column_alias3, '` FROM table1 GROUP BY `user_id`'); PREPARE dynamic_statement FROM @query; EXECUTE dynamic_statement
用戶 ID | level_2019 | level_2020 | level_2021
------: | ---------: | ---------: | ---------:
1 | 2 | 3 | 4
2 | 空| 2 | 空值
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/422030.html
標籤：