mysql 将 group by 中的每一行合并起来

Question

我有一个这样的表:

id   weekday   money   rank
---------------------------
1    0         20      1
2    1         30      1
3    2         40      1
4    3         50      1
5    4         60      1
6    5         70      1
7    6         80      1

我想要得到这个结果:

rank   Sun   Mon   Tue   Wed   Thu   Fri   Sat
----------------------------------------------
1      20    30    40    50    60    70    80

到目前为止我的查询:

SELECT rank,
    CASE WHEN weekday = 0 THEN money END AS 'Sun',
    CASE WHEN weekday = 1 THEN money END AS 'Mon',
    CASE WHEN weekday = 2 THEN money END AS 'Tue',
    CASE WHEN weekday = 3 THEN money END AS 'Wed',
    CASE WHEN weekday = 4 THEN money END AS 'Thu',
    CASE WHEN weekday = 5 THEN money END AS 'Fri',
    CASE WHEN weekday = 6 THEN money END AS 'Sat'
FROM weekday
GROUP BY rank

此查询仅显示太阳钱。如何修改 MySQL 查询以获得所需的结果？

Answer 1

请检查:

select rank,sum(sun) as sun, 
        sum(mon) as Mon,sum(tue) as tue, 
        sum(wed) as wed, sum(thu) as thu ,
        sum(fri) as Fri, sum(sat) as sat 
    from 
        (SELECT rank, 
            CASE WHEN weekday = 0 THEN money END AS 'Sun', 
            CASE WHEN weekday = 1 THEN money END AS 'Mon', 
            CASE WHEN weekday = 2 THEN money END AS 'Tue', 
            CASE WHEN weekday = 3 THEN money END AS 'Wed', 
            CASE WHEN weekday = 4 THEN money END AS 'Thu', 
            CASE WHEN weekday = 5 THEN money END AS 'Fri', 
            CASE WHEN weekday = 6 THEN money END AS 'Sat' 
        FROM weekday where rank =1) 
    as Tbl2
    group by rank