mysql - 根据星期几列枢纽MySQL表格

以db格式保存数据

---------------------------------------------
count | margin |    date    | week | day    |
---------------------------------------------
 230  |   140  | 2013-01-01 |  0   | Monday  |
----------------------------------------------
 500  |   340  | 2013-01-02 |  0   | Tuesday  |
----------------------------------------------
 200  |   240  | 2013-01-08 |  1   | Monday  |
----------------------------------------------

要求输出为

-----------------------------------------------------------
|   week  |   monday  |  tuesday  |  wednesday  | .........
-----------------------------------------------------------
|    0    |  230:140  |  500:340  |
-----------------------------------------------------------
|    1    |  200:240  |
-----------------------------------------------------------

如有任何帮助，我们将不胜感激

最佳答案

尝试此静态查询：

SELECT Week
    ,GROUP_CONCAT(CASE WHEN day = 'Monday'
                       THEN CONCAT(count,':',margin) ELSE NULL END) AS Monday
    ,GROUP_CONCAT(CASE WHEN day = 'Tuesday'
                       THEN CONCAT(count,':',margin) ELSE NULL END) AS Tuesday
    ,GROUP_CONCAT(CASE WHEN day = 'Wednesday'
                       THEN CONCAT(count,':',margin) ELSE NULL END) AS Wednesday
    ,GROUP_CONCAT(CASE WHEN day = 'Thursday'
                       THEN CONCAT(count,':',margin) ELSE NULL END) AS Thursday
    ,GROUP_CONCAT(CASE WHEN day = 'Friday'
                       THEN CONCAT(count,':',margin) ELSE NULL END) AS Friday
    ,GROUP_CONCAT(CASE WHEN day = 'Saturday'
                       THEN CONCAT(count,':',margin) ELSE NULL END) AS Saturday
    ,GROUP_CONCAT(CASE WHEN day = 'Sunday'
                       THEN CONCAT(count,':',margin) ELSE NULL END) AS Sunday
FROM MyTable
GROUP BY Week

也可以使用此动态查询：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'GROUP_CONCAT(CASE WHEN `day` = ''',
      `day`,
      ''' THEN CONCAT(count,'':'',margin) ELSE NULL END) AS `',
      `day`, '`'
    )
  ) INTO @sql
FROM MyTable;

SET @sql = CONCAT('SELECT Week, ', @sql,'
                     FROM MyTable
                    GROUP BY Week
                  ');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

见this SQLFiddle

关于mysql - 根据星期几列枢纽MySQL表格，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/17760057/