查询结果集的sql疑惑？

[wenle]

请问有两个表ta和tb 如下，现在要查询的结果集为
id a e
1 4 3
2 3 1
3 5 0

查询出来的e列为表ta表中id在tb表中b列大于2的记录条数
ta.id=tb.id

表ta
id a
1 4
2 3
3 5
表tb
id b
1 5
1 5
1 5
2 4
2 1

[-狙击手-]

select a.id,a.a,e = select count(1) from tb where id = a.id and b > 2)
from a 

[昵称被占用了]

select a.*,isnull(b.e,0) as e
from ta a left join (
select id ,count(1) as e from tb where b>2 group by id
) as b
on a.id=b.id

[dawugui]

select ta.id , ta.a , e = (select isnull(count(*),0) from tb where b > 2 and id = ta.id) from ta

[free1879]

select a.id , a.a , e = (select isnull(count(*),0) from tb where b > 2 and id = a.id) from ta a

[upingking]

select id ,a,( select count 1 from tb where ta.id=tb.id and b>2 ) as e from ta

[dawugui]

create table ta(id int, a int)
insert into ta values(1, 4 )
insert into ta values(2, 3 )
insert into ta values(3, 5 )
create table tb(id int, b int)
insert into tb values(1, 5 )
insert into tb values(1, 5 )
insert into tb values(1, 5 )
insert into tb values(2, 4 )
insert into tb values(2, 1)
go
select ta.id , ta.a , e = (select isnull(count(*),0) from tb where b > 2 and id = ta.id) from ta

drop table ta,tb

/*
id          a           e
----------- ----------- -----------
1           4           3
2           3           1
3           5           0

(3 行受影响)
*/

[pt1314917]

create table ta(id int,a int)
insert into ta select 1,4
insert into ta select 2,3
insert into ta select 3,5

create table tb(id int,b int)
insert into tb select 1,5
insert into tb select 1,5
insert into tb select 1,5
insert into tb select 2,4
insert into tb select 2,1


select a.id,a.a,isnull(b.e,0)e from ta a left join
(select id,count(1)e from tb where b>2 group by id)b
on a.id=b.id

[qiuming0306]

总是没有机会得分啊！

[dobear_0922]

create table ta(id int, a int)
create table tb(id int, b int)

insert into ta values(1, 4 )
insert into ta values(2, 3 )
insert into ta values(3, 5 )

insert into tb values(1, 5 )
insert into tb values(1, 5 )
insert into tb values(1, 5 )
insert into tb values(2, 4 )
insert into tb values(2, 1 )

select a.id, a, e=isnull(b.e,0)
from ta a left join 
(
	select id , e=count(1) from tb where b>2 group by id
) b on a.id=b.id

/*
id          a           e
----------- ----------- -----------
1           4           3
2           3           1
3           5           0

(3 row(s) affected)
*/

drop table ta,tb