表a,结构和数据:
id, pid, col1, col2, col3, col4, col5... 后面还有若干字段
1 2 a b c d e
1 2 f j n r v
1 3 g k o s w
1 4 h l p t x
1 4 i m q u y
2 3 z 2 4 6 8
2 3 1 3 5 7 9要求按id和pid进行group by
其它字段,任意返回一条即可返回结果类似:
id, pid, col1, col2, col3, col4, col5... 后面还有若干字段
1 2 a b c d e
1 3 g k o s w
1 4 h l p t x
2 3 z 2 4 6 8
id, pid, col1, col2, col3, col4, col5... 后面还有若干字段
1 2 a b c d e
1 2 f j n r v
1 3 g k o s w
1 4 h l p t x
1 4 i m q u y
2 3 z 2 4 6 8
2 3 1 3 5 7 9要求按id和pid进行group by
其它字段,任意返回一条即可返回结果类似:
id, pid, col1, col2, col3, col4, col5... 后面还有若干字段
1 2 a b c d e
1 3 g k o s w
1 4 h l p t x
2 3 z 2 4 6 8
SELECT ROW_NUMBER() OVER (ORDER BY t4.itemcode) rcn, t4.itemcode, t4.cdate, t4.amount
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY t1.itemcode ORDER BY t1.itemcode) rcn, t1.itemcode, t1.cdate, t1.amount
FROM item_tb t1
WHERE EXISTS ( SELECT 1 FROM (
SELECT itemcode FROM
(SELECT distinct itemcode FROM item_tb ORDER BY sys_guid()) t2
WHERE rownum<=1000 ) t3
WHERE t3.itemcode=t1.itemcode )
) t4
WHERE t4.rcn <= 3;
select * from a where rowid in (
select max(rowid) from a group by id, pid)
SELECT ROW_NUMBER() OVER (ORDER BY t4.itemcode) rcn, t4.itemcode, t4.cdate, t4.amount
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY t1.itemcode ORDER BY t1.itemcode) rcn, t1.itemcode, t1.cdate, t1.amount
FROM item_tb t1
WHERE EXISTS ( SELECT 1 FROM (
SELECT itemcode FROM
(SELECT distinct itemcode FROM item_tb ORDER BY sys_guid()) t2
WHERE rownum<=1000 ) t3
WHERE t3.itemcode=t1.itemcode )
) t4
WHERE t4.rcn <= 3;-- 那我就蹭点分!
--分析函数
select id, pid, col1, col2, col3, col4,col5,...
from
(select id, pid, col1, col2, col3, col4,col5,...,
row_number() over(partition by id,pid order by col1) rn
from a) t
where rn=1
from a t
where not exists(
select 1 from a where id=t.id
and pid=t.pid and rowid<t.rowid)
(select max(rowid) from a t2 where t2.id=t1.id and t2.pid=t1.pid)