挑战极限，请绝对高手进！给分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