关于同ID对应多条数据时按规则取一条数据的问题

0
------旅客信息表
create table passenger_certificate(
PID INT,--主键
PNAME VARCHAR2(20),--旅客姓名
PNUM VARCHAR2(30),--证件号码
CREATE_DATE DATE
);
insert into passenger_certificate values (1,'AAA','04603735M',TO_DATE('20130628','YYYY-MM-DD'));
insert into passenger_certificate values (1,'AAA','G60251594',TO_DATE('20130629','YYYY-MM-DD'));
insert into passenger_certificate values (2,'BBB','110105197007081818',TO_DATE('20130628','YYYY-MM-DD'));
insert into passenger_certificate values (2,'BBB','G60250022',TO_DATE('20130628','YYYY-MM-DD'));
insert into passenger_certificate values (3,'CCC','421125198808081919',TO_DATE('20130628','YYYY-MM-DD'));

---电子票号表
create table electronic_ticket(
EID INT,
PID INT,
TNO NUMBER --票号
);
INSERT INTO electronic_ticket VALUES(1,1,'4792459249070');
INSERT INTO electronic_ticket VALUES(2,2,'4980812219070');
INSERT INTO electronic_ticket VALUES(3,3,'4267810283021');

SELECT P.PNAME, P.PNUM, P.CREATE_DATE, T.TNO
FROM passenger_certificate P, electronic_ticket T
WHERE P.PID = T.PID

**
  • --目前状况：**
同一票号可能对应多个证件号码，因为旅客可能用到身份证，护照，等证件号。
现在想一个票号 只取一个证件号即可。
---取数规则--
1.同一票号对应多个证件号时,以身份证优先，即length(PNUM)>=15位优先
2.如果对应多个证件号，且都不为身份证，按最新的CREATE_DATE 取。

. 最终结果集应如下：

PNAME PNUM CREATE_DATE TNO
AAA G60251594 2013/6/29 4792459249070
BBB 110105197007081000 2013/6/28 4980812219070
CCC 421125198808081919 2013/6/28 4267810283021

2014-08-08 添加评论
分享

没有找到相关结果

    已邀请:
    1

    梁勇 - 天道酬勤、上善若水。爱好商业智能 2014-08-11 回答

    可以通过 ROW_NUMBER() OVER(PARTITION BY PNAME ORDER BY CREATE_DATE DESC)的方式加上UNION ALL的方式来实现。具体请参考如下

    
WITH CTE AS (
    

    
SELECT P.PNAME, P.PNUM, P.CREATE_DATE, T.TNO
    
FROM passenger_certificate P, electronic_ticket T
    
WHERE P.PID = T.PID
    
),CTE2 AS (
    
SELECT A.*  ,ROW_NUMBER() OVER(PARTITION BY PNAME ORDER BY CREATE_DATE  DESC) AS RN FROM CTE A
    
)
    
SELECT  PNAME, PNUM, CREATE_DATE, TNO FROM CTE2 WHERE length(PNUM)<15 AND RN = 1   ---规则2
    
UNION ALL
    
SELECT * FROM CTE  WHERE length(PNUM)>=15  ----规则1


    1.gif
    2014-08-11 1
    分享
    1

    caiyutao - ODI、BIEE高级顾问、数据仓库架构 2014-08-11 回答

    SELECT t.PNAME,t.PNUM,t.CREATE_DATE,t.TNO FROM (
    SELECT P.PNAME, P.PNUM, P.CREATE_DATE, T.TNO,row_number()OVER(PARTITION BY T.TNO ORDER BY CASE WHEN LENGTH(p.pnum)>=15 THEN 1 ELSE 2 END, P.CREATE_DATE DESC) ROW_NUM
    FROM passenger_certificate P, electronic_ticket T
    WHERE P.PID = T.PID
    ) t
    WHERE t.ROW_NUM=1
    ORDER BY t.PNAME,t.PNUM,t.CREATE_DATE,t.TNO;
    2014-08-11 2
    分享
    为什么被折叠? 0 个回复被折叠

    要回复问题请先登录或注册