0

## group by 能否保留原欄位的值

### 2 個回答

5

iT邦大師 1 級 ‧ 2018-08-01 13:33:51

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)

### 測試DDL:

``````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')
;
``````

### SQL Fiddle

wei225890 iT邦新手 5 級 ‧ 2018-08-01 14:05:34 檢舉

0

iT邦超人 1 級 ‧ 2018-08-01 12:05:30

wei225890 iT邦新手 5 級 ‧ 2018-08-01 14:06:27 檢舉