公司的:
select SysCode into #Temp
from orderbill O
inner join SplitStringToTable('O001,O002,O003',',') a on O.OrgCode =a.FieldName
inner join SplitStringToTable('001,002,003',',') b on O.WhCode =b.FieldName
where O.ApprovalStatus = 2
and O.StockStatus = 0
and O.Status = 1select O.AutoID into #e
from
(
select *
from OrderBills where SysCode in (select * from #Temp) ) O
Left join ( select * from InOutBills where SysCode in(select * from #Temp)
) I
on O.SysCode = I.SysCode select count(*) from #e
select * from #edrop table #Temp
drop table #e我的
select SysCode into #Temp
from orderbill O
where O.ApprovalStatus = 2
and O.StockStatus = 0
and O.Status = 1
and O.OrgCode in (select * from SplitStringToTable('O001,O002,O003',',') S)
and O.WhCode in (select * from SplitStringToTable('001,002,003',',') S)select O.AutoID into #e
from
(
select *
from OrderBills where SysCode in (select * from #Temp) ) O
Left join ( select * from InOutBills where SysCode in(select * from #Temp)
) I
on O.SysCode = I.SysCode select count(*) from #e
select * from #edrop table #Temp
drop table #e说明:
大部分的语句不重要,关键是在数据的选择上,使用内联选择数据快,还是使用where + in快?001,002,003是外部的,不同长度的。二问:公司在分页时使用一张含有自增字段的表(临时)和需分页的表连接,再根据自增字段取数以分页,这个分页方法比两次top方式分页那个快?
select SysCode into #Temp
from orderbill O
inner join SplitStringToTable('O001,O002,O003',',') a on O.OrgCode =a.FieldName
inner join SplitStringToTable('001,002,003',',') b on O.WhCode =b.FieldName
where O.ApprovalStatus = 2
and O.StockStatus = 0
and O.Status = 1select O.AutoID into #e
from
(
select *
from OrderBills where SysCode in (select * from #Temp) ) O
Left join ( select * from InOutBills where SysCode in(select * from #Temp)
) I
on O.SysCode = I.SysCode select count(*) from #e
select * from #edrop table #Temp
drop table #e我的
select SysCode into #Temp
from orderbill O
where O.ApprovalStatus = 2
and O.StockStatus = 0
and O.Status = 1
and O.OrgCode in (select * from SplitStringToTable('O001,O002,O003',',') S)
and O.WhCode in (select * from SplitStringToTable('001,002,003',',') S)select O.AutoID into #e
from
(
select *
from OrderBills where SysCode in (select * from #Temp) ) O
Left join ( select * from InOutBills where SysCode in(select * from #Temp)
) I
on O.SysCode = I.SysCode select count(*) from #e
select * from #edrop table #Temp
drop table #e说明:
大部分的语句不重要,关键是在数据的选择上,使用内联选择数据快,还是使用where + in快?001,002,003是外部的,不同长度的。二问:公司在分页时使用一张含有自增字段的表(临时)和需分页的表连接,再根据自增字段取数以分页,这个分页方法比两次top方式分页那个快?
语句很重要,还有,
如果只是查一个表的数据,EXISTS比连接效率会好点,如果两表数据,肯定得JOIN
不信可以试试,服务器地址192.168.115.28用户名sa密码ty9856,