MySQL 统计记录频率

Question

表:

laterecords
-----------
studentid - varchar
latetime - datetime
reason - varchar

students
--------
studentid - varchar -- Primary
class - varchar

我想做一个查询来显示以下内容:

Sample Report

Class      No of Students late  1 times  2 times   3 times  4 times    5 & more 
Class A        3                  1       0         2         0         0
Class B        1                  0       1         0         0         0

我下面的查询可以显示第一列结果:

SELECT count(Distinct studentid), class FROM laterecords, students
WHERE students.studenid=laterecords.studentid AND
GROUP BY class

我只能想到获取每一列的结果并将它们存储到 php 数组中。然后将它们回显到 HTML 表格中。

是否有更好的 SQL 方法来执行上述操作？如何进行 mysql 查询？

Answer 1

试试这个:

SELECT
    a.class,
    COUNT(b.studentid) AS 'No of Students late',
    SUM(b.onetime) AS '1 times',
    SUM(b.twotime) AS '2 times',
    SUM(b.threetime) AS '3 times',
    SUM(b.fourtime) AS '4 times',
    SUM(b.fiveormore) AS '5 & more'
FROM
    students a
LEFT JOIN
    (
        SELECT
            aa.studentid,
            IF(COUNT(*) = 1, 1, 0) AS onetime,
            IF(COUNT(*) = 2, 1, 0) AS twotime,
            IF(COUNT(*) = 3, 1, 0) AS threetime,
            IF(COUNT(*) = 4, 1, 0) AS fourtime,
            IF(COUNT(*) >= 5, 1, 0) AS fiveormore
        FROM 
            students aa
        INNER JOIN
            laterecords bb ON aa.studentid = bb.studentid
        GROUP BY 
            aa.studentid
    ) b ON a.studentid = b.studentid
GROUP BY
    a.class