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

Question

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不能重复的）

Answer 1

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

Answer 2

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

Answer 3

如果有数据，测试一下会更好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

Answer 4

上面的错了。粘上错误，继续等待答案！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 出错

Answer 5

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

Answer 6

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

Answer 7

--试试这个
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

Answer 8

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;