表 DownLog CD SongID Count
------- ------ ------
1 232 2
2 232 1
1 233 1
1 234 1
1 234 2
...表 SongListid Name
------- ----------
232 歌曲一
233 歌曲二
234 歌曲三
...表 CD_SongCD 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语句实现
------- ------ ------
1 232 2
2 232 1
1 233 1
1 234 1
1 234 2
...表 SongListid Name
------- ----------
232 歌曲一
233 歌曲二
234 歌曲三
...表 CD_SongCD 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语句实现
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
A.SongID,
B.SongName,
SUM(A.[Count]) 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
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
A.SongID,
B.SongName,
SUM(A.[Count]) 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 And A.CD = C.CD
Where
A.CD = 1
Group By
A.SongID,
B.SongName
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 and CD=1) then 'Y' else 'N' end) as InCD
from
SongList s,DownLog d
where
s.id=d.SongID and d.CD=1
group by
s.ID,s.Name
Create Table DownLog
(CD Int,
SongID Int,
[Count] Int)
Insert DownLog Select 1, 232, 2
Union All Select 1, 232, 1
Union All Select 2, 233, 1
Union All Select 1, 234, 1
Union All Select 1, 234, 2 Create Table SongList
(id Int,
Name Nvarchar(10))
Insert SongList Select 232, N'歌曲一'
Union All Select 233, N'歌曲二'
Union All Select 234, N'歌曲三'Create Table CD_Song
(CD Int,
SongID Int)
Insert CD_Song Select 1, 231
Union All Select 1, 232
Union All Select 1, 235
GO
Select
A.SongID,
B.Name As SongName,
SUM(A.[Count]) 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 And A.CD = C.CD
Where
A.CD = 1
Group By
A.SongID,
B.Name
GO
Drop Table DownLog, SongList, CD_Song
/*
SongID SongName Count InCD
232 歌曲一 3 Y
234 歌曲三 3 N
*/
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
各位老大真快啊!如果 SongList 里找不到 232的歌曲记录,就不显示这条记录 怎么实现呢?
-------------
我的和红尘的都是用的關聯,所以,如果SongList 里找不到 232的歌曲记录,就不會显示这条记录。不同的是得到InCD,红尘用的是子查詢,我用的是左聯接,應該用聯接的效率要優一些些。