mysql 表组是什么_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 |

+---------------------+--------+--------+-----------+

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

解决方法:

我没有找到答案.这是我一个朋友的回答,下面是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;

标签：sql,mysql

来源： https://codeday.me/bug/20191108/2007512.html