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=1)table2

如果是有多行數據，可以加個鏈接控制

SELECT table1.車輛數,table2.違規車輛數 FROM
(SELECT 0 AS a,COUNT(DISTINCT(cra_id)) AS '車輛數' FROM `testmodel_test` )table1
JOIN
(SELECT 0 AS c ,COUNT(DISTINCT(cra_id)) AS '違規車輛數' FROM `testmodel_test` WHERE if_weigui=1)table2
ON table1.a=table2.c

結果：