Oracle语句

mayudong1 2016-12-19 11:55:13

--实现按照ZYID和 ETYPEID分组，每个不同的ZYID下的不同的ETYPEID，返回最新的20条数据
SELECT * FROM (SELECT TY.ZYID, INFO.ETYPEID,INFO.ENUM,INFO.ENUMTWO,INFO.STIME FROM TA_ANARCORD_OTHER INFO,TY_PPERAINFO TY,TA_ANARECORD TA WHERE INFO.ANARECORDID=TA.ID AND TA.PPERA_ID=TY.ID AND TY.ZYID IN(1,2,3,4) AND INFO.ETYPEID in(6,7,8,13,11) ORDER BY INFO.ID DESC) WHERE ROWNUM<=20
需要改成如上所述

...全文

231 3 打赏收藏转发到动态举报

写回复

用AI写文章

3 条回复

切换为时间正序

请发表友善的回复…

发表回复

H_Gragon 2016-12-19

打赏
举报

引用 1 楼 wmxcn2000 的回复:


--实现按照ZYID和 ETYPEID分组
SELECT *
  FROM (SELECT row_number() over(partition by ZYID, ETYPEID order by INFO.ID DESC) rn,
               INFO.ENUM,
               TY.ZYID,
               INFO.ETYPEID,
               INFO.ENUM,
               INFO.ENUMTWO,
               INFO.STIME,
               INFO.ENUMTWO,
               INFO.STIME
          FROM TA_ANARCORD_OTHER INFO, TY_PPERAINFO TY, TA_ANARECORD TA
         WHERE INFO.ANARECORDID = TA.ID
           AND TA.PPERA_ID = TY.ID
           AND TY.ZYID IN (1, 2, 3, 4)
           AND INFO.ETYPEID in (6, 7, 8, 13, 11))
 WHERE rn <= 20

sych888 2016-12-19

打赏
举报

排名函数主要是如何区分ETYPEID的新旧？

卖水果的net 2016-12-19

打赏
举报


--实现按照ZYID和 ETYPEID分组
SELECT *
  FROM (SELECT row_number() over(partition by ZYID, ETYPEID order by INFO.ID DESC) rn,
               INFO.ENUM,
               TY.ZYID,
               INFO.ETYPEID,
               INFO.ENUM,
               INFO.ENUMTWO,
               INFO.STIME,
               INFO.ENUMTWO,
               INFO.STIME
          FROM TA_ANARCORD_OTHER INFO, TY_PPERAINFO TY, TA_ANARECORD TA
         WHERE INFO.ANARECORDID = TA.ID
           AND TA.PPERA_ID = TY.ID
           AND TY.ZYID IN (1, 2, 3, 4)
           AND INFO.ETYPEID in (6, 7, 8, 13, 11))
 WHERE rn <= 20