表中有如下数据
col1 col2 col3 col4
a aa 1 002
a aa 2 002
a aa 4 002
b bb 1 003
b bb 3 003
c cc 2 003
c cc 0 003
d dd 0 004
d dd 3 004其中第三列是表示优先级 切优先级如下 4>1>3>0>2查询出来的结果是 当col1,col2,col4不重复时所有数据都出来 当col1,col2,col4重复时就只取col3优先级最高的数据
比如上面的数据最后的结果就是
a aa 4 002
b bb 1 003
c cc 0 003
d dd 3 004
求给具体的语句
col1 col2 col3 col4
a aa 1 002
a aa 2 002
a aa 4 002
b bb 1 003
b bb 3 003
c cc 2 003
c cc 0 003
d dd 0 004
d dd 3 004其中第三列是表示优先级 切优先级如下 4>1>3>0>2查询出来的结果是 当col1,col2,col4不重复时所有数据都出来 当col1,col2,col4重复时就只取col3优先级最高的数据
比如上面的数据最后的结果就是
a aa 4 002
b bb 1 003
c cc 0 003
d dd 3 004
求给具体的语句
select col1,col2,max(case when col3 = 4 then 4 when col3 = 1 then 3 when col3 = 3 then 2
when col3 = 0 then 2 when col3 = 2 then 1 else 0 end) col3,col4
from tb
group by col1,col2,col4
;with cte as
(
select col1,col2,(case when col3 = 4 then 4 when col3 = 1 then 3 when col3 = 3 then 2
when col3 = 0 then 2 when col3 = 2 then 1 else 0 end) rno,col3,col4
from tb
)select col1,col2,col3,col4
from cte t
where not exists (select 1 from cte where col1=t.col1 and col2=t.col2 and col4=t.col4 and rno>t.rno)
不知道ORACLE是否可以查詢到。
SELECT 'a'col1,'aa'col2,1 col3,'002'col4 FROM dual
UNION ALL
SELECT 'a','aa',2,'002' FROM dual
UNION ALL
SELECT 'a','aa',4,'002' FROM dual
UNION ALL
SELECT 'b','bb',1,'003' FROM dual
UNION ALL
SELECT 'b','bb',3,'003' FROM dual
UNION ALL
SELECT 'c','cc',2,'003' FROM dual
UNION ALL
SELECT 'c','cc',0,'003' FROM dual
UNION ALL
SELECT 'd','dd',0,'004' FROM dual
UNION ALL
SELECT 'd','dd',3,'004' FROM dual
)
SELECT col1,col2,col3,col4
FROM
(
SELECT
col1,
col2,
col3,
col4,
Row_Number() over (PARTITION BY col1,col2,col4 ORDER BY Decode(col3,4,1,1,2,3,3,0,4,2,5)) rn
FROM t
)WHERE rn=1;output:
COL1, COL2, COL3, COL4
a aa 4 002
b bb 1 003
c cc 0 003
d dd 3 004