sql查询
create table #A
(
AA int,
BB nvarchar(20),
)
insert into #A values(1, 'a ')
insert into #A values(2, 'b ')
insert into #A values(3, 'a ')
insert into #A values(4, 'a ')
insert into #A values(5, 'b ')
insert into #A values(6, 'c ')
insert into #A values(7, 'c ')
create table #B
(
AA int,
CC int
)
insert into #B values(1,1)
insert into #B values(2,2)
insert into #B values(3,1)
insert into #B values(4,2)
insert into #B values(5,1)
insert into #B values(6,1)
insert into #B values(7,2)
测试数据：
希望先分组再条件。AA连接：
希望结果：
查出A表BB出现2次 B表的CC为1的数目。
最好一个sql.
错误查询：select count(A.BB) from #A A inner join #B B on A.AA = B.AA where B.CC = 1 group by A.BB having (count(A.BB) = 2)
应该是2个，b和c的.分别1个.
[解决办法]
是不是这样
select count(*) from #A A inner join #B B on A.AA = B.AA
where B.CC = 1 and BB in(select BB from #A group by BB having count(BB)=2)
[解决办法]
select count(*) from #B b join #A a on b.AA=a.AA join (select BB from #A group by BB having(count(*))=2) c on a.BB=c.BB where b.CC=1
[解决办法]
select count(1) from #A A join #B B on A.AA = B.AA
where B.CC = 1 and BB in (select BB from #A group by BB having count(1)=2)
[解决办法]
Select
A.*,
B.CC
From
#A As A
Inner Join
#B As B
On A.AA=B.AA And Exists
(Select Count(BB) From #A Where BB=A.BB Group By BB Having Count(BB)=2)
Where B.CC=1
[解决办法]
wishY() ( ) 信誉：100 2007-9-13 14:00:54 得分: 0
这样我也写过，不过数据量大了，就跑不起来了。
-------------------------------------------------------
你写的是以上哪个?
你是在前台程序中调用还是在查询分析器里执行,超时啊???
[解决办法]
select * from #a a ,#b b
where a.aa=b.aa and exists (select bb from #a where bb=a.bb group by bb having count(aa)=2 )
and b.cc=1
看看这个会不会超时！！
