按月从2个表和组中获取计数

小编典典

按月从2个表和组中获取计数

sql

我这里有2个基本上具有相同结构的表。这是结构。

---------------------------
| 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
---------------------------

我只想计算这些数据，预期结果是：

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

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

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

请帮我。

阅读 146

2021-05-16

共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

另外，要按月对结果进行排序，您还需要使用以下子查询：

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;

看到这个SQLFiddle

2021-05-16