MySQL查询返回多个总计列 [英] MySQL query to return multiple summed columns

问题描述

  uid用户类别计数日期
 ----我的SQL表中有以下数据。 ---------------------------------------------- 
 1 henry RED 4 2013-05-01 
 2 john蓝色3 2013-05-01 
 3 alice RED 5 2013-05-01 
 4 eric GREEN 2 2013-05-02 
 5 eric BLUE 2 2013-05-02 
 6 john RED 3 2013-05-02 
 7 henry GREEN 2 2013-05-02 
 8 eric RED 3 2013-05-03 
 9 john BLUE 5 2013-05-03 
  

我想要一个查询给我返回以下数据

 类别2013-05-01 2013-05-02 2013-05-03 
  - ------------------------------------------------- 
红色9 3 3 
蓝色3 2 5 
绿色0 4 0 
  

我可以通过以下查询获得一列数据，但我不知道如何获取如上所示将多列分组。

  SELECT类别，SUM（count）我想要一个查询，我的PHP呈现，而不是循环遍历每个日期。 
 FROM dbtable 
 WHERE date ='2013-05-01'
 GROUP BY类别; 
  

这可能吗？想法？

解决方案

> 选择
类别，
SUM（CASE WHEN date ='2013-05-01'THEN END END）AS`2013-05-01`，
SUM CASE WHEN date ='2013-05-02'THEN count END）as`2013-05-02`，
SUM（CASE WHEN date ='2013-05-03'THEN END END）AS`2013-05 -03`
FROM dbtable
GROUP BY类别


I have the following data in my SQL table.

uid    user    category    count    date
--------------------------------------------------
1      henry   RED         4        2013-05-01
2      john    BLUE        3        2013-05-01
3      alice   RED         5        2013-05-01
4      eric    GREEN       2        2013-05-02
5      eric    BLUE        2        2013-05-02
6      john    RED         3        2013-05-02
7      henry   GREEN       2        2013-05-02
8      eric    RED         3        2013-05-03
9      john    BLUE        5        2013-05-03

I would like a query that gives me back the following data

category    2013-05-01    2013-05-02    2013-05-03
---------------------------------------------------
RED         9             3             3
BLUE        3             2             5
GREEN       0             4             0

I can get one column of data with the following query, but I do not know how to get multiple columns grouped as shown above. I'd like to have one query that my PHP renders instead of looping through each date.

SELECT category, SUM(count) 
FROM dbtable 
WHERE date='2013-05-01' 
GROUP BY category;

Is this possible? Ideas?

解决方案

Something like this should work:

SELECT
  category,
  SUM(CASE WHEN date = '2013-05-01' THEN count END) AS `2013-05-01`,
  SUM(CASE WHEN date = '2013-05-02' THEN count END) AS `2013-05-02`,
  SUM(CASE WHEN date = '2013-05-03' THEN count END) AS `2013-05-03`
FROM dbtable
GROUP BY category

这篇关于MySQL查询返回多个总计列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！