with t as (select 1 id, 1 num, 1 flag from dual union all select 1 id, 2 num, 2 flag from dual union all select 1 id, 3 num, 3 flag from dual union all select 2 id, 1 num, 1 flag from dual union all select 3 id, 1 num, 1 flag from dual union all select 3 id, 2 num, 2 flag from dual union all select 4 id, 1 num, 1 flag from dual union all select 4 id, 2 num, 3 flag from dual union all select 6 id, 1 num, 1 flag from dual) select t1.*, t2.flag from (select id, max(num) num from t group by id) t1, t t2 where t1.id = t2.id and t1.num = t2.num order by t1.id;
你分组的结果集 再和源表关联下 不就查出flag了
WITH T AS (SELECT 1 ID, 1 NUM, 1 FLAG FROM DUAL UNION ALL SELECT 1 ID, 2 NUM, 2 FLAG FROM DUAL UNION ALL SELECT 1 ID, 3 NUM, 3 FLAG FROM DUAL UNION ALL SELECT 2 ID, 1 NUM, 1 FLAG FROM DUAL UNION ALL SELECT 3 ID, 1 NUM, 1 FLAG FROM DUAL UNION ALL SELECT 3 ID, 2 NUM, 2 FLAG FROM DUAL UNION ALL SELECT 4 ID, 1 NUM, 1 FLAG FROM DUAL UNION ALL SELECT 4 ID, 2 NUM, 3 FLAG FROM DUAL UNION ALL SELECT 6 ID, 1 NUM, 1 FLAG FROM DUAL)SELECT ID, NUM, FLAG FROM (SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY NUM DESC) RN, T.* FROM T) WHERE RN = 1
我认为先要从BB表中根据id分组和找出num最大的记录(这里顺便排了下序是结果和作者要求的一样), 然后连接BB表和上面查出来的结果做连接,取出别名为A的那个表的字段,具体如下: select A.id,A.num,A.flag from BB A join (select id,max(num) as num from BB group by id order by id) B on B.id=A.id and B.num=A.num; 查询结果:
这个其实很简单 select id,max(num),max(flag)keep(dense_rank last order by num) from table1 group by id
(select 1 id, 1 num, 1 flag
from dual
union all
select 1 id, 2 num, 2 flag
from dual
union all
select 1 id, 3 num, 3 flag
from dual
union all
select 2 id, 1 num, 1 flag
from dual
union all
select 3 id, 1 num, 1 flag
from dual
union all
select 3 id, 2 num, 2 flag
from dual
union all
select 4 id, 1 num, 1 flag
from dual
union all
select 4 id, 2 num, 3 flag
from dual
union all
select 6 id, 1 num, 1 flag from dual)
select t1.*, t2.flag
from (select id, max(num) num from t group by id) t1, t t2
where t1.id = t2.id
and t1.num = t2.num
order by t1.id;
WITH T AS
(SELECT 1 ID, 1 NUM, 1 FLAG FROM DUAL UNION ALL
SELECT 1 ID, 2 NUM, 2 FLAG FROM DUAL UNION ALL
SELECT 1 ID, 3 NUM, 3 FLAG FROM DUAL UNION ALL
SELECT 2 ID, 1 NUM, 1 FLAG FROM DUAL UNION ALL
SELECT 3 ID, 1 NUM, 1 FLAG FROM DUAL UNION ALL
SELECT 3 ID, 2 NUM, 2 FLAG FROM DUAL UNION ALL
SELECT 4 ID, 1 NUM, 1 FLAG FROM DUAL UNION ALL
SELECT 4 ID, 2 NUM, 3 FLAG FROM DUAL UNION ALL
SELECT 6 ID, 1 NUM, 1 FLAG FROM DUAL)SELECT ID, NUM, FLAG
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY NUM DESC) RN, T.*
FROM T)
WHERE RN = 1
然后连接BB表和上面查出来的结果做连接,取出别名为A的那个表的字段,具体如下:
select A.id,A.num,A.flag from BB A join
(select id,max(num) as num from BB group by id order by id) B
on B.id=A.id and B.num=A.num;
查询结果:
select id,max(num),max(flag)keep(dense_rank last order by num) from table1
group by id