mysql - 从具有组的多个表中计数

Question

我有两张 table 。第一张 table 登记入学。第二个有导出，如下图:

 Table 1: Admissions
+----------+---------------------+---------+
| entry_id | join_date           | name    |
+----------+---------------------+---------+
|       26 | 2017-01-01 00:00:00 | James   |
|       29 | 2017-01-01 00:00:00 | Jan     |
|       27 | 2017-01-01 00:00:00 | Chris   |
|       28 | 2017-01-01 00:00:00 | Mary    |
|       22 | 2017-01-02 00:00:00 | Anna    |
|       21 | 2017-01-02 00:00:00 | Andy    |
|       24 | 2017-01-02 00:00:00 | Bob     |
|       20 | 2017-01-04 00:00:00 | Alice   |
|       23 | 2017-01-04 00:00:00 | Chris   |
|       25 | 2017-01-04 00:00:00 | Happy   |
+----------+---------------------+---------+

Table 2: Exits
+----------+---------------------+----------+
| entry_id | exit_date           | name     |
+----------+---------------------+----------+
|      322 | 2017-01-01 00:00:00 | Kay      |
|      344 | 2017-01-01 00:00:00 | Agnes    |
|      920 | 2017-01-02 00:00:00 | Andre    |
|      728 | 2017-01-02 00:00:00 | Mark     |
|      583 | 2017-01-03 00:00:00 | Alsta    |
|      726 | 2017-01-03 00:00:00 | Bull     |
|      816 | 2017-01-03 00:00:00 | Jane     |
|      274 | 2017-01-04 00:00:00 | Jack     |
|      723 | 2017-01-04 00:00:00 | Anna     |
|      716 | 2017-01-04 00:00:00 | Bill     |
+----------+---------------------+----------+

我正在寻找一种解决方案，以了解按日期分组的录取人数、退出人数和余额。

我正在找这个>

+---------------------+--------+--------+-----------+
| date                | joins  | exist  | net       |
+---------------------+--------+--------+-----------+
| 2017-01-01 00:00:00 |      4 |      2 |         2 |
| 2017-01-02 00:00:00 |      3 |      2 |         1 |
| 2017-01-03 00:00:00 |      0 |      3 |        -3 |
| 2017-01-04 00:00:00 |      3 |      3 |         0 |
+---------------------+--------+--------+-----------+

注意:可能有几天有入场，但没有登记出场，反之亦然。

Answer 1

给你:

SELECT
  d,
  SUM(CASE WHEN t = 'j' THEN 1 ELSE 0 END) as joins,
  SUM(CASE WHEN t = 'x' THEN 1 ELSE 0 END) as exits,
  SUM(CASE WHEN t = 'j' THEN 1 ELSE 0 END) - SUM(CASE WHEN t = 'x' THEN 1 ELSE 0 END) as net
FROM
  (SELECT join_date as d, 'j' as t FROM admissions) j
  UNION ALL
  (SELECT exit_date as d, 'x' as t FROM exits) x
GROUP BY d

我们使用 UNION ALL 连接数据并记下它的类型 - join 或 exit，我们可以稍后比较一个简单的 char

我们将其按 d 分组，每行给出一个日期，然后我们对有条件地查看它是 'j'oin 还是 e'x' 它。如果该行是 j，则将 1 添加到跟踪当天连接总数的列，依此类推

唯一没有给你的是没有加入或退出的日子..(例如 2018-12-25, 0, 0, 0 因为圣诞节是关闭的那天没有人做任何事)。但是你没有说你想要那些。

如果你确实想要带有日期的行，0 次退出，0 次加入，0 次网络，那么我们必须使用一些额外的魔法，这有点让人头疼/让它更难理解(所以我把它忘了)