如何把:
select top 12 * from WJ_EnterpriseSummary where Guid not in(select top 24 Guid from WJ_EnterpriseSummary where 1=1 order by companyGuid asc,sumYear desc,sumMonth desc) order by companyGuid asc,sumYear desc,sumMonth desc
not in 转换为not exists
select top 12 * from WJ_EnterpriseSummary where Guid not in(select top 24 Guid from WJ_EnterpriseSummary where 1=1 order by companyGuid asc,sumYear desc,sumMonth desc) order by companyGuid asc,sumYear desc,sumMonth desc
not in 转换为not exists
into #
from WJ_EnterpriseSummary
order by companyGuid asc, sumYear desc, sumMonth descselect * from # where id >24
not exists
(
select 1 from EnterpriseSummary child where child.Guid in
(
select top 24 Guid from EnterpriseSummary order by companyGuid asc,sumYear desc,sumMonth desc) and parent.Guid =child.Guid
)
order by companyGuid asc,sumYear desc,sumMonth desc
select top 36 identity(int, 1,1) as Id, *
into #
from WJ_EnterpriseSummary
order by companyGuid asc, sumYear desc, sumMonth descselect * from # where id >24
=============================================================对我不适合select top 12 * from WJ_EnterpriseSummary A where not exists(select top 24 B.guid from WJ_EnterpriseSummary B where A.guid=B.guid order by B.companyGuid asc,B.sumYear desc,B.sumMonth desc) order by A.companyGuid asc,A.sumYear desc,A.sumMonth desc-----------------------------------------------------------------------
没有效果
这种SQL不需要采用not exists,因为子查询只有24条记录。而且SQL Server中也需要子查询:select top 12 parent.* from WJ_EnterpriseSummary parent where
not exists
(select 1 from (select top 24 Guid from WJ_EnterpriseSummary order by companyGuid asc,sumYear desc,sumMonth desc) child where parent.Guid =child.Guid )
order by parent.companyGuid asc,parent.sumYear desc,parent.sumMonth desc请测试两种方法的效果并回帖,谢谢!
into #
from WJ_EnterpriseSummary
order by companyGuid asc, sumYear desc, sumMonth descselect * from # where id >24------------------------------------------------------------
声称临时表不可取,如果多用户同时操作,相互覆盖怎么办?
我要在分页存储过程中用所以一定要用not exists