比如一个表A(id,B,C,D),有四组值(1,b1,c1,d1),(2,b2,c2,d2),(3,b1,c1,d3),(4,b2,c2,d4),把C下的值当成另一个表E的的列,E(ID,B,c1,c2),之后检索出这种形式:
(1,b1,d1,d3)
(2,b2,d2,d4)
E表建不建都无所谓只要能检出这种形式就行。
(1,b1,d1,d3)
(2,b2,d2,d4)
E表建不建都无所谓只要能检出这种形式就行。
(B,c1,c2)的形式 结果就是:
(b1,d1,d2)
(b2,d3,d4)
INSERT INTO Aa VALUES (1, 'b1', 'c1', 'd1');
INSERT INTO Aa VALUES (2, 'b1', 'c2', 'd2');
INSERT INTO Aa VALUES (3, 'b2', 'c1', 'd3');
INSERT INTO Aa VALUES (4, 'b2', 'c2', 'd4');
INSERT INTO Aa VALUES (5, 'b3', 'c1', 'd5');
INSERT INTO Aa VALUES (6, 'b3', 'c2', 'd6');
SELECT e.f1,e.f2,e.f3 FROM (
SELECT row_number()over(PARTITION BY f.f1 ORDER BY f.f1,f.f2) rn,f.f1,f.f2,f.f3
FROM (
SELECT A1.b f1, A1.d f2, A2.d f3
FROM (SELECT c.b, a.d
FROM Aa a, (SELECT DISTINCT b FROM Aa ORDER BY b) c
WHERE c.b = a.b
ORDER BY c.b, a.d) A1,
(SELECT c.b, a.d
FROM Aa a, (SELECT DISTINCT b FROM Aa ORDER BY b) c
WHERE c.b = a.b
ORDER BY c.b, a.d) A2
WHERE A1.b = A2.b AND a1.d<>a2.d
ORDER BY a1.b,a1.d )f )e
WHERE e.rn=1