挑戰極限，請絕對高手進！給分100
問題如下：
給出數據庫,整個過程都要求個數>=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
第一步:掃描數據庫D
統計出不同元素的個數，也就是
1 2 3 4 5 6 (不包括0),分別出現的
個數，并且要求出現的個數>=3,得到
的結果如下：
L1
------------
item1 個數
1 5
2 6
3 7
4 5
5 3
6 4
-----------
解答:
SELECT item as item1,SUM(CNT) as 個數
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
再掃描數據庫D統計C2中各行元素出現的
次數
C2
--------------------
item1 item2 個數
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 個數
into #C2 from C2 m
說明：以C2中的1 2為例
因為1 2同時（不管順序如何）出現在數據庫D
中的3 7 行，所以個數為2，再以1 3為例，同時
出現在數據庫D中的1 3 7 8行，所以個數為4,依
次類推....
由
C2選出個數>=3的，得到
L2
--------------------
item1 item2 個數
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 個數>=3
select * from L2
由L2（各行元素）掃描數據庫（為了簡化數據庫D），把
不包含L2中的項從數據庫中刪除，因為L2中每
一行元素的個數為2（不包含“個數”這一列）
所以把D中的第九行刪除，又因為數據庫D中第
四行只有2 6這兩個元素，而L2中沒有這個2 6
所以把D中的第四行也刪除，此時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.個數 or b=m.個數 or c=m.個數 or d=m.個數 or e=m.個數)
select * from D
接下來再統計L2（掃描L2）中各不同元素出現的次數
（為了簡化L2）。
各元素出現的次數
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
因為L2中每一行包含兩個元素（不算“個數”這一列），而
5這個元素在L2中只出現了一次，所以，就把L2中所有包含5
的行刪除，簡化后的得到L2'
L2'
--------------------
item1 item2 個數
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