要实现这样的功能,应该怎么写SQL语句

[bluesky1980]

两个表(通过表1的id与表2的table1_id相关联)
table1 table2
id state id table1_id result
1 成功 1 1 y
2 失败 2 1 n
3 成功 3 2 n
4 3 y
5 3 n

想得到的结果 : num1为count(table1.id) 在统计num2时,需判断一下state,若为成功,则再判断result,若为y,则计数(count(result)),若为n,则不计数;
若为失败,则直接count(result) 即:
state num1 num2
成功 2 2
失败 1 1

请指点,谢谢

[victorycyz]

为什么同一个问题，反反复复地问呢？原来给你的回复，你认为有什么问题呢？

[zjcxc]

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

[playyuer]

hao

[bluesky1980]

victorycyz(中海，学SQL Server的菜鸟) :是我举一反三的能力不强,请高手莫怪.

[zjcxc]

--上面的错了.楼主的意思应该是这样的吧?

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

[zjcxc]

--下面是测试

--测试数据
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 行）

--*/

[j9988]

select A.state,
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