select ID,MAILTYPE,MAILTITLE,to_char(WRITETIME,'yyyy-mm-dd') WRITETIME from mails where ID in (select ID from mails where ISPUB=1 and ISVAIDATE=1 and ISSECRET=0 and ISCHOOSEPUB=1 order by ID desc) and rownum<11 哪里缺右括号了? 不明白
改一下: select ID,MAILTYPE,MAILTITLE,to_char(WRITETIME,'yyyy-mm-dd') WRITETIME from mails where ID in (select ID from mails where ISPUB=1 and ISVAIDATE=1 and ISSECRET=0 and ISCHOOSEPUB=1) and rownum <11 order by ID desc;
in (子查询)的用法中,子查询不能使用order by!否则报错。修改下: SELECT ID, MAILTYPE, MAILTITLE, TO_CHAR(WRITETIME, 'yyyy-mm-dd') WRITETIME FROM MAILS WHERE ID IN (SELECT ID FROM MAILS WHERE ISPUB = 1 AND ISVAIDATE = 1 AND ISSECRET = 0 AND ISCHOOSEPUB = 1) AND ROWNUM < 11;
select ID,MAILTYPE,MAILTITLE,to_char(WRITETIME,'yyyy-mm-dd') WRITETIME from mails where ID in (select ID from mails where ISPUB=1 and ISVAIDATE=1 and ISSECRET=0 and ISCHOOSEPUB=1) and rownum <11 order by ID desc;
SELECT ID, MAILTYPE, MAILTITLE, TO_CHAR(WRITETIME, 'yyyy-mm-dd') WRITETIME
FROM MAILS
WHERE ID IN (SELECT ID
FROM MAILS
WHERE ISPUB = 1
AND ISVAIDATE = 1
AND ISSECRET = 0
AND ISCHOOSEPUB = 1)
AND ROWNUM < 11;
要排序,外面排。