请教一个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