(Reproduced) to challenge limits, into absolute master! To the sub-100
(Reproduced) to challenge limits, into absolute master! To the sub-100
To challenge limits, into absolute master! To the sub-100
Questions are as follows:
Given database, the entire process requires the number of> = 3
D
————–
Id abcde
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
————–
— Built Table script:
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
Step 1: D scanning database
The statistics of the number of different elements, that is,
1 2 3 4 5 6 (excluding 0), respectively in the number of, and demands on the number of> = 3, the results are as follows:
L1
————
Number item1
1 5
2 6
3 7
4 5
5 3
6 4
———–
Solution:
SELECT item as item1, SUM (CNT) as the number of
Into L1 FROM
(
Select a as item, the 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
Step 2:
L1 since connections are
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
————–
Solution:
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 statistical database to scan in the C2 elements in the number of firms
C2
——————–
Number 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 the number of
Into # C2 from C2 m
Note: C2 in 1 2 as an example because 1 2 at the same time (no matter how sequence) appear in the database D
The 3 7 line, the number is 2, then 1 3 as an example, at the same time in the database D in the 1 3 7 8 trip, the number to four, followed by analogy ….
From
C2 selected number of> = 3, by
L2
——————–
Number 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 the number of> = 3
Select * from L2
By L2 (each element) scanning database (To simplify database D), the L2 does not contain the items deleted from the database because L2 elements in each row number 2 (does not include "the number of" out)
Therefore, the D line in the ninth deleted, because the database D in the fourth line only 6 of these two elements, and the L2 not 2 6
Therefore, the D in the fourth was also deleted, this simplified D D '
D '
————–
Id abcde
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 number or b = = m. m. m. number of the number or c or d = = = m. m. number or e number)
Select * from D
Next to statistics L2 (scanning L2) of the different elements in the number of (To simplify L2).
The number of the elements
1 3
2
3 5
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 because in each row contains two elements (not "number" series), and
5 This element only in L2 there was, therefore, put all L2 contains 5
Delete rows, simplified by the L2 '
L2 '
——————–
Number 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
Posted on 2007-01-25 19:32 Tauruser Reading (79) Comments (0) edit collections cited
Tags: Master