请教一个SQLSERVER里的子查询的问题 急
"Select   ID,(select   count(*)   from   IPCount   where   AdId   =   ADCount.Id),(select   count(*)   from   IPCount   where   come_state   =   1   and   AdId   =   ADCount.Id)   from   AdCount   Order   By   Id   Desc "
这句可有什么地方是错误的?

前提是:

表IPCount中两条符合AdId   =   AdCount.Id的值,有一条符合come_state   =   1   and   AdId   =   ADCount.Id

但现在出现的问题就是,当表IPCount中有come_state=1存在时,就取不到记录,也不报错,我把come_state   =   0时,就正常了!

还有一种就是,我把AdId   =   AdCount.Id   直接改成我要取的AdId号,也正常!


例如表IPCount中有

Adid                               come_state
1                                       0
1                                       1

表ADCount中有
Id
1

用上面的语句,无法取出来

写成 "Select   ID,(select   count(*)   from   IPCount   where   AdId   =   1),(select   count(*)   from   IPCount   where   come_state   =   1   and   AdId   =   1)   from   AdCount   Order   By   Id   Desc "
就可以!


实在不解,求达人帮忙!分不多,仅表谢意!




------解决方案--------------------
Select
ID,
(select count(*) from IPCount where AdId = ADCount.Id),
(select sum(case come_state when 1 then 1 else 0 end) from IPCount where AdId = ADCount.Id)
from
AdCount
Order
By Id Desc
------解决方案--------------------

Select
A.ID,
Count(*) As Count1,
SUM(Case come_state When 1 Then 1 Else 0 End) As Count2
From
AdCount A
Inner Join
IPCount B
On A.Id = B.AdId
Group By A.Id
Order By A.Id Desc
------解决方案--------------------
Select ID,(select count(*) from IPCount where AdId = Id),(select count(*) from IPCount where come_state = 1 and AdId = Id) from AdCount Order By Id Desc

这样

------解决方案--------------------
什么非要写成这样，直接用两张表关联一次不就行了？？？
select b.id, count(*),
sum( a.come_state
when '0 ' then 1 --只统计come_state=0的用户
else 0
end
) as ct1
From IPCount a, adCount b
where b.id = a.adid
group by b.id