小弟sql水平欠佳,遇到一个问题如下:
table A :
title type
BB 甲
CC 甲
DD 乙
BB 乙
-------------------------------
查询之后要的结果是:
title type
BB 甲
CC 甲
DD 乙
--------------------------------title有相同值而type不管相不相同时只随机选取一条记录
table A :
title type
BB 甲
CC 甲
DD 乙
BB 乙
-------------------------------
查询之后要的结果是:
title type
BB 甲
CC 甲
DD 乙
--------------------------------title有相同值而type不管相不相同时只随机选取一条记录
用row_number()看看
select title, max(type)
from table
group by title
SQL> select title, type from a where rowid = (select max(rowid) from a b where b.title = a.title);TITLE TYPE
-------------------------------- ----
CC 甲
DD 乙
BB 乙
select title ,type, row_number() over(partition by title) r from table A
) where r = 1;
SQL> select * from (
2 select title ,type, row_number() over(partition by title order by title) r from A
3 ) where r = 1;TITLE TYPE R
-------------------------------- ---- ----------
BB 甲 1
CC 甲 1
DD 乙 1
select * from (
select title ,type, row_number() over(partition by title order by dbms_random.random) r from table A
) where r=1;
自己试下用dbms_random.random行不行,代码可能需要改
create table a(
title varchar2(20),
type varchar2(20));insert into a(title,type) values('BB', '甲');
insert into a(title,type) values('CC', '甲');
insert into a(title,type) values('DD', '乙');
insert into a(title,type) values('BB', '乙');commit;-- 在row_number() over(partition by ... order by sys_guid()),
-- 这样:利用sys_guid()随机函数即可满足楼主需求
select a.title, a.type
from (
select row_number() over(partition by a1.title order by sys_guid()) rcn, a1.title, a1.type
from a a1 ) a
where a.rcn=1;
select e.title,e.type,row_number() over(partition by e.title order by e.rs) rss from (
select a.title,a.type,dbms_random.value rs from a ) e ) k
where k.rss=1
;
from (
select row_number() over(partition by a1.title order by sys_guid()) rcn, a1.title, a1.type
from a a1 ) a
where a.RCN =ROUND(DBMS_RANDOM.value(1,2));应该这样才是真正的随机。
with t as
(select 'BB' title, '甲' type
from dual
union
select 'CC' title, '甲' type
from dual
union
select 'CC' title, '乙' type
from dual
union
select 'CC' title, '丙' type
from dual
union
select 'DD' title, '乙' type
from dual
union
select 'BB' title, '乙' type from dual)select t1.title, t1.type
from (select title,
type,
row_number() over(partition by title order by title) rn
from t) t1,
(select title, count(type) c from t group by title) t2
where t1.title = t2.title
and t1.rn = round(dbms_random.value(1, t2.c))
第一次执行结果:
1 BB 甲
2 CC 乙
3 DD 乙第二次执行结果:
1 BB 甲
2 CC 丙
3 DD 乙第三次执行结果:
1 BB 乙
2 CC 甲
3 DD 乙
(select 'BB' title, '甲' type
from dual
union
select 'CC' title, '甲' type
from dual
union
select 'CC' title, '乙' type
from dual
union
select 'CC' title, '丙' type
from dual
union
select 'DD' title, '乙' type
from dual
union
select 'BB' title, '乙' type from dual
)select *
from (select title,
type,
row_number() over(partition by title order by title desc) rn
from t) t1
where rn = 1