需求:由表一查询出表二
解决方案:
– 此处是原表
-- 此处是原表
SELECT tp.`name` projectName,
COUNT(tp.`project_id`) total,
SUM(spi.`flag` = 0) noCheck,
SUM(spi.`flag` = 1) qualified,
SUM(spi.`flag` = 3) unqualified,
SUM(spi.`flag` = 4) manualAdjustment
FROM`s_photo_info` spi
JOIN `tb_project` tp ON spi.`project_id` = tp.`project_id`
JOIN `tb_project_student` tps ON spi.`student_id` = tps.`id`
JOIN `tb_school_info` ts ON tps.`school_id` = ts.`school_id`
WHERE 1 = 1
GROUP BY spi.`project_id`
ORDER BY tp.project_id DESC
– 查询出结果的表
-- 查询出结果的表
SELECT COALESCE(a.projectName,'总计') AS projectName,
SUM(a.total) AS total,
SUM(a.noCheck) AS noCheck,
SUM(a.qualified) AS qualified,
SUM(a.unqualified) AS unqualified,
SUM(a.manualAdjustment) AS manualAdjustment
FROM
(SELECT tp.`name` projectName,
COUNT(tp.`project_id`) total,
SUM(spi.`flag` = 0) noCheck,
SUM(spi.`flag` = 1) qualified,
SUM(spi.`flag` = 3) unqualified,
SUM(spi.`flag` = 4) manualAdjustment
FROM`s_photo_info` spi
JOIN `tb_project` tp ON spi.`project_id` = tp.`project_id`
JOIN `tb_project_student` tps ON spi.`student_id` = tps.`id`
JOIN `tb_school_info` ts ON tps.`school_id` = ts.`school_id`
WHERE 1 = 1
GROUP BY spi.`project_id`
ORDER BY tp.project_id DESC) AS a
GROUP BY a.projectName
WITH ROLLUP;
这里主要使用sql语句: WITH ROLLUP 关键字
WITH ROLLUP:在group分组字段的基础上再进行统计数据。
SELECT 字段1, SUM(字段2) as money FROM 表 GROUP BY 字段1 WITH ROLLUP;
更多推荐
SQL语句在做统计时如何在最后增加一行合计
发布评论