MySQL新版本已經支持row_number了
但推測大大應該是舊版,所以提供舊版本變數模擬方式
select
`mathinelD`,
## 藉由rank已經篩選出唯一值 所以用min取得值就可以
min(case when rank = 1 then `valueA` end) as `valueA1`,
min(case when rank = 2 then `valueA` end) as `valueA2`,
min(case when rank = 3 then `valueA` end) as `valueA3`,
min(case when rank = 4 then `valueA` end) as `valueA4`,
min(case when rank = 1 then `valueB` end) as `valueB1`,
min(case when rank = 2 then `valueB` end) as `valueB2`,
min(case when rank = 3 then `valueB` end) as `valueB3`,
min(case when rank = 4 then `valueB` end) as `valueB4`
from (
## MySQL舊版本,藉由變數 + case 比較,模擬出Row_Number over功能
SELECT T1.*,
CASE `mathinelD`
WHEN @GroupCol1 THEN @curRow := @curRow + 1
ELSE @curRow := 1
END AS rank
,@GroupCol1 := `mathinelD`
FROM T T1
JOIN (SELECT @curRow := 0, @GroupCol1 := '') r
ORDER BY `mathinelD`
) temT
group by `mathinelD`
mathinelD | valueA1 | valueA2 | valueA3 | valueA4 | valueB1 | valueB2 | valueB3 | valueB4 |
---|---|---|---|---|---|---|---|---|
00001 | 11 | 31 | 51 | 61 | 12 | 32 | 52 | 62 |
00002 | 21 | 41 | (null) | (null) | 22 | 42 | (null) | (null) |
CREATE TABLE T
(`id` int, `mathinelD` varchar(7), `valueA` int, `valueB` int, `timestamp` datetime)
;
INSERT INTO T
(`id`, `mathinelD`, `valueA`, `valueB`, `timestamp`)
VALUES
(1, '00001', 11, 12, '2018-07-31 16:00:00'),
(2, '00002', 21, 22, '2018-07-31 16:00:00'),
(3, '00001', 31, 32, '2018-07-31 16:15:00'),
(4, '00002', 41, 42, '2018-07-31 16:15:00'),
(5, '00001', 51, 52, '2018-07-31 16:30:00'),
(6, '00001', 61, 62, '2018-07-31 16:45:00')
;
補充一下,假如mysql環境支持動態sql的話,可以在優化,不用自己算欄位數。
你先看一下,有問題跟我說
醒醒吧
你根本沒有 Group By