bt设计给的难题（SQL）

Question

有一个笨办法，修改表结构，为副评审建议个ViceSyndics和ViceSyndicDetails两个表，ViceSyndics有一个Key , 它被Report_Info参考，ViceSyndicDetails表中有一个ViceSyndics_ID,还有FirstViceSyndic_ID和SecondViceSyndic_ID两个字段，记录两个评审。不知我说清除没有。忽略其他表和你所说的外键关系，我的例子是这样的ViceSyndics
-------
ViceSyndics_ID
1ViceSyndicDetails
-----------
ViceSyndics_ID    FirstViceSyndic_ID  SecondViceSyndic_ID
1                 副一                副二
1                 副三                副四
1                 副五ReportInfo
----------
Report_ID Syndics_ID
如何起床  1

Answer 1

应该是：
ReportInfo
----------
Report_ID ViceSyndics_ID
如何起床  1

Answer 2

这样做的缺点就是
1.表设计过于琐碎和被动，被报表，界面等牵住了鼻子。
2.填写八个副评审的时候的逻辑复杂了。FirstViceSyndic_ID <> :p_Syndic and SecondViceSyndic_ID <> :p_Syndic等的判断，新建还是更新。Update时，FirstViceSyndic_ID IS NULL的判断，填First还是填Second。等等。

Answer 3

1。表结构
CREATE TABLE ZHOUXY.REPORT_INFO
(
    REPORT_ID                      CHAR(4),
    AUTHOR_ID                      CHAR(5),
    JUDGE_ID                       CHAR(4),
    JUDGE_DIV                      NUMBER(1,0),
    REPORT_TITLE                   CHAR(8)
)
/CREATE TABLE ZHOUXY.REGISTER_BASE
(
    REGIST_ID                      CHAR(5),
    NAME                           VARCHAR2(10)
)
/CREATE TABLE ZHOUXY.STAFF_DETAIL
(
    STAFF_ID                       CHAR(4),
    NAME                           VARCHAR2(10)
)
/2。测试数据A.表REPORT_INFO
INSERT INTO REPORT_INFO values('0001','SY001','F001',1,'如何睡觉')
/
INSERT INTO REPORT_INFO values('0001','SY001','M001',0,'如何睡觉')
/
INSERT INTO REPORT_INFO values('0003','SY002','M003',0,'如何毕业')
/
INSERT INTO REPORT_INFO values('0003','SY002','F00A',1,'如何毕业')
/
INSERT INTO REPORT_INFO values('0003','SY002','F00B',1,'如何毕业')
/
INSERT INTO REPORT_INFO values('0002','BY001','M002',0,'如何起床')
/
INSERT INTO REPORT_INFO values('0002','BY001','F001',1,'如何起床')
/
INSERT INTO REPORT_INFO values('0002','BY001','F002',1,'如何起床')
/
INSERT INTO REPORT_INFO values('0002','BY001','F005',1,'如何起床')
/
INSERT INTO REPORT_INFO values('0002','BY001','F003',1,'如何起床')
/
INSERT INTO REPORT_INFO values('0002','BY001','F004',1,'如何起床')
/B.表REGISTER_BASE
INSERT INTO REGISTER_BASE values('SY001','张三')
/
INSERT INTO REGISTER_BASE values('BY001','里斯')
/C.表STAFF_DETAIL
INSERT INTO STAFF_DETAIL values('M001','主一')
/
INSERT INTO STAFF_DETAIL values('F001','副一')
/
INSERT INTO STAFF_DETAIL values('M002','主二')
/
INSERT INTO STAFF_DETAIL values('M003','主三')
/
INSERT INTO STAFF_DETAIL values('F002','副二')
/
INSERT INTO STAFF_DETAIL values('F003','副三')
/
INSERT INTO STAFF_DETAIL values('F004','副四')
/
INSERT INTO STAFF_DETAIL values('F005','副五')
/
INSERT INTO STAFF_DETAIL values('F00A','副A')
/
INSERT INTO STAFF_DETAIL values('F00B','副B')
/
3。SQL
SELECT 
        C.REPORT_ID , 
        C.AUTHOR_ID , 
        NVL ( E.NAME , '无名1' ) AUTHOR_NAME, 
--    C.JUDGE_ID , 
        NVL ( C.NAME , '无名2' ) JUDGE_NAME1, 
--     D.JUDGE_ID2 , 
        NVL ( D.NAME2 , '无名3' ) JUDGE_NAME2, 
--     D.JUDGE_ID3 , 
        D.NAME3  JUDGE_NAME3, 
        C.REPORT_TITLE 
FROM 
        ( 
        SELECT 
                A.* , 
                A2.NAME 
        FROM 
                REPORT_INFO A , 
                STAFF_DETAIL A2 
        WHERE 
                JUDGE_DIV = 0 
                AND A.JUDGE_ID = A2.STAFF_ID ( + ) 
        ORDER BY 
                REPORT_ID , 
                JUDGE_DIV , 
                JUDGE_ID ) C , 
        ( 
        SELECT 
                REPORT_ID , 
                AUTHOR_ID , 
                JUDGE_ID JUDGE_ID2 , 
                NAME NAME2 , 
                ( 
                SELECT 
                        JUDGE_ID 
                FROM 
                        ( 
                        SELECT 
                                A.* , 
                                A2.NAME , 
                                RANK ( ) OVER ( PARTITION BY REPORT_ID 
                                ORDER BY 
                                        REPORT_ID , 
                                        JUDGE_DIV , 
                                        JUDGE_ID ) RANK 
                        FROM 
                                REPORT_INFO A , 
                                STAFF_DETAIL A2 
                        WHERE 
                                A.JUDGE_DIV = 1 
                                AND A.JUDGE_ID = A2.STAFF_ID ( + ) 
                        ORDER BY 
                                A.REPORT_ID , 
                                A.JUDGE_DIV , 
                                A.JUDGE_ID ) B1 
                WHERE 
                        B1.RANK = B.RANK + 1 
                        AND B.REPORT_ID = B1.REPORT_ID ) JUDGE_ID3 , 
                ( 
                SELECT 
                        NAME 
                FROM 
                        ( 
                        SELECT 
                                A.* , 
                                A2.NAME , 
                                RANK ( ) OVER ( PARTITION BY REPORT_ID 
                                ORDER BY 
                                        REPORT_ID , 
                                        JUDGE_DIV , 
                                        JUDGE_ID ) RANK 
                        FROM 
                                REPORT_INFO A , 
                                STAFF_DETAIL A2 
                        WHERE 
                                A.JUDGE_DIV = 1 
                                AND A.JUDGE_ID = A2.STAFF_ID ( + ) 
                        ORDER BY 
                                A.REPORT_ID , 
                                A.JUDGE_DIV , 
                                A.JUDGE_ID ) B1 
                WHERE 
                        B1.RANK = B.RANK + 1 
                        AND B.REPORT_ID = B1.REPORT_ID ) NAME3 , 
                REPORT_TITLE 
        FROM 
                ( 
                SELECT 
                        A.* , 
                        A2.NAME , 
                        RANK ( ) OVER ( PARTITION BY REPORT_ID 
                        ORDER BY 
                                REPORT_ID , 
                                JUDGE_DIV , 
                                JUDGE_ID ) RANK 
                FROM 
                        REPORT_INFO A , 
                        STAFF_DETAIL A2 
                WHERE 
                        A.JUDGE_DIV = 1 
                        AND A.JUDGE_ID = A2.STAFF_ID ( + ) 
                ORDER BY 
                        A.REPORT_ID , 
                        A.JUDGE_DIV , 
                        A.JUDGE_ID ) B 
        WHERE 
                MOD ( RANK , 2 ) = 1 ) D , 
        REGISTER_BASE E 
WHERE 
        C.REPORT_ID = D.REPORT_ID 
        AND C.AUTHOR_ID = E.REGIST_ID ( + ) 
ORDER BY 
        C.REPORT_ID , 
        C.AUTHOR_ID , 
        C.JUDGE_ID , 
        D.JUDGE_ID2 , 
        D.JUDGE_ID3 4。测试环境
WINDOWSXP + Oracle 9.2.05。测试结果
REPORT_ID AUTHOR_ID AUTHOR_NAME JUDGE_NAME1 JUDGE_NAME2 JUDGE_NAME3 REPORT_TITLE
0001 SY001 张三 主一 副一 如何睡觉 
0002 BY001 里斯 主二 副一 副二 如何起床
0002 BY001 里斯 主二 副三 副四 如何起床
0002 BY001 里斯 主二 副五 如何起床
0003 SY002 无名1 主三 副A 副B 如何毕业 6。其他
写出的SQL里面，注释了评审ID,需要的话，可以注释出来。