mysql - 完全外部自我连接，两个别名mysql的计数

我有一个包含3个字段的表diagnoses

 id(PK), created_date(DateTime), doctor_id(Foreign Key).
 doctor1 = 4 (doctor_id),
 and
 doctor2= 11(doctor_id).

doctor1在早上到达，而Doctor 2在晚上到达。众所周知，当我们运行Count（*）函数时，出现的日期是第一个找到的记录的日期。
所以我想打印这样的东西

    Date | Am-TimeIn | Doctor1 (Count) | Pm-TimeIn | Doctor2 (Count)

因此，如果不存在doctor1且存在doctor2，则将打印行，类似Doctor2的ABSENCY。

它需要full outer self join，在Date（created_date）上使用group by子句，以便每位医生每天汇总所有记录。目前我有这个，但它不起作用

    SELECT Date(CASE WHEN (a.created_date IS NOT NULL) THEN a.created_date ELSE b.created_date END) 'Date', a.`doctor1`, TIME(a.created_date) 'AM-TimeIn', b.`doctor2`,  TIME(b.created_date) 'PM-TimeIn'
    From (select created_date, count(*) doctor1 FROM diagnoses WHERE  doctor_id = 4 GROUP BY DATE(created_date)) a
    full join
    (select created_date, count(*) doctor2 FROM diagnoses WHERE doctor_id = 11 GROUP BY DATE(created_date)) b
    ON DATE(b.created_date) = DATE(a.created_date);

最佳答案

MariaDB [sandbox]> drop table if exists diagnosis;
Query OK, 0 rows affected (0.09 sec)

MariaDB [sandbox]> create table diagnosis(id int, dt datetime,doctor_id int);
Query OK, 0 rows affected (0.22 sec)

MariaDB [sandbox]> insert into diagnosis values
    -> (1,'2017-01-01 07:00:00',1),(1,'2017-01-01 08:00:00',1),(1,'2017-01-01 13:00:00',2),
    -> (1,'2017-01-02 07:00:00',1),(1,'2017-01-02 08:00:00',1),
    -> (1,'2017-01-03 13:00:00',2);
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [sandbox]>
MariaDB [sandbox]> select   min(case when doctor_id = 1 then doctor_id end) doc1,
    -> min(case when doctor_id = 1 then dt end) am_timein,
    -> sum(case when doctor_id = 1 then 1 else 0 end)  doc1cases,
    -> min(case when doctor_id = 2 then doctor_id end) doc2,
    -> min(case when doctor_id = 2 then dt end) pm_timein,
    -> sum(case when doctor_id = 2 then 1 else 0 end)  doc2cases,
    -> count(*) as Total
    -> from diagnosis
    -> group by date(dt);
+------+---------------------+-----------+------+---------------------+-----------+-------+
| doc1 | am_timein           | doc1cases | doc2 | pm_timein           | doc2cases | Total |
+------+---------------------+-----------+------+---------------------+-----------+-------+
|    1 | 2017-01-01 07:00:00 |         2 |    2 | 2017-01-01 13:00:00 |         1 |     3 |
|    1 | 2017-01-02 07:00:00 |         2 | NULL | NULL                |         0 |     2 |
| NULL | NULL                |         0 |    2 | 2017-01-03 13:00:00 |         1 |     1 |
+------+---------------------+-----------+------+---------------------+-----------+-------+
3 rows in set (0.00 sec)

关于mysql - 完全外部自我连接，两个别名mysql的计数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/41844902/