使用组从多个表中计数

ca1c2owp 于 2021-06-18 发布在 Mysql

关注(0)|答案(3)|浏览(244)

我有两张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 |
+---------------------+--------+--------+-----------+

注意：可能会有几天发生入院，但没有登记退出，反之亦然。

sql mysql

来源：https://stackoverflow.com/questions/53649665/count-from-multiple-tables-with-group

3条答案

按热度按时间

l2osamch1#

干得好：

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，使用一个简单的字符，我们可以稍后比较
我们把它按d分组，每行给出一个日期，然后有条件地检查它是否是a 'j' oin还是e 'x' 是的。如果该行是j，则会将1添加到跟踪当天连接总数的列中，依此类推
唯一没有给你的，是没有连接或退出的日子(例如 2018-12-25, 0, 0, 0 因为圣诞节已经结束了，那天没有人做任何事。。但你没说你想要这些。
如果你真的想要有日期的行，0个出口，0个连接，0个网，那么我们必须要做一些额外的工作，这是一个有点头痛/使它更难理解（所以我省略了）

赞(0）回复(0）举报 2021-06-18

ljo96ir52#

我没有找到答案。下面是我一个朋友的回答，下面是mysql版本：

select aa.date, IFNULL(aa.joins, 0) joins, IFNULL(bb.exits,0) exits, (IFNULL(aa.joins,0) - IFNULL(bb.exits,0)) net
from
(
  select join_date date, count(name) joins
  from Admissions
  group by join_date
) aa
left join
(
 select exit_date date, count(name) exits
 from Exits
 group by exit_date
) bb on aa.date = bb.date

UNION

select bb.date, IFNULL(aa.joins, 0) joins, IFNULL(bb.exits,0) exits, (IFNULL(aa.joins,0) - IFNULL(bb.exits,0)) net
from
(
  select join_date date, count(name) joins
  from Admissions
  group by join_date
) aa
right join
(
 select exit_date date, count(name) exits
 from Exits
 group by exit_date
) bb on aa.date = bb.date order by date;

赞(0）回复(0）举报 2021-06-18

xiozqbni3#

以下步骤可以：

select 
        CASE WHEN join_date is not null THEN join_date 
             WHEN exit_date is not null THEN exit_date END as date,
        entry.cnt as joins,
        exit.cnt as exits,
        (extry.cnt - exit.cnt) as net
    FROM
        (select join_date, COALESCE(count(*), 0) as cnt from Admissions group by join_date) entry 
    FULL OUTER JOIN
        (select exit_date, COALESCE(count(*), 0) as cnt from Exits group by exit_date) exit 
    ON 
        entry.join_date=exit.exit_date
    ;

赞(0）回复(0）举报 2021-06-18

我来回答

使用组从多个表中计数

3条答案

相关问题

热门标签

最新问答