ID NAME AGE TYPE DATADATE TNUM1 KING 10 A1 2010-10-12 50000
1 KING 12 Az 2010-9-10 2432
1 KING 25 A1 2010-10-12 3000
3 BEN 25 A1 2010-12-23 123
3 BEN 14 Az 2010-12-1 5000
2 ANN 32 Az 2010-8-1 33333
2 ANN 23 Aa 2010-11-13 23456
我想查询出当TYPE为A1直接取A1这行,如果出现同一ID下又两个TYPE为A1的比较DATADATE,如果DATADATE相同时取TNUM为最大的。
如果同一ID下TYPE没有A1,取TYPE为最小的。希望最终结果为
ID NAME AGE TYPE DATADATE TNUM
1 KING 10 A1 2010-10-12 50000
3 BEN 25 A1 2010-12-23 123
2 ANN 23 Aa 2010-11-13 23456保证同一ID下只有一行。
1 KING 12 Az 2010-9-10 2432
1 KING 25 A1 2010-10-12 3000
3 BEN 25 A1 2010-12-23 123
3 BEN 14 Az 2010-12-1 5000
2 ANN 32 Az 2010-8-1 33333
2 ANN 23 Aa 2010-11-13 23456
我想查询出当TYPE为A1直接取A1这行,如果出现同一ID下又两个TYPE为A1的比较DATADATE,如果DATADATE相同时取TNUM为最大的。
如果同一ID下TYPE没有A1,取TYPE为最小的。希望最终结果为
ID NAME AGE TYPE DATADATE TNUM
1 KING 10 A1 2010-10-12 50000
3 BEN 25 A1 2010-12-23 123
2 ANN 23 Aa 2010-11-13 23456保证同一ID下只有一行。
SELECT 1 ID,'KING' NAME,10 AGE,'A1' TYPE,TO_DATE('2010-10-12','YYYY-MM-DD') DATADATE,50000 TNUM FROM DUAL
UNION ALL
SELECT 1 ID,'KING' NAME,12 AGE,'AZ' TYPE,TO_DATE('2010-9-10','YYYY-MM-DD') DATADATE,2432 TNUM FROM DUAL
UNION ALL
SELECT 1 ID,'KING' NAME,25 AGE,'A1' TYPE,TO_DATE('2010-10-12','YYYY-MM-DD') DATADATE,3000 TNUM FROM DUAL
UNION ALL
SELECT 3 ID,'BEN' NAME,25 AGE,'A1' TYPE,TO_DATE('2010-12-23','YYYY-MM-DD') DATADATE,123 TNUM FROM DUAL
UNION ALL
SELECT 3 ID,'BEN' NAME,14 AGE,'AZ' TYPE,TO_DATE('2010-12-1','YYYY-MM-DD') DATADATE,5000 TNUM FROM DUAL
UNION ALL
SELECT 2 ID,'ANN' NAME,32 AGE,'AZ' TYPE,TO_DATE('2010-8-1','YYYY-MM-DD') DATADATE,33333 TNUM FROM DUAL
UNION ALL
SELECT 2 ID,'ANN' NAME,23 AGE,'Aa' TYPE,TO_DATE('2010-11-13','YYYY-MM-DD') DATADATE,23456 TNUM FROM DUAL
)
SELECT * FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATADATE DESC,TYPE,TNUM DESC) RR,
T.*
FROM TAB T) WHERE RR=1 ORDER BY TYPE
SELECT *
FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATADATE DESC,TYPE,TNUM DESC) RR,
T.*
FROM TAB T
) WHERE RR=1
ORDER BY TYPE
楼主看完这个就行了 ,你懂的
with tb as(
select 1 ID,'KING' NAME,10 AGE,'A1' TYPE,DATE'2010-10-12' DATADATE,50000 TNUM from dual union all
select 1, 'KING', 12, 'Az', date'2010-9-10', 2432 from dual union all
select 1, 'KING', 25, 'A1', date'2010-10-12', 3000 from dual union all
select 3, 'BEN', 25, 'A1', date'2010-12-23', 123 from dual union all
select 3, 'BEN', 14, 'Az', date'2010-12-1', 5000 from dual union all
select 2, 'ANN', 32, 'Az', date'2010-8-1', 33333 from dual union all
select 2, 'ANN', 23, 'Aa', date'2010-11-13', 23456 from dual)
select * from (
select row_number() over(partition by id order by nullif(type,'A1') nulls first,datadate desc,tnum desc) rn,
tb.* from tb) where rn=1 order by id
RN ID NAME AGE TY DATADATE TNUM
---------- ---------- ---- ---------- -- ------------------- ----------
1 1 KING 10 A1 2010-10-12 00:00:00 50000
1 2 ANN 23 Aa 2010-11-13 00:00:00 23456
1 3 BEN 25 A1 2010-12-23 00:00:00 123
with tb as(
select 1 ID,'KING' NAME,10 AGE,'A1' TYPE,DATE'2010-10-12' DATADATE,50000 TNUM from dual union all
select 1, 'KING', 12, 'Az', date'2010-12-10', 2432 from dual union all
select 1, 'KING', 25, 'A1', date'2010-10-12', 3000 from dual union all
select 3, 'BEN', 25, 'A1', date'2010-12-23', 123 from dual union all
select 3, 'BEN', 14, 'A0', date'2010-12-23', 5000 from dual union all
select 2, 'ANN', 32, 'A0', date'2010-8-1', 33333 from dual union all
select 2, 'ANN', 23, 'Aa', date'2010-11-13', 23456 from dual)
SELECT *
FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATADATE DESC,TYPE,TNUM DESC) RR,
T.*
FROM TB T
) WHERE RR=1
ORDER BY TYPE
RR ID NAME AGE TY DATADATE TNUM
---------- ---------- ---- ---------- -- ------------------- ----------
1 3 BEN 14 A0 2010-12-23 00:00:00 5000
1 2 ANN 23 Aa 2010-11-13 00:00:00 23456
1 1 KING 12 Az 2010-12-10 00:00:00 2432