PHP mySQL 查询返回预先排序的列表并计算字段值的出现次数(即使不存在)

Question

基本上，我有一个 mysql 数据库表，其中包含一个日期时间列和一个类别列。我想创建一个 SQL 查询来检索类别列中存在的所有值，并计算按日期时间列的月/年分组的每个类别值出现的次数。如果可能的话，我还希望返回总数。统计一个月内所有发生次数和类别总数。

注意:类别值无法进行硬编码，因为它们是由用户设置并存储在另一个表中。

数据库表具有以下结构:

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 查询中完成，但我不知道如何完成。谁能帮我吗？

谢谢!

Answer 1

首先让我说，我认为这感觉更像是一个需要在表示逻辑(php 代码)中处理的问题。然而，SQL 可以产生这样的结果。您正在尝试完成两件不同的事情。

首先，您要查找PIVOT 表。 MySQL不支持PIVOT命令，但您可以使用MAX和CASE来模拟它。当您知道潜在类别的数量时，这很有效，但在您的情况下不起作用。

接下来，您希望获得行总计，然后是最终总计行。 同样，这更适合在表示层中处理。

但是，使用动态 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