所以这是我很长的SQL查询。
基本上，我有2个学校级别，即JC1和JC2，我正在为当前日期和上一个日期的每个级别对下面的字段进行计数。
这是我今天的原始子查询，我使用另一个具有不同日期的子查询。
SELECT
level,
COUNT(studentid) AS total,
SUM(leader1 <> '' OR leader2 <> '') AS leaders,
SUM(scholarship <> '') AS scholarship,
SUM(pegasus <> '') as pegasus
FROM `laterec-students`
WHERE latetime > '2012-05-25 00:00:00'
GROUP BY level;
它将返回
level | total | leaders | scholarship |pegasus
JC1 | 28 | 7 | 0 | 2
JC2 | 14 | 6 | 0 | 3
现在，对于某些日期，我可能没有同时返回JC1和JC2。 （例如，从2012年5月25日开始，我同时拥有JC1和JC2，因为前一天我只有JC2，因为没有JC1数据）
因此，这就是为什么我没有使用JOIN来帮助我的原因，或者也许是因为我真的不知道如何正确使用JOIN。
对于下面的SQL查询，
子查询返回此内容（子查询tjc1的示例）
total | leaders | scholarship |pegasus
28 | 7 | 0 | 2
SELECT
SUM(tjc1.total) AS JC1total,
SUM(yjc1.ytotal) AS JC1ytotal,
SUM(tjc1.leaders) AS JC1leaders,
SUM(yjc1.yleaders) AS JC1yleaders,
SUM(tjc1.scholarship) AS JC1scholarship,
SUM(yjc1.yscholarship) AS JC1yscholarship,
SUM(tjc1.pegasus) AS JC1pegasus,
SUM(yjc1.ypegasus) AS JC1ypegasus,
SUM(tjc2.total) AS JC2total,
SUM(yjc2.ytotal) AS JC2ytotal,
SUM(tjc2.leaders) AS JC2leaders,
SUM(yjc2.yleaders) AS JC2yleaders,
SUM(tjc2.scholarship) AS JC2scholarship,
SUM(yjc2.yscholarship) AS JC2yscholarship,
SUM(tjc2.pegasus) AS JC2pegasus,
SUM(yjc2.ypegasus) AS JC2ypegasus
FROM
(
SELECT
COUNT(studentid) AS total,
SUM(leader1 <> '' OR leader2 <> '') AS leaders,
SUM(scholarship <> '') AS scholarship,
SUM(pegasus <> '') as pegasus
FROM `laterec-students`
WHERE latetime > '2012-05-25 00:00:00'
AND level = 'JC1'
) tjc1,
(
SELECT
COUNT(studentid) AS ytotal,
SUM(leader1<>'' or leader2<>'') AS yleaders,
SUM(scholarship<>'') AS yscholarship,
SUM(pegasus<>'') as ypegasus
FROM `laterec-students`
WHERE latetime BETWEEN '2012-05-24 00:00:00' AND '2012-05-24 23:59:59'
AND level = 'JC1'
) yjc1,
(
SELECT
COUNT(studentid) AS total,
SUM(leader1 <> '' OR leader2 <> '') AS leaders,
SUM(scholarship <> '') AS scholarship,
SUM(pegasus <> '') as pegasus
FROM `laterec-students`
WHERE latetime > '2012-05-25 00:00:00'
AND level = 'JC2'
) tjc2,
(
SELECT
COUNT(studentid) AS ytotal,
SUM(leader1<>'' or leader2<>'') AS yleaders,
SUM(scholarship<>'') AS yscholarship,
SUM(pegasus<>'') as ypegasus
FROM `laterec-students`
WHERE latetime BETWEEN '2012-05-24 00:00:00' AND '2012-05-24 23:59:59'
AND level = 'JC2'
) yjc2
因此，如果您认为可以找到一种方法来帮助我缩短查询，提高查询效率等，那么我将永远感激不已，并且能够一路学习。谢谢！
最佳答案
尝试这个：
select the_type,
level,
sum(total),
sum(leaders),
sum(scholarship),
sum(pegasus)
FROM
(
(
SELECT
't' the_type,
level,
COUNT(studentid) AS total,
SUM(leader1 <> '' OR leader2 <> '') AS leaders,
SUM(scholarship <> '') AS scholarship,
SUM(pegasus <> '') as pegasus
FROM `laterec-students`
WHERE latetime > '2012-05-25 00:00:00'
AND level in('JC1', 'JC2')
GROUP BY the_type, level
)
UNION ALL
(
SELECT
'y' the_type,
level,
COUNT(studentid) AS ytotal,
SUM(leader1<>'' or leader2<>'') AS yleaders,
SUM(scholarship<>'') AS yscholarship,
SUM(pegasus<>'') as ypegasus
FROM `laterec-students`
WHERE latetime BETWEEN '2012-05-24 00:00:00' AND '2012-05-24 23:59:59'
AND level in('JC1', 'JC2')
GROUP BY the_type, level
)
) AS the_union
GROUP BY the_type, level;
另一种方法可能是这样的：
SELECT
date(latetime) the_date,
level,
COUNT(studentid) AS total,
SUM(leader1<>'' or leader2<>'') AS leaders,
SUM(scholarship<>'') AS scholarship,
SUM(pegasus<>'') as pegasus
FROM `laterec-students`
WHERE latetime between '2012-05-24 00:00:00' AND '2012-05-25 23:59:59'
AND level in('JC1', 'JC2')
group by the_date, level;
关于mysql - 缩短SQL COUNT语句，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/12030570/