设定有一张表Tab,Tab中有两个字段:A,B。A和B组合起来可以唯一确定一条记录(相当与主键)。表Tab中的数据如下:A B
a 1
a 2
a 3
b 1
b 2
c 1现在要求是将表Tab中字段A值相同的记录数多于1的记录中随机的选择一条记录出来。比如查询的结果应该是:
A B
a 1
b 2当然还有其他的结果集。。求教SQL语句怎么写??
a 1
a 2
a 3
b 1
b 2
c 1现在要求是将表Tab中字段A值相同的记录数多于1的记录中随机的选择一条记录出来。比如查询的结果应该是:
A B
a 1
b 2当然还有其他的结果集。。求教SQL语句怎么写??
from Tab
group by A
SELECT 'a' A, '1' B FROM DUAL
UNION ALL
SELECT 'a', '2' FROM DUAL
UNION ALL
SELECT 'a' ,'3' FROM DUAL
UNION ALL
SELECT 'b' ,'1' FROM DUAL
UNION ALL
SELECT 'b' ,'2' FROM DUAL
UNION ALL
SELECT 'c' ,'1' FROM DUAL
)
SELECT A,B FROM(
SELECT ROW_NUMBER()OVER(PARTITION BY A ORDER BY DBMS_RANDOM.VALUE()) ID,
A,
B
FROM CTE)
WHERE ID=2
with t as (
select 'a' as A, 1 as B from dual
union all
select 'a', 2 from dual
union all
select 'a', 3 from dual
union all
select 'b', 1 from dual
union all
select 'b', 2 from dual
union all
select 'c', 1 from dual
)
select A,max(B) B
from t
group by A
having count(1) >1
A B
- ----------------------
a 3
b 2
-- 顶一个!
-- *1. 随机;
-- *2. A值相同的记录数大于1;