时间戳

首页 > 解决方案 > 从带有组的多个表中计数

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

问题描述

我有两张桌子。第一个表登记录取。第二个有出口，如下图：

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

注意：可能有几天会发生录取，但没有注册退出，反之亦然。

标签： mysqlsql

解决方案

干得好：

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 连接数据并记下它的类型 - 加入或退出，我们稍后可以比较一个简单的字符

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

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

如果您确实想要带有日期的行，并且 0 退出，0 加入，0 网络，那么我们必须使用一些额外的魔法，这有点让人头疼/更难理解（所以我离开了出去）

推荐阅读