日期:2014-05-18  浏览次数:20525 次

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