mysql - 如何从 MySQL 中的两个表中获取数据并分别计数？

Question

我怎样才能在一个查询中写这个？

我有两张 table ，其中一张很喜欢这个名字(晚):

id     name     S_id
1       A         6
2       A         6
3       B         5
4       C         8
5       A         6
6       A         6
7       C         8
8       C         8

另一个喜欢这个叫(缺席):

id     name     S_id
1       A         6
2       A         6
3       A         6
4       A         6
5       A         6
6       A         6
7       B         5
8       c         8

我想要像这张表这样的结果:

其中 (count late) 计算迟到次数，(count absent) 计算缺席时间。

name    Count late    Count absent
 A          4              6
 B          1              1
 C          3              1

我试过这样的:

这没用!

SELECT 
*
FROM
(SELECT name, COUNT(*) AS '# count absent' FROM absent GROUP BY s_id)  t1 
INNER JOIN
(SELECT name, COUNT(*) AS '# count Late' FROM late   GROUP BY s_id)  t2
ON t1.s_id = t2.s_id ;

Answer 1

使用两个表的联合:迟到和缺席......然后总结迟到和缺席的数量。

试试这个:

SELECT 
    SUM(tardies) as 'total_lates', SUM(absences) as 'total_absences', name, s_id
FROM
    ((SELECT
        COUNT(*) as 'tardies',
        0 as 'absences',
        name,
        s_id
    FROM 
        lates
    GROUP BY
        s_id
    )
UNION
    (SELECT 
        0 as 'tardies',
        COUNT(*) as 'absences',
        name,
        s_id
     FROM
        absents
     GROUP BY
        s_id
    )
)
as maintable
GROUP by s_id
ORDER BY name