MySQL : SELECT multiple with different condition of selection for each

Question

我有一个这样的表:

Date  DATA Country  
2013  3    UK
2013  1    UK
2012  2    UK
2013  5    DE
2012  4    DE

我想做的是:

Country  2012  2013
UK       1     2
DE       1     1

所以在伪代码中我会:

SELECT country, COUNT(DATA) WHERE Date = 2013, COUNT(DATA) WHERE Date=2012 
FROM table 
GROUP BY Country

我知道这是不正确的，但我想知道该怎么做。

Answer 1

您可以在聚合函数中使用 CASE 表达式将数据行转换为列:

SELECT country, 
  sum(case when date = 2012 then 1 else 0 end) `2012`,
  sum(case when date = 2013 then 1 else 0 end) `2013`
FROM yourtable 
GROUP BY Country;

参见 SQL Fiddle with Demo .

如果你有一个未知的年数，或者你想实现一个日期范围，那么你可以使用一个准备好的语句来生成动态 SQL:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(CASE WHEN date = ',
      date,
      ' THEN 1 else 0 END) AS `',
      date, '`'
    )
  ) INTO @sql
FROM yourtable;

SET @sql 
  = CONCAT('SELECT country, ', @sql, ' 
            from yourtable
            group by country');

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

参见 SQL Fiddle with Demo