基本上，我有一个mysql数据库表，其中包含一个日期时间列和一个类别列。我想创建一个SQL查询来检索类别列中存在的所有值，并计算按datetime列的月/年分组的每个类别值出现的次数。如果可能的话，我还想把总数退回来。一个月内所有事件的总数和计算的类别总数。
注意:类别值不能硬编码，因为它们是由用户设置并存储在另一个表中。
数据库表的结构如下:
datetime | category
2009-01-05 | fish
2009-01-06 | fish
2009-01-06 | potato
2009-01-16 | fish
2009-02-08 | pineapple
2009-02-15 | potato
我希望从查询返回的结果是:
Month | fish | potato | pineapple | total
2009-01 | 3 | 1 | 0 | 4
2009-02 | 0 | 1 | 1 | 2
Total | 3 | 2 | 1 | 6
我认为(希望)它可以在一个单一的SQL查询，但我不知道如何。有人能帮我吗?
谢谢!
让我首先说，我认为这更像是在您的表示逻辑(php代码)中处理的问题。但是，SQL可以产生这样的结果。你在尝试完成两件不同的事情。
首先，您要查找PIVOT表。MySQL不支持PIVOT命令，但可以用MAX和CASE来模拟。当你知道潜在类别的数量时，这种方法很有效，但在你的情况下就行不通了。
接下来，您希望获得行总数，然后是最终的总行。同样，这更适合在表示层处理。
然而，使用Dynamic SQL，您可以实现PIVOT表和行总数。下面是一些示例代码:
首先构建PIVOT变量@sql:
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'COUNT(IF(category = ''', category, ''',1,NULL)) AS ', category)
) INTO @sql
FROM (
SELECT *,
@rn:=IF(@prevMonthYear=CONCAT(YEAR(datetime),'-',MONTH(datetime)),@rn+1,1) rn,
@prevMonthYear:=CONCAT(YEAR(datetime),'-',MONTH(datetime)) dt
FROM yourtable JOIN (SELECT @rn:=0,@prevParent:=0) t
) t
;
现在构建行摘要变量@totsql:
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'SUM(', category, ') AS sum_', category)
) INTO @totsql
FROM (
SELECT *,
@rn:=IF(@prevMonthYear=CONCAT(YEAR(datetime),'-',MONTH(datetime)),@rn+1,1) rn,
@prevMonthYear:=CONCAT(YEAR(datetime),'-',MONTH(datetime)) dt
FROM yourtable JOIN (SELECT @rn:=0,@prevParent:=0) t
) t
;
把它们放在一起:
SET @sql = CONCAT('SELECT dt,
', @sql, ', COUNT(1) total
FROM (
SELECT *,
@rn:=IF(@prevMonthYear=CONCAT(YEAR(datetime),''-'',MONTH(datetime)),@rn+1,1) rn,
@prevMonthYear:=CONCAT(YEAR(datetime),''-'',MONTH(datetime)) dt
FROM yourtable JOIN (SELECT @rn:=0,@prevParent:=0) t
) t
GROUP BY dt
UNION
SELECT ''Totals'',', @totsql, ', SUM(total)
FROM (
SELECT dt,
', @sql, ', COUNT(1) total
FROM (
SELECT *,
@rn:=IF(@prevMonthYear=CONCAT(YEAR(datetime),''-'',MONTH(datetime)),@rn+1,1) rn,
@prevMonthYear:=CONCAT(YEAR(datetime),''-'',MONTH(datetime)) dt
FROM yourtable JOIN (SELECT @rn:=0,@prevParent:=0) t
) t
GROUP BY dt
) t2
;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
- SQL Fiddle Demo
结果:
MONTH FISH POTATO PINEAPPLE TOTAL
2009-1 3 1 0 4
2009-2 0 1 1 2
Totals 3 2 1 6
你可以有多个嵌套查询，或者在这种情况下你可以使用mysql循环。最简单的方法是获取所需的所有数据并使用php进行处理。