表 table
id categoryid severityId name
1 1 1 name1
2 1 2 name2
3 1 3 name3
4 2 1 name4
5 2 2 name5如何实现下面结果
categoryid severityId name
1 3 name3
2 2 name5
也就是说
categoryid 重复时,取severityId 最大的纪录
id categoryid severityId name
1 1 1 name1
2 1 2 name2
3 1 3 name3
4 2 1 name4
5 2 2 name5如何实现下面结果
categoryid severityId name
1 3 name3
2 2 name5
也就是说
categoryid 重复时,取severityId 最大的纪录
where (categoryid,severityId)
in
(
select categoryid,max(severityId)
from table
group by categoryid
)
(
select categoryid,max(severityId) as severityId
from table
group by categoryid
) b
where a.categoryid=b.categoryid and a.severityId=b.severityId
from table
group by categoryid
group by categoryid) a,categoryid b
where a.categoryid=b.categoryid
and a.severityId2=b.severityId
select *
from (
select t.*
,row_number()over(partition by categoryid order by severityId desc ) rn
from table t
)where rn=1
select M.* from test_xwh M ,(select A,max(B) B from test_xwh N group by A having count(A)>1) N where M.A=N.A and M.B=N.B
select M.* from table M ,(select categoryid,max(severityId) severityId from test_xwh N group by categoryid having count(categoryid)>1) N where M.categoryid=N.categoryid and M.severityId=N.severityId
select M.* from table M ,(select categoryid,max(severityId) severityId from test_xwh N group by categoryid having count(categoryid)>1) N where M.categoryid=N.categoryid and M.severityId=N.severityId
select 1 from (
( select max(id) from table group by categoryid )v
where t.id=v.id)
SELECT m.*
FROM (
SELECT categoryid,severityId,name,
ROW_NUMBER() OVER (PARTITION BY categoryid ORDER BY name desc) AS SEQ
FROM table
) m
WHERE m.SEQ = 1
/
1。
select *
from (
select t.*
,row_number()over(partition by categoryid order by severityId desc ) rn
from table t
)where rn=1
2。再有就是利用子查询
select * from table
where (categoryid,severityId)
in
(
select categoryid,max(severityId)
from table
group by categoryid
)
max(severityid) over(partition by categoryid) severityId,
max(name) over(partition by categoryid) name
from table;