學(xué)數(shù)學(xué)的時(shí)候都知道有交集和差集的概念。
用SQL語(yǔ)句來(lái)表示交集和差集的時(shí)候會(huì)遇到很多小困難，比如條件有重復(fù)，編號(hào)有空等等。
最近在處理一些歌曲列表是否有版權(quán)的時(shí)候就遇到過(guò)這些困難，現(xiàn)在把它們記錄一下：
copyright_songs表是有版權(quán)的歌曲清單 local_songs表是在用的歌曲清單
生成歌曲名和歌手名都相同的交集用如下SQL：
select distinct t2.id,t1.name,t1.singer,t1.id as local_id,t1.company,
t2.company as copyright_company,t2.type
into same_1
from copyright_songs t2,local_songs t1
where (t1.name=t2.name and t1.singer=t2.singer)
如果歌曲名稱(chēng)里面帶有括號(hào)，如:星語(yǔ)心愿（節(jié)奏版）考試,大提示需要去掉歌曲里面括號(hào)后再比較.
select * into local_songs_old from local_songs
update local_songs set name=left(name,charindex(’(’,name)-1)
where charindex(’(’,name)>0
drop table same_1
select distinct t2.id,t1.name,t1.singer,t1.id as local_id,t1.company,
t2.company as copyright_company,t2.type
into same_1
from copyright_songs t2,local_songs t1
where (t1.name=t2.name and t1.singer=t2.singer)
顯示歌曲名和歌手名都相同歌曲清單(交集)
select * from same_1
顯示歌曲名和歌手名都相同歌曲清單，用原來(lái)帶括號(hào)的歌曲名
select distinct t1.id,t2.name,t1.singer,t1.local_id,t1.company,t1.num,t1.type
from same_1 t1,local_songs_old t2
where t1.t1_id=t2.id
找到?jīng)]有版權(quán)對(duì)應(yīng)的歌曲列表
顯示編號(hào)是否有為空的記錄
select * from local_songs where id is null
select * from same_1 where local_id is null
去掉編號(hào)為空的記錄
delete from local_songs where id is null
delete from same_1 where local_id is null
生成本地歌曲表里面沒(méi)有版權(quán)的歌曲清單(差集)
select * into no_local_songs from local_songs
where id not in (select local_id from same_1)
顯示本地歌曲表里面沒(méi)有版權(quán)的歌曲清單
select * from no_local_songs
沒(méi)有版權(quán)的歌曲就要屏蔽起來(lái)，不能再使用原來(lái)免費(fèi)的互聯(lián)網(wǎng)音樂(lè)來(lái)贏利了。
用SQL語(yǔ)句來(lái)表示交集和差集的時(shí)候會(huì)遇到很多小困難，比如條件有重復(fù)，編號(hào)有空等等。
最近在處理一些歌曲列表是否有版權(quán)的時(shí)候就遇到過(guò)這些困難，現(xiàn)在把它們記錄一下：
copyright_songs表是有版權(quán)的歌曲清單 local_songs表是在用的歌曲清單
生成歌曲名和歌手名都相同的交集用如下SQL：
select distinct t2.id,t1.name,t1.singer,t1.id as local_id,t1.company,
t2.company as copyright_company,t2.type
into same_1
from copyright_songs t2,local_songs t1
where (t1.name=t2.name and t1.singer=t2.singer)
如果歌曲名稱(chēng)里面帶有括號(hào)，如:星語(yǔ)心愿（節(jié)奏版）考試,大提示需要去掉歌曲里面括號(hào)后再比較.
select * into local_songs_old from local_songs
update local_songs set name=left(name,charindex(’(’,name)-1)
where charindex(’(’,name)>0
drop table same_1
select distinct t2.id,t1.name,t1.singer,t1.id as local_id,t1.company,
t2.company as copyright_company,t2.type
into same_1
from copyright_songs t2,local_songs t1
where (t1.name=t2.name and t1.singer=t2.singer)
顯示歌曲名和歌手名都相同歌曲清單(交集)
select * from same_1
顯示歌曲名和歌手名都相同歌曲清單，用原來(lái)帶括號(hào)的歌曲名
select distinct t1.id,t2.name,t1.singer,t1.local_id,t1.company,t1.num,t1.type
from same_1 t1,local_songs_old t2
where t1.t1_id=t2.id
找到?jīng)]有版權(quán)對(duì)應(yīng)的歌曲列表
顯示編號(hào)是否有為空的記錄
select * from local_songs where id is null
select * from same_1 where local_id is null
去掉編號(hào)為空的記錄
delete from local_songs where id is null
delete from same_1 where local_id is null
生成本地歌曲表里面沒(méi)有版權(quán)的歌曲清單(差集)
select * into no_local_songs from local_songs
where id not in (select local_id from same_1)
顯示本地歌曲表里面沒(méi)有版權(quán)的歌曲清單
select * from no_local_songs
沒(méi)有版權(quán)的歌曲就要屏蔽起來(lái)，不能再使用原來(lái)免費(fèi)的互聯(lián)網(wǎng)音樂(lè)來(lái)贏利了。