/*[type]列是优先级列,对于同一个id,取优先级大的记录。优先级顺序:High>med>low>unknown*/create table tb(id int,[type] varchar(20))
insert into tb values
(1600831,'High'),
(1600831,'low'),
(1600831,'med'),
(28482,'med'),
(28482,'low'),
(28482,'Unknown'),
(1715742,'low'),
(695071,'Unknown'),
(1234,'Unknown'),
(695071,'High');/*
1234,'Unknown'
1600831,'High'
28482,'med'
1715742,'low'
695071,'High'
*/
insert into tb values
(1600831,'High'),
(1600831,'low'),
(1600831,'med'),
(28482,'med'),
(28482,'low'),
(28482,'Unknown'),
(1715742,'low'),
(695071,'Unknown'),
(1234,'Unknown'),
(695071,'High');/*
1234,'Unknown'
1600831,'High'
28482,'med'
1715742,'low'
695071,'High'
*/
create table tb(id int,[type] varchar(20))
insert into tb values
(1600831,'High'),
(1600831,'low'),
(1600831,'med'),
(28482,'med'),
(28482,'low'),
(28482,'Unknown'),
(1715742,'low'),
(695071,'Unknown'),
(1234,'Unknown'),
(695071,'High')SELECT id,type
FROM (SELECT ID,type,CASE type WHEN 'High' THEN 1
WHEN 'med' THEN 2
WHEN 'low' THEN 3
WHEN 'Unknown' THEN 4 END AS Level
FROM tb) AS T
WHERE (SELECT COUNT(1) FROM (SELECT ID,type,CASE type WHEN 'High' THEN 1
WHEN 'med' THEN 2
WHEN 'low' THEN 3
WHEN 'Unknown' THEN 4 END AS Level
FROM tb) AS A WHERE A.id = T.id AND A.Level < T.Level) < 1id type
1600831 High
28482 med
1715742 low
1234 Unknown
695071 High
with t
as(
select *,
case when [type]='unknown' then
when [type]='low' then 2
when [type]='med' then 3 else 4 end as 优先级
from tbl
)
select id,[type] from t a
where not exists(select 1 from t b where a.id=b.id and a.优先级<b.优先级)
(select id,[type]
,case when [type]='High' then 1 when [type]='med' then 2 when [type]='low' then 3 when [type]='low' then 4 end xorder
from tb) a where a.xorder=(select min(xorder) from (select id,[type]
,case when [type]='High' then 1 when [type]='med' then 2 when [type]='low' then 3 when [type]='low' then 4 end xorder
from tb) c where a.id=c.id)