我想到的是用case when 把STERE和KG转换成一个标识的字符,然后根据D字段排序取第一条
如果A字段没有重复,可以使用下面的方法,
如果A字段有重复,可以通过下面的方法先取到STERE和KG的最大值,然后union all其他值create table t1
(
id varchar(10),
name varchar(10),
num number
)insert into t1 values ('A','AAA',50);
insert into t1 values ('B','BBB',60);
insert into t1 values ('C','CCC',50);
insert into t1 values ('D','DDD',60);select *
from (select t.*, row_number() over(partition by id2 order by ID) as rm
from (select case
when id = 'A' then
'spcial'
when id = 'B' then
'spcial'
else
ID
end as ID2,
id,
name,
num
from t1) t)
where rm = 1
如果A字段没有重复,可以使用下面的方法,
如果A字段有重复,可以通过下面的方法先取到STERE和KG的最大值,然后union all其他值create table t1
(
id varchar(10),
name varchar(10),
num number
)insert into t1 values ('A','AAA',50);
insert into t1 values ('B','BBB',60);
insert into t1 values ('C','CCC',50);
insert into t1 values ('D','DDD',60);select *
from (select t.*, row_number() over(partition by id2 order by ID) as rm
from (select case
when id = 'A' then
'spcial'
when id = 'B' then
'spcial'
else
ID
end as ID2,
id,
name,
num
from t1) t)
where rm = 1
FROM TABLE1 T
WHERE NOT EXISTS (SELECT 1
FROM (SELECT A, ROW_NUMBER() OVER(ORDER BY D DESC) RN
FROM TABLE1
WHERE A IN ('STERE', 'KG')) X
WHERE T.A = X.A
AND X.RN > 1)