MySQL-Count和GroupBy [英] MySQL - Count and GroupBy

问题描述

我有这样的结构:

id|  date_1  |  date_2
---------------------
01|2017-01-01|2017-02-22
02|2017-01-02|2017-03-25
03|2017-02-10|2017-03-20
04|2017-03-11|2017-04-10
05|2017-03-15|2017-05-01
06|2017-03-20|2017-05-20

我需要这种结果:

Month  |Count(date_1)|Count(date_2)
---------------------------------
2017-01| 2           | 0
2017-02| 1           | 1
2017-03| 3           | 2
2017-04| 0           | 1
2017-05| 0           | 2

现在，我使用此查询(仅适用于一个日期):

Now, I use this query (it works with only one date):

SELECT CONCAT(YEAR(date_1), '-', DATE_FORMAT(date_1,'%m')) AS month,
COUNT(*) AS items
FROM table
GROUP BY YEAR(date_1), MONTH(date_1)
ORDER BY date_1 DESC

推荐答案

考虑使用SELECT

SELECT   distinct DATE_FORMAT(t.d, '%y-%m'),
     (
        SELECT count(*) 
        FROM your_table as dd 
        where DATE_FORMAT(dd.date_1, '%y-%m') = DATE_FORMAT(t.d, '%y-%m')
     ) as count_date_1,
     (
        SELECT count(*) 
        FROM your_table as dd 
        WHERE DATE_FORMAT(dd.date_2, '%y-%m') = DATE_FORMAT(t.d, '%y-%m')
     ) as count_date_2
FROM     
(
  SELECT date_1 AS d FROM your_table 
      UNION ALL
  SELECT date_2 as d FROM your_table
) as t

dbfiddle演示

这篇关于MySQL-Count和GroupBy的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！