【现有表T1】
通过一级 通过二级 分类
ID CHK1[BIT] CHK2[BIT] CATE[VARCHAR(20)]
1 1 0 A
2 0 0 A
3 1 1 A
4 1 0 B
5 1 0 B
...
【返回以下格式查询】
CATE BAND1 BAND2
分类 未通过一级数 通过一级但未通过二级数
A 1 1
B 0 2
【使用两句SQL】
1.
SELECT CATE,Count(ID) BAND1
FROM T1
WHERE CHK1=0
GROUP BY ALL CATE
ORDER BY CATE ASC
2.
SELECT CATE,Count(ID) BAND2
FROM T1
WHERE CHK1=1 AND CHK2=0
GROUP BY ALL CATE
ORDER BY CATE ASC
【妙用一句SQL实现】
SELECT CATE,SUM(1-CHK1) AS BAND1,SUM(CHK1*(1-CHK2)) AS BAND2
FROM T1
GROUP BY CATE
ORDER BY CATE ASC