几要实用sql语句!!!-优快云博客

本文链接：https://blog.youkuaiyun.com/yxtyxt3311/article/details/1517155

本文详细介绍了使用SQL进行数据查询的多种高级技巧，包括利用MINUS操作符、ROWNUM及ROW_NUMBER()函数来精确控制查询结果的数量，以及通过联表查询和注释优化查询效率的方法。这些技巧对于提高数据库查询性能至关重要。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

select caseno,CASESORT,CASEADDRSS,APPELLATEUNIT,WRITEDATE from case_penal
where 1=1 and caseno like '%1%' and rownum<=60000

minus select caseno,CASESORT,CASEADDRSS,APPELLATEUNIT,WRITEDATE from case_penal
where 1=1 and caseno like '%1%' and rownum<=59980

--------------------------------------------------------------------------------------------------------------------------------------

select * from (select rownum rn,a.caseno,a.CASESORT,a.CASEADDRSS,a.APPELLATEUNIT,a.WRITEDATE from case_penal a
where 1=1 and a.caseno like '%1%' and rownum<=60000 ) where rn>59980 (good sql)

--------------------------------------------------------------------------------------------------------------------------------------

select * from (select caseno from (select row_number() over (order by recordtime desc) rn,
caseno from case_penal where caseno like '%1%' and recordman='101121') t where rn>0 and rn<=30) b,
(select a.caseno,a.CASESORT,a.CASEADDRSS,a.APPELLATEUNIT,a.WRITEDATE,recordtime from case_penal a) t

where t.caseno=b.caseno

--------------------------------------------------------------------------------------------------------------------------------------

select /*+first_rows*/ a.*,b.casesort as casesortname,c.name as sldw,d.state as zstate from case_penal a,
casesort b,unionunit c,CASESTATE d where a.caseno=d.caseno(+) and a.APPELLATEUNIT=c.code(+)
and a.casesort=b.sortno(+) and recordman='101121' and a.caseno like '%1%' order by recordtime desc(good sql)

--------------------------------------------------------------------------------------------------------------------------------------

select caseno,CASESORT,CASEADDRSS,APPELLATEUNIT,WRITEDATE from

(select /*+first_rows*/ rownum rn,a.caseno,a.CASESORT,a.CASEADDRSS,a.APPELLATEUNIT,a.WRITEDATE

from case_penal a

where 1=1 and a.caseno like '%1%' and rownum<=60000 ) where rn>59980(good sql)

--------------------------------------------------------------------------------------------------------------------------------------

alter table CASE_PENAL nologging

insert /*+append*/ into CASE_PENAL values ('3132','004','0','1',f')