从 2 个表中获取计数并按月分组 [英] Getting count from 2 table and group by month

问题描述

我这里有 2 张表，基本上结构相同.这是结构.

I had 2 tables here with same structure basically. Here's the structure.

---------------------------
| Table In                 
---------------------------
| Id    | Date
---------------------------
| 1     | 2013-05-22
| 2     | 2013-07-20
---------------------------


---------------------------
| Table Out                 
---------------------------
| Id    | Date
---------------------------
| 1     | 2013-05-20
| 2     | 2013-06-21
| 3     | 2013-07-24
---------------------------

我只是想统计一下这个数据，预期的结果是:

I just want to count this data and the expected results is :

----------------------------------------------
| month   | countin       | countout
----------------------------------------------
| 5       | 1             | 1
| 6       | 0             | 1
| 7       | 1             | 1

但是，当我尝试使用此查询时:

But, when I try with this query :

SELECT month(date) AS `month`, count(*) AS `countin`,
       (SELECT count(*)
        FROM `out`
        WHERE month(date) = `month`) AS `countout`
FROM `in`
GROUP BY `month`

结果是:

----------------------------------------------
| month   | countin       | countout
----------------------------------------------
| 5       | 1             | 1
| 7       | 1             | 1

请帮帮我.

推荐答案

用月份连接两个表:

SELECT MONTH(I.date) AS `month`
     , COUNT(I.ID) AS `countin`
     , COUNT(O.ID) AS `countOUT`
  FROM TableIN I
 LEFT JOIN TableOUT O
    ON MONTH(I.Date) = MONTH(O.Date)
 GROUP BY MONTH(I.date)
UNION
SELECT MONTH(O.date) AS `month`
     , COUNT(I.ID) AS `countin`
     , COUNT(O.ID) AS `countOUT`
  FROM TableIN I
 RIGHT JOIN TableOUT O
    ON MONTH(I.Date) = MONTH(O.Date)
 GROUP BY MONTH(I.date);

结果:

| MONTH | COUNTIN | COUNTOUT |
------------------------------
|     5 |       1 |        1 |
|     7 |       1 |        1 |
|     6 |       0 |        1 |

参见这个 SQLFiddle

还要按月对结果进行排序，您需要使用这样的子查询:

See this SQLFiddle

Also to order your result by month you need to use a sub-query like this:

SELECT * FROM
(
    SELECT MONTH(I.date) AS `month`
         , COUNT(I.ID) AS `countin`
         , COUNT(O.ID) AS `countOUT`
      FROM TableIN I
     LEFT JOIN TableOUT O
        ON MONTH(I.Date) = MONTH(O.Date)
     GROUP BY MONTH(I.date)
    UNION
    SELECT MONTH(O.date) AS `month`
         , COUNT(I.ID) AS `countin`
         , COUNT(O.ID) AS `countOUT`
      FROM TableIN I
     RIGHT JOIN TableOUT O
        ON MONTH(I.Date) = MONTH(O.Date)
     GROUP BY MONTH(I.date)
    ) tbl
ORDER BY Month;

这篇关于从 2 个表中获取计数并按月分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！