By any chance you would be able to help optimize this query without me showing you the tables?
在没有我向您展示表格的情况下，您将能够帮助优化此查询吗？
My original table that all of these queries are derived from has the following columns and the table is named laterec-students
我的所有这些查询的原始表都包含以下列，该表名为laterec-students
--------------------------------------------------------------
| studentid | name | class | latetime | waived |
--------------------------------------------------------------
| ID1111STU | Stu 1 | 1A |2012-01-09 08:09:00 |Waived |
SELECT A.class, NoStudentsLate, 1xLATE, 2xLATE FROM (
SELECT
class,
count(DISTINCT studentid) AS NoStudentsLate
FROM `laterec-students`
WHERE waived!="Waived"
GROUP BY class
) AS A
LEFT JOIN (
SELECT class, count(distinct studentid) AS 1xLATE from (
SELECT `laterec-students`.class, `laterec-students`.studentid
FROM `laterec-students`
WHERE waived!="Waived"
GROUP BY studentid
HAVING count(studentid)=1) as temp
GROUP BY class
) AS B ON A.class=B.class
LEFT JOIN (
SELECT class, count(distinct studentid) AS 2xLATE from (
SELECT `laterec-students`.class, `laterec-students`.studentid
FROM `laterec-students`
WHERE waived!="Waived"
GROUP BY studentid
HAVING count(studentid)=2) as temp
GROUP BY class
) AS C ON A.class=C.class
This is what I am trying to accomplish
这就是我想要实现的目标
---------------------------------------------------------------------
| Class | Total # of students late | # late 1 times | # late 2 times |
---------------------------------------------------------------------
| 1A | 5 | 3 | 2 |
| 1B | 3 | 3 | 0 |
---------------------------------------------------------------------
So what this means, in class 1A, there are a total of 5 student late as counted using the student id. Out of this 5, 3 students are late once, and 2 students are late twice.
所以这意味着，在1A级中，总共有5名学生迟到了，使用学生ID计算。在这5名学生中，有3名学生迟到了一次，2名学生迟到了两次。
Again in class 1B, total 3 students are late, and all of them are only late once.
同样在1B班，共有3名学生迟到，所有学生都只迟到一次。
1 个解决方案
#1
3
I hope that I understood your query, but the following works with my SQL Fiddle example.
我希望我理解你的查询，但以下工作与我的SQL小提琴示例。
SELECT
class,
SUM(cnt > 0) AS NoStudentsLate,
SUM(cnt = 1) AS 1xLate,
SUM(cnt = 2) AS 2xLate
FROM
(
SELECT class, studentid, COUNT(*) AS cnt
FROM `laterec-students`
WHERE waived!='Waived'
GROUP BY class, studentid
) t
GROUP BY class;
#1
3
I hope that I understood your query, but the following works with my SQL Fiddle example.
我希望我理解你的查询，但以下工作与我的SQL小提琴示例。
SELECT
class,
SUM(cnt > 0) AS NoStudentsLate,
SUM(cnt = 1) AS 1xLate,
SUM(cnt = 2) AS 2xLate
FROM
(
SELECT class, studentid, COUNT(*) AS cnt
FROM `laterec-students`
WHERE waived!='Waived'
GROUP BY class, studentid
) t
GROUP BY class;