向各位高手求教一个语句,由于初学SQL,想了好长时间没想出来!
数据如下:
ID TYPE RESULT
001 AAA 02
002 BBB 04
003 AAA 05
004 CCC 99
005 DDD 04
006 CCC 02
007 EEE 02
008 EEE 02
... ...
目的:统计TYPE字段中某型号的出现次数且未出现问题,result字段中02代表未出现问题,其余都属异常。也就是想得到结果为“型号EEE被检查了两次且未出现问题”。
万分感谢!
数据如下:
ID TYPE RESULT
001 AAA 02
002 BBB 04
003 AAA 05
004 CCC 99
005 DDD 04
006 CCC 02
007 EEE 02
008 EEE 02
... ...
目的:统计TYPE字段中某型号的出现次数且未出现问题,result字段中02代表未出现问题,其余都属异常。也就是想得到结果为“型号EEE被检查了两次且未出现问题”。
万分感谢!
declare @table table (ID varchar(3),TYPE varchar(3),RESULT varchar(2))
insert into @table
select '001','AAA','02' union all
select '002','BBB','04' union all
select '003','AAA','05' union all
select '004','CCC','99' union all
select '005','DDD','04' union all
select '006','CCC','02' union all
select '007','EEE','02' union all
select '008','EEE','02'select TYPE,count(1) as 出现次数
from @table where RESULT='02'
group by TYPE
/*
TYPE 出现次数
---- -----------
AAA 1
CCC 1
EEE 2
*/
insert into tb
select '001','AAA','02' union all
select '002','BBB','04' union all
select '003','AAA','05' union all
select '004','CCC','99' union all
select '005','DDD','04' union all
select '006','CCC','02' union all
select '007','EEE','02' union all
select '008','EEE','02'select TYPE , count(1) cnt from tb where TYPE not in
(select distinct TYPE from tb where RESULT <> '02')
group by TYPE/*
TYPE cnt
---- -----------
EEE 2(所影响的行数为 1 行)
*/drop table tb
) from tb a
declare @table table (ID varchar(3),TYPE varchar(3),RESULT varchar(2))
insert into @table
select '001','AAA','02' union all
select '002','BBB','04' union all
select '003','AAA','05' union all
select '004','CCC','99' union all
select '005','DDD','04' union all
select '006','CCC','02' union all
select '007','EEE','02' union all
select '008','EEE','02'select aa.TYPE,aa.numb from(select TYPE,
(select count(*) from @table where type=b.type) as numa,
count(1) as numb from @table b where RESULT='02'
group by TYPE) aa where numa=numb
/*
TYPE numb
---- -----------
EEE 2
*/