MYSQL多行合并成一行多列

最新推荐文章于 2023-02-02 12:22:56 发布

weixin_34104341

最新推荐文章于 2023-02-02 12:22:56 发布

阅读量1.4k

点赞数

文章标签：数据库

原数据：
id      code           name            value
         -----------      ----------        --------  
1      2014000          A                       10
2      2014000          B                       9
3      2014001          C                       100
4      2014002          D                       4
5      2014002          E                        5 
6      2014003          F                       9
7     2014003          G                      10 

期望结果：
          code           name1            value1           name2            value2
         -----------       ---------              ---------          ---------              ---------   
         2014000          A                      10                   B                   9
         2014001          C                     100               null               null                                         
         2014002         D                       4                   E                     5
         2014003         F                       9                   G                   10



注意：同1个ID最多只2条记录，会出现相同value。



CREATE TABLE # (ID INT, code INT, NAME CHAR(1), VALUE VARCHAR(10))
INSERT INTO # VALUES
(1,2014000,'A','10'),
(2,2014000,'B','9'),
(3,2014001,'C','100'),
(4,2014002,'D','4'),
(5,2014002,'E','5'),
(6,2014003,'F','9'),
(7,2014003,'G','10')
 
SELECT
    code,MIN(name) AS name1,MIN(CAST(REPLACE(value,' ','') AS INT)) AS value1,
    CASE COUNT(ID) WHEN 1 THEN NULL ELSE MAX(name) END AS name2,
    CASE COUNT(ID) WHEN 1 THEN NULL ELSE MAX(CAST(REPLACE(value,' ','') AS INT)) END AS value2
FROM
    #
GROUP BY
    code