mysql 合计_mysql-查询不同列的数量合计

车辆违规信息表testmodel_test

表结构：

表字段：cra_id(车牌号)，if_weigui(该次行驶是否违规，0是正常，1是违规)

目的：

查询表中共有几辆车，违规的有几辆车；

方法1

SELECT COUNT(f.cra_id) carNum, SUM(f.weigui) weiguiNum FROM

(SELECT a.cra_id,b.weigui

FROM testmodel_test a

LEFT JOIN

(SELECT t.cra_id, 1 AS weigui FROM testmodel_test t

WHERE t.if_weigui=1

GROUP BY t.cra_id) b ON b.cra_id=a.cra_id

GROUP BY a.cra_id)f

方法2

SELECT table1.车辆数,table2.违规车辆数 FROM

(SELECT COUNT(DISTINCT(cra_id)) AS '车辆数' FROM `testmodel_test` )table1

JOIN

(SELECT COUNT(DISTINCT(cra_id)) AS '违规车辆数' FROM `testmodel_test` WHERE if_weigui&