oracle取不重复的记录问题，请高手帮忙解答

[yangtingKL]

oracle语句：

SELECT LS.AREA_NAME, PIC.PATH,PIC.FILENAME,LS.CITY,LS.AREA_ID,PIC.PIC_ID FROM LS_AREA_PIC PIC,LS_AREA LS WHERE PIC.RELATE_ID=LS.AREA_ID AND
PIC.TABLE_NAME='LS_AREA' AND CITY = '广州市'



需要在这个语句上再加两个条件：

1. 对ls.area_id重复的记录只取一条

2. 对所有结果集只提取四条记录即可（ls.area_id不能重复的）

[真的不知道取什么名]

select AREA_NAME, 
       PATH,
       FILENAME,
       CITY,
       AREA_ID,
       PIC_ID
from
(SELECT LS.AREA_NAME, 
       PIC.PATH,
       PIC.FILENAME,
       LS.CITY,
       LS.AREA_ID,
       PIC.PIC_ID,
       row_number() over(partition by ls.area_id) rn
FROM LS_AREA_PIC PIC,LS_AREA LS 
WHERE PIC.RELATE_ID=LS.AREA_ID
AND   PIC.TABLE_NAME='LS_AREA' 
AND   CITY = '广州市')
where rn=1
and   rownum<5

[秋雨飘落]

row_number() over(partition by ls.area_id) rn

[304的的哥]

[Quote=引用 1 楼 zhuomingwang 的回复:]
select AREA_NAME,
PATH,
FILENAME,
CITY,
AREA_ID,
PIC_ID
from ……
[/Quote]
[Quote=引用楼主 yangtingkl 的回复:]
oracle语句：

SELECT LS.AREA_NAME, PIC.PATH,PIC.FILENAME,LS.CITY,LS.AREA_ID,PIC.PIC_ID FROM LS_AREA_PIC PIC,LS_AREA LS WHERE PIC.RELATE_ID=LS.AREA_ID AND
PIC.TABLE_NAME='LS_AREA' AND CITY = '广州市'

……
[/Quote]
如果有数据，测试一下会更好

select AREA_NAME, 
       PATH,
       FILENAME,
       CITY,
       AREA_ID,
       PIC_ID
from
(SELECT LS.AREA_NAME, 
       PIC.PATH,
       PIC.FILENAME,
       LS.CITY,
       LS.AREA_ID,
       PIC.PIC_ID,
       row_number() over(partition by ls.area_id) rn
FROM LS_AREA_PIC PIC,LS_AREA LS 
WHERE PIC.RELATE_ID=LS.AREA_ID
AND   PIC.TABLE_NAME='LS_AREA' 
AND   CITY = '广州市')
where rn=1
and   rownum<5

[yangtingKL]

上面的错了。

粘上错误，继续等待答案！

ORA-30485: 在窗口说明中丢失 ORDER BY 表达式
30485. 00000 - "missing ORDER BY expression in the window specification"
*Cause: Either the ORDER BY expression is mandatory for this function, or
there is an aggregation group without any ORDER by expression.
*Action:
行 35 列 7 出错

[ICE-word]

你后面在加 order by AREA_NAME,
PATH,
FILENAME,
CITY,
AREA_ID,
PIC_ID
就可以了

[yangtingKL]

还是报相同的错误。。我单执行子查询也是这个错误

[Phoenix_99]

select AREA_NAME, 
       PATH,
       FILENAME,
       CITY,
       AREA_ID,
       PIC_ID
from
(SELECT LS.AREA_NAME, 
       PIC.PATH,
       PIC.FILENAME,
       LS.CITY,
       LS.AREA_ID,
       PIC.PIC_ID,
       row_number() over(partition by ls.area_id order by LS.AREA_NAME) rn
FROM LS_AREA_PIC PIC,LS_AREA LS 
WHERE PIC.RELATE_ID=LS.AREA_ID
AND   PIC.TABLE_NAME='LS_AREA' 
AND   CITY = '¹ãÖÝÊÐ')
where rn=1
and   rownum<5

[真的不知道取什么名]

--试试这个
select AREA_NAME, 
       PATH,
       FILENAME,
       CITY,
       AREA_ID,
       PIC_ID
from
(SELECT LS.AREA_NAME, 
       PIC.PATH,
       PIC.FILENAME,
       LS.CITY,
       LS.AREA_ID,
       PIC.PIC_ID,
       row_number() over(partition by ls.area_id order by LS.AREA_NAME ) rn
FROM LS_AREA_PIC PIC,LS_AREA LS 
WHERE PIC.RELATE_ID=LS.AREA_ID
AND   PIC.TABLE_NAME='LS_AREA' 
AND   CITY = '广州市')
where rn=1
and   rownum<5

[winer1220]

with t1 as(
select 1 area_id,'aa' tname from dual union all
select 1,'bb' from dual union all
select 1,'tt' from dual union all
select 2,'cc' from dual union all
select 3,'dd' from dual union all
select 4,'ee' from dual union all
select 5,'ff' from dual)
select * from (
select area_id,tname,dense_rank() over(partition by area_id order by area_id) rn from t1
) t where t.rn = 1 and rownum < 5;

[winer1220]

稍微该一下就ok了

[yangtingKL]

ok了耶，非常感谢楼上

[yangtingKL]

七 、 八楼的执行ok ..感谢大家。

[tyjohn]

万分感谢，学习了