select a.state,num1=count(a.id)
,num2=sum(case when a.state='成功' and b.result='y' then 1 else 0 end)
from table1 a join table2 b on a.id=b.table1_id
,num2=sum(case when a.state='成功' and b.result='y' then 1 else 0 end)
from table1 a join table2 b on a.id=b.table1_id
,num2=sum(case when (a.state='成功' and b.result='y') or (a.state='失败' and b.result='n') then 1 else 0 end)
from table1 a join table2 b on a.id=b.table1_id
group by a.state
declare @table1 table(id int,state varchar(10))
insert into @table1
select 1,'成功'
union all select 2,'失败'
union all select 3,'成功'declare @table2 table(id int,table1_id int,result varchar(1))
insert into @table2
select 1,1,'y'
union all select 2,1,'n'
union all select 3,2,'n'
union all select 4,3,'y'
union all select 5,3,'n'--查询统计
select a.state,num1=count(distinct a.id)
,num2=sum(case when (a.state='成功' and b.result='y') or (a.state='失败' and b.result='n') then 1 else 0 end)
from @table1 a join @table2 b on a.id=b.table1_id
group by a.state/*--测试结果
state num1 num2
---------- ----------- -----------
成功 2 2
失败 1 1(所影响的行数为 2 行)--*/
count(distinct A.state) as num1,
sum(case when (A.status='成功' and B.result='y')
or A.status='失败'
then 1
else 0 end) as num2
from table1 A,table2 b
where A.id=B.table1_id
group by A.state