举列子表:id,com_id,product_id
1,1001,2001
2,1001,2002
3,1001,2003
4,1002,2004,
5,1002,2005
6,1003,2008
需要把各公司都取出来,并且只取一条产品信息的,结果比如:
1,1001,2001
5,1002,2005
6,1003,2008
要求是sql语句搞定,不要存储过程,游标啥的,
谢谢各位大侠,重分答谢,急~~~~
1,1001,2001
2,1001,2002
3,1001,2003
4,1002,2004,
5,1002,2005
6,1003,2008
需要把各公司都取出来,并且只取一条产品信息的,结果比如:
1,1001,2001
5,1002,2005
6,1003,2008
要求是sql语句搞定,不要存储过程,游标啥的,
谢谢各位大侠,重分答谢,急~~~~
这是随机返回每组的一条记录
WITH t AS (
SELECT 1 id,1001 com_id,2001 product_id FROM dual
UNION ALL
SELECT 2,1001,2002 FROM dual
UNION ALL
SELECT 3,1001,2003 FROM dual
UNION ALL
SELECT 4,1002,2004 FROM dual
UNION ALL
SELECT 5,1002,2005 FROM dual
UNION ALL
SELECT 6,1003,2008 FROM dual
)
SELECT id,com_id,product_id FROM
(
SELECT t.*,Row_Number() over (PARTITION BY com_id ORDER BY dbms_random.random)rn FROM t
)
WHERE rn=1
ORDER BY com_id;
(select 1001 id,2001 year from dual
union all
select 1001 ,2002 from dual
union all
select 1001 ,2003 from dual
union all
select 1002 ,2004 from dual
union all
select 1002 ,2005 from dual
union all
select 1003 ,2008 from dual)
select id,case when mod(id, 2) = 0 then max(year) else min(year) end
from t
group by id
order by id;结果如下: ID 结果
---------- ----------
1001 2001
1002 2005
1003 2008