挑戰(zhàn)極限，請(qǐng)絕對(duì)高手進(jìn)！給分100
問題如下：
給出數(shù)據(jù)庫,整個(gè)過程都要求個(gè)數(shù)>=3
D
--------------
id a b c d e
1 1 3 4 6 0
2 2 3 4 0 0
3 1 2 3 0 0
4 2 6 0 0 0
5 2 3 4 5 0
6 2 3 5 0 0
7 1 2 3 4 6
8 1 3 4 5 6
9 1 0 0 0 0
--------------
--建表腳本:
create table D
(
id int,
a int,
b int,
c int,
d int,
e int
)
go
insert into D(id, a, b, c, d, e)
select 1 , 1,3,4,6,0 UNION ALL
select 2 , 2,3,4,0,0 UNION ALL
select 3 , 1,2,3,0,0 UNION ALL
select 4 , 2,6,0,0,0 UNION ALL
select 5 , 2,3,4,5,0 UNION ALL
select 6 , 2,3,5,0,0 UNION ALL
select 7 , 1,2,3,4,6 UNION ALL
select 8 , 1,3,4,5,6 UNION ALL
select 9 , 1,0,0,0,0
GO
第一步:掃描數(shù)據(jù)庫D
統(tǒng)計(jì)出不同元素的個(gè)數(shù)，也就是
1 2 3 4 5 6 (不包括0),分別出現(xiàn)的
個(gè)數(shù)，并且要求出現(xiàn)的個(gè)數(shù)>=3,得到
的結(jié)果如下：
L1
------------
item1 個(gè)數(shù)
1 5
2 6
3 7
4 5
5 3
6 4
-----------
解答:
SELECT item as item1,SUM(CNT) as 個(gè)數(shù)
into L1 FROM
(
select a as item,count(a) as CNT from D WHERE a<>0 GROUP BY a
UNION ALL select b,count(b) from D WHERE b<>0 GROUP BY b
UNION ALL select c,count(c) from D WHERE c<>0 GROUP BY c
UNION ALL select d,count(d) from D WHERE d<>0 GROUP BY d
UNION ALL select e,count(e) from D WHERE e<>0 GROUP BY e
) m
GROUP BY item having(SUM(CNT)>=3)
select * from L1
第二步：
L1自連接，得到
C2
-------------
item1 item2
1 2
1 3
1 4
1 5
1 6
2 3
2 4
2 5
2 6
3 4
3 5
3 6
4 5
4 6
5 6
--------------
解答:
select m.item1,n.item1 item2 into C2
from L1 m LEFT JOIN L1 n ON m.item1<n.item1
where m.item1<n.item1 order by m.item1,n.item1
select * from C2
再掃描數(shù)據(jù)庫D統(tǒng)計(jì)C2中各行元素出現(xiàn)的
次數(shù)
C2
--------------------
item1 item2 個(gè)數(shù)
1 2 2
1 3 4
1 4 3
1 5 1
1 6 3
2 3 5
2 4 3
2 5 2
2 6 2
3 4 5
3 5 3
3 6 3
4 5 2
4 6 3
5 6 1
--------------------
select m.item1,m.item2,
(select count(1) from D where
(a=m.item1 or b=m.item1 or c=m.item1 or d=m.item1 or e=m.item1 )
and (a=m.item2 or b=m.item2 or c=m.item2 or d=m.item2 or e=m.item2 )
) as 個(gè)數(shù)
into #C2 from C2 m
說明：以C2中的1 2為例
因?yàn)? 2同時(shí)（不管順序如何）出現(xiàn)在數(shù)據(jù)庫D
中的3 7 行，所以個(gè)數(shù)為2，再以1 3為例，同時(shí)
出現(xiàn)在數(shù)據(jù)庫D中的1 3 7 8行，所以個(gè)數(shù)為4,依
次類推....
由
C2選出個(gè)數(shù)>=3的，得到
L2
--------------------
item1 item2 個(gè)數(shù)
1 3 4
1 4 3
1 6 3
2 3 5
2 4 3
3 4 5
3 5 3
3 6 3
4 6 3
--------------------
select * into L2 from #C2 where 個(gè)數(shù)>=3
select * from L2
由L2（各行元素）掃描數(shù)據(jù)庫（為了簡化數(shù)據(jù)庫D），把
不包含L2中的項(xiàng)從數(shù)據(jù)庫中刪除，因?yàn)長2中每
一行元素的個(gè)數(shù)為2（不包含“個(gè)數(shù)”這一列）
所以把D中的第九行刪除，又因?yàn)閿?shù)據(jù)庫D中第
四行只有2 6這兩個(gè)元素，而L2中沒有這個(gè)2 6
所以把D中的第四行也刪除，此時(shí)D簡化為D'
D'
--------------
id a b c d e
1 1 3 4 6 0
2 2 3 4 0 0
3 1 2 3 0 0
5 2 3 4 5 0
6 2 3 5 0 0
7 1 2 3 4 6
8 1 3 4 5 6
--------------
delete D where id NOT IN
(select n.id from L2 m,D n WHERE a=m.個(gè)數(shù) or b=m.個(gè)數(shù) or c=m.個(gè)數(shù) or d=m.個(gè)數(shù) or e=m.個(gè)數(shù))
select * from D
接下來再統(tǒng)計(jì)L2（掃描L2）中各不同元素出現(xiàn)的次數(shù)
（為了簡化L2）。
各元素出現(xiàn)的次數(shù)
1 3
2 2
3 5
4 4
5 1
6 3
select item1,SUM(cnt) CNT FROM
(
select item1,count(item1) cnt from L2 GROUP BY item1
UNION ALL select item2,count(item2) from L2 GROUP BY item2
) t GROUP BY item1
因?yàn)長2中每一行包含兩個(gè)元素（不算“個(gè)數(shù)”這一列），而
5這個(gè)元素在L2中只出現(xiàn)了一次，所以，就把L2中所有包含5
的行刪除，簡化后的得到L2'
L2'
--------------------
item1 item2 個(gè)數(shù)
1 3 4
1 4 3
1 6 3
2 3 5
2 4 3
3 4 5
3 6 3
4 6 3
--------------------
delete L2 from
(
select item1 FROM
(
select item1,count(item1) cnt from L2 GROUP BY item1
UNION ALL select item2,count(item2) from L2 GROUP BY item2
) t GROUP BY item1 HAVING SUM(cnt)=1
) tmp where L2.item1=tmp.item1 or L2.item2=tmp.item1
select * from L2