表 DownLog
CD SongID Count
------- ------ ------
1 232 2
2 232 1
1 233 1
1 234 1
1 234 2
...
表 SongList
id Name
------- ----------
232 歌曲一
233 歌曲二
234 歌曲三
...
表 CD_Song
CD SongID
------- ----------
1 231
1 232
1 235
.....
要实现的结果示例如下：
条件 CD = 1
1，求相同SongID的 Count 之合
2，查询此SongID是否与CD存在关联
SongID SongName Count InCD
------- --------- ------- ------
232 歌曲一 3 Y
234 歌曲三 3 N
...
求一句SQL语句实现
------解决方案--------------------
Select
A.SongID,
B.SongName,
Count(A.SongID) As [Count],
Max(Case When C.CD Is Null Then 'N ' Else 'Y ' End) As InCD
From
DownLog A
Inner Join
SongList B
On A.SongID = B.id
Left Join
CD_Song C
On A.SongID = C.SongID
Group By
A.SongID,
B.SongName
------解决方案--------------------
select
s.ID as SongID,
s.Name as SongName,
sum(d.Count) as Count,
(case when exists(select 1 from CD_Song where SongID=s.ID) then 'Y ' else 'N ' end) as InCD
from
SongList s,DownLog d
where
s.id=d.SongID
group by
s.ID,s.Name
------解决方案--------------------
select _d.SongID,_s.SongName,sum(count) as count,case when avg(_c.CD) = 1 then 'Y ' else 'N ' end as InCd
from DownLog _d
join SongList _s on _s.id = _d.SongID
left join CD_Song _c on _c.SongID = _s.id and _c.CD = 1
group by _d.SongID,_s.SongName