SELECT 种类,
['已检测物品个数']=(SELECT COUNT(DISTICNT 物品 ) FROM TABLE WHERE 检测日期IS NOT NULL AND 种类= A.种类),
['未检测物品个数']=(SELECT COUNT(DISTICNT 物品 ) FROM TABLE WHERE 检测日期 IS NULL AND 种类= A.种类),
['检测次数']=(SELECT COUNT(1) FROM TABLE WHERE 检测日期 IS NOT NULL AND 种类= A.种类)
FROM TABLE AS A
GROUP BY 种类
['已检测物品个数']=(SELECT COUNT(DISTICNT 物品 ) FROM TABLE WHERE 检测日期IS NOT NULL AND 种类= A.种类),
['未检测物品个数']=(SELECT COUNT(DISTICNT 物品 ) FROM TABLE WHERE 检测日期 IS NULL AND 种类= A.种类),
['检测次数']=(SELECT COUNT(1) FROM TABLE WHERE 检测日期 IS NOT NULL AND 种类= A.种类)
FROM TABLE AS A
GROUP BY 种类
已检测物品个数=(select count(distinct(物品)) from t1 where 种类=a.种类 and 检测日期 is not null ),
未检测物品个数=sum(case when 种类=a.种类 and 检测日期 is null then 1 else 0 end),
检测次数=sum(case when 种类=a.种类 and 检测日期 is not null then 1 else 0 end)
from t1 a group by a.种类
mname varchar(3),
mtype int,
mdate datetime null
)insert into test21
select '101', 2 , '2004-01-01' union all
select'101', 2, '2004-03-13' union all
select'222', 5, '2005-01-10' union all
select'3', 1, '2004-12-12' union all
select'3', 1, '2005-01-10' union all
select'113', 4,null union all
select'010', 1, null---------------------下面是執行語句--------------------select mtype ,
已檢=isnull((select count(*) from (select mname,mtype from test21 where mtype=a.mtype and mdate is not null group by mname,mtype ) b ),0) ,
未檢=isnull((select count(*) from (select mname,mtype from test21 where mtype=a.mtype and mdate is null group by mname,mtype ) b ),0) ,
檢次=isnull((select count(*) from test21 where mtype=a.mtype and mdate is not null ),0)
from test21 a group by mtype-------------結果----------
1 1 1 2
2 1 0 2
4 0 1 0
5 1 0 1