mysql 列表展示，最后一行统计以上列表。union all

最新推荐文章于 2024-07-30 08:31:58 发布

drose29

最新推荐文章于 2024-07-30 08:31:58 发布

阅读量1.3k

点赞数 3

文章标签： java mysql

本文链接：https://blog.csdn.net/drose29/article/details/83344051

版权

效果如图，原本想最后一行的数据再请求一次进行统计。。

百度瞎捉摸，Mysq中的Union all 可以拼接。只需对上面的sql进行sum，字段对应就可以。


SELECT
a.area,
a.companyNum,
COUNT(a.EXAMINE_RESULT != '')checkNum,
COUNT(a.EXAMINE_RESULT = 1) unPassNum,
COUNT(a.HANDLE_RESULT LIKE '%5%') punishNum
FROM 
(
SELECT 
zje.AREA,
COUNT(zje.LICENCE_CODE !='') companyNum,
zjr.EXAMINE_RESULT,
zjr.COMPANY_ID,
zjr.HANDLE_RESULT,
COUNT(CASE WHEN zju.PUNISH LIKE '%5%' THEN 1 ELSE NULL END) rePunish
FROM 
zj_enterprise zje INNER JOIN zj_samling_result zjr ON zje.ENTERPRISE_ID = zjr.COMPANY_ID INNER JOIN zj_samling_unqualified zju ON zjr.ID = zju.RESULT_ID
GROUP BY zje.AREA
)AS a
GROUP BY a.COMPANY_ID

UNION ALL 

SELECT '合计' AS AREA,SUM(b.companyNum) companyNum,SUM(b.checkNum) checkNum,SUM(b.unPassNum) unPassNum,SUM(b.punishNum) punishNum FROM (
SELECT
a.area,
a.companyNum,
COUNT(a.EXAMINE_RESULT != '')checkNum,
COUNT(a.EXAMINE_RESULT = 1) unPassNum,
COUNT(a.HANDLE_RESULT LIKE '%5%') punishNum
FROM 
(
SELECT 
zje.AREA,
COUNT(zje.LICENCE_CODE !='') companyNum,
zjr.EXAMINE_RESULT,
zjr.COMPANY_ID,
zjr.HANDLE_RESULT,
COUNT(CASE WHEN zju.PUNISH LIKE '%5%' THEN 1 ELSE NULL END) rePunish
FROM 
zj_enterprise zje INNER JOIN zj_samling_result zjr ON zje.ENTERPRISE_ID = zjr.COMPANY_ID INNER JOIN zj_samling_unqualified zju ON zjr.ID = zju.RESULT_ID
GROUP BY zje.AREA
)AS a
GROUP BY a.COMPANY_ID
) AS b

使用Union all 拼接的使用，前后两个的字段要对应。。

简单给自己记录下，省的忘记了。