超级复杂sql题目

Question

超级复杂sql题目
ID：序号，唯一；C_NO：客户号（主键）；C_AT1：客户属性1；C_AT2：客户属性2；ID_C_NO：合并后的客户序号要求：客户属性相同，则该2个或以上客户为同一客户，并且客户间可以相互合并，合并后的客户序号(ID_C_NO)等于这些客户中的最大序号(ID)。说明：
(1)、属性c_at1 = 'A'，客户c1、c2为同一个客户，则客户c1、c2的客户序号(ID_C_NO)均等于2(取c1、c2的最大序号(ID))
(2)、属性c_at1 = 'C'，客户c4、c8为同一个客户，则客户c4、c8的客户序号(ID_C_NO)均等于8(取c4、c8的最大序号(ID))
(3)、属性c_at1 = 'E'，客户c3、c5、c6、c7为同一个客户，则客户c3、c5、c6、c7的客户序号(ID_C_NO)均等于7(取c3、c5、c6、c7的最大序号(ID))(4)、属性c_at2 = 'B'，客户c2、c3为同一个客户，则客户c2、c3的客户序号(ID_C_NO)均等于3(取c2、c3的最大序号(ID))
(5)、属性c_at2 = 'D'，客户c4、c5为同一个客户，则客户c4、c5的客户序号(ID_C_NO)均等于5(取c4、c5的最大序号(ID))
(6)、属性c_at2 = 'F'，客户c6、c7为同一个客户，则客户c6、c7的客户序号(ID_C_NO)均等于7(取c6、c7的最大序号(ID))(7)、根据(1)、(3)、(4)，客户c1、c2、c3、c5、c6、c7为同一个客户，则客户c1、c2、c3、c5、c6、c7的客户序号(ID_C_NO)均等于7(取c1、c2、c3、c5、c6、c7的最大序号(ID))
(8)、根据(2)、(5)，客户c4、c5、c8为同一个客户，则客户c4、c5、c8的客户序号(ID_C_NO)均等于8(取c4、c5、c8的最大序号(ID))
(9)、根据(7)、(8)，客户c1、c2、c3、c4、c5、c6、c7、c8为同一个客户，则客户c1、c2、c3、c4、c5、c6、c7、c8的客户序号(ID_C_NO)均等于8(取c1、c2、c3、c4、c5、c6、c7、c8的最大序号(ID))
源表：
ID        C_NO        C_AT1        C_AT2        ID_C_NO
---------------------------------------------------------------------
1           c1              A                
2           c2              A                B        
3           c3              E                B        
4           c4              C                D        
5           c5              E                D        
6           c6              E                F        
7           c7              E                F        
8           c8              C                最终结果：
ID        C_NO        C_AT1        C_AT2        ID_C_NO
---------------------------------------------------------------------
1           c1              A                                  8
2           c2              A                B                 8
3           c3              E                B                 8        
4           c4              C                D                 8
5           c5              E                D                 8
6           c6              E                F                  8
7           c7              E                F                 8
8           c8              C                                  8
这真是个很复杂的sql题目呀，想了N天了，都没有弄出来 
预计论坛里面能解答这个sql题目的人也不多吧 
诚恳的跪求，各位高手，不求您用一条sql能解答出来，就是用一个sp能解答出来也行 

Answer 1

CREATE TABLE test(
    ID INT,
    C_NO CHAR(3),
    C_AT1 CHAR(1),
    C_AT2 CHAR(1),
    ID_C_NO INT
);INSERT INTO test VALUES(1,'C1','A','',0);
INSERT INTO test VALUES(2,'C2','A','B',0);
INSERT INTO test VALUES(3,'C3','E','B',0);
INSERT INTO test VALUES(4,'C4','C','D',0);
INSERT INTO test VALUES(5,'C5','E','D',0);
INSERT INTO test VALUES(6,'C6','E','F',0);
INSERT INTO test VALUES(7,'C7','E','F',0);
INSERT INTO test VALUES(8,'C8','C','',0);
INSERT INTO test VALUES(30,'C30','W','',0);--參數說明：c_at:C_AT1屬性的值,current_val:對應ID屬性的值
CREATE OR REPLACE FUNCTION callback_test(c_at IN CHAR,current_val INT)
RETURN INT
AS
    maxid INT;
BEGIN   
    maxid :=  current_val;
    FOR X IN (
        SELECT DISTINCT id,at_tmp FROM (
            SELECT id,c_at2 at_tmp FROM test WHERE (c_at1 = c_at OR c_at2=c_at) --AND c_at2 IS NOT NULL
            UNION 
            SELECT id,c_at1 at_tmp FROM test WHERE (c_at1 = c_at OR c_at2=c_at)  --AND c_at2 IS NOT NULL
        ) A ORDER BY id,at_tmp
    )     
    LOOP        
        IF (X.Id > maxid) THEN 
            IF c_at <> X.at_tmp THEN   
                maxid := callback_test(X.at_tmp,X.Id + 1);
            END IF;
        ELSIF X.id = maxid THEN
            IF X.at_tmp <> c_at THEN
               maxid := callback_test(X.at_tmp,X.Id + 1);
            END IF; 
        END IF;      
    END LOOP;        
       
    RETURN maxid;
END;--測試代碼
SELECT callback_test('A',1) FROM dual;
SELECT callback_test('A',2) FROM dual;
SELECT callback_test('E',3) FROM dual;
SELECT callback_test('C',4) FROM dual;
SELECT callback_test('E',5) FROM dual;
SELECT callback_test('A',7) FROM dual;
SELECT callback_test('E',7) FROM dual;樓主測試看可以嗎？

Answer 2

使用开窗函数，一个SQL就应该可以实现。

Answer 3

-- 递归查询，connect by

Answer 4

--借二楼的数据一用
select id,
       c_no,
       c_at1,
       c_at2,
       max(id_c_no) id_c_no
from (select id,
             c_no,
             c_at1,
             c_at2,
             substr(id_c_no, 
                     instr(id_c_no, '/', 1, 1) + 1, 
                     (instr(id_c_no, '/', 1, 2) - instr(id_c_no, '/', 1, 1) - 1)) id_c_no
      from (select id,
                   c_no,
                   c_at1,
                   c_at2,
                   sys_connect_by_path(id,'/')||'/' id_c_no
            from test
            start with c_at2 is null
            connect by  nocycle prior c_at1=c_at1 or prior c_at2=c_at2))
group by id,c_no,c_at1,c_at2
order by id
       ID C_N C C ID_C_NO
--------- --- - - ------------
        1 C1  A   8
        2 C2  A B 8
        3 C3  E B 8
        4 C4  C D 8
        5 C5  E D 8
        6 C6  E F 8
        7 C7  E F 8
        8 C8  C   8
       30 C30 W   30

Answer 5

顶一个，一条语句，很强大！
同时提个疑点，我这边测试的执行结果里的ID=1的结果ID_C_NO是1，而不是8。期待给出更强大的SQL。

Answer 6

我用的是您提供的数据啊！
我这边的结果好像正常
你把start with 改成start with c_at1=c_at1  看看

Answer 7

解答不事情正确，如果表里面加上2条一下数据，c9～c10均为10哦
insert into test(ID, C_NO, C_AT1, C_AT2)
values (9, 'c9', 'K', 'K');insert into test (ID, C_NO, C_AT1, C_AT2)
values (10, 'c10', 'K', 'K');

Answer 8

--再修改了下
 select id,
        c_no,
        c_at1,
        c_at2,
        max(to_number(id_c_no)) id_c_no
 from (select id,
              c_no,
              c_at1,
              c_at2,
              substr(id_c_no,
                     instr(id_c_no, '/', 1, 1) + 1,
                     (instr(id_c_no, '/', 1, 2) - instr(id_c_no, '/', 1, 1) - 1)) id_c_no
       from (select id,
                    c_no,
                    c_at1,
                    c_at2,
                    sys_connect_by_path(id,'/')||'/' id_c_no
             from test
             start with id=id
             connect by  nocycle prior c_at1=c_at1 or prior c_at2=c_at2 and prior id<>id))
 group by id,c_no,c_at1,c_at2
 order by id