select UID, T_0_FLD_0, T_0_FLD_1, T_0_FLD_2, T_1_FLD_0, T_1_FLD_1, T_1_FLD_2 --...
from
(
select ROW_NUMBER() over(order by UID) as pos, UID, T_0_FLD_0, T_0_FLD_1, T_0_FLD_2, T_1_FLD_0, T_1_FLD_1, T_1_FLD_2 --...
from
(
Select DISTINCT UID, T_0_FLD_0, T_0_FLD_1, T_0_FLD_2, T_1_FLD_0, T_1_FLD_1, T_1_FLD_2 --...
from VIEW_5
where T_0_FLD_0='1' or T_1_FLD_1='2' or T_3_FLD_1='3' or T_4_FLD_1='4' or T_5_FLD_1='5'
) t
) tt
where pos>100 and pos<200create view [dbo].[VIEW_5] as
select T_0.UID, T_0.T_0_FLD_0, T_0.T_0_FLD_1, T_0.T_0_FLD_2,--...
T_1.T_1_FLD_0, T_1.T_1_FLD_1, T_1.T_1_FLD_2,--...
T_2.T_2_FLD_0, T_2.T_2_FLD_1, T_2.T_2_FLD_2,--...
T_3.T_3_FLD_0, T_3.T_3_FLD_1, T_3.T_3_FLD_2,--...
T_4.T_4_FLD_0, T_4.T_4_FLD_1, T_4.T_4_FLD_2,--...
T_5.T_5_FLD_0, T_5.T_5_FLD_1, T_5.T_5_FLD_2--...
from T_0
left join T_1 on T_0.UID=T_1.UID
left join T_2 on T_0.UID=T_2.UID
left join T_3 on T_0.UID=T_3.UID
left join T_4 on T_0.UID=T_4.UID
left join T_5 on T_0.UID=T_5.UID 所有表的UID字段上都建立了非聚集索引。
由于查询的结果字段和条件不固定,所以预先创建了VIEW_5这个视图。
INTO #T
FROM 表
create proc getInfo
as
if object_id('#T') is not null drop table #T
go
select * into #T from
(
create view [dbo].[VIEW_5] as
select T_0.UID, T_0.T_0_FLD_0, T_0.T_0_FLD_1, T_0.T_0_FLD_2,--...
T_1.T_1_FLD_0, T_1.T_1_FLD_1, T_1.T_1_FLD_2,--...
T_2.T_2_FLD_0, T_2.T_2_FLD_1, T_2.T_2_FLD_2,--...
T_3.T_3_FLD_0, T_3.T_3_FLD_1, T_3.T_3_FLD_2,--...
T_4.T_4_FLD_0, T_4.T_4_FLD_1, T_4.T_4_FLD_2,--...
T_5.T_5_FLD_0, T_5.T_5_FLD_1, T_5.T_5_FLD_2--...
from T_0
left join T_1 on T_0.UID=T_1.UID
left join T_2 on T_0.UID=T_2.UID
left join T_3 on T_0.UID=T_3.UID
left join T_4 on T_0.UID=T_4.UID
left join T_5 on T_0.UID=T_5.UID
) TT
goselect UID, T_0_FLD_0, T_0_FLD_1, T_0_FLD_2, T_1_FLD_0, T_1_FLD_1, T_1_FLD_2 --...
from
(
select ROW_NUMBER() over(order by UID) as pos, UID, T_0_FLD_0, T_0_FLD_1, T_0_FLD_2, T_1_FLD_0, T_1_FLD_1, T_1_FLD_2 --...
from
(
Select DISTINCT UID, T_0_FLD_0, T_0_FLD_1, T_0_FLD_2, T_1_FLD_0, T_1_FLD_1, T_1_FLD_2 --...
from #T
where T_0_FLD_0='1' or T_1_FLD_1='2' or T_3_FLD_1='3' or T_4_FLD_1='4' or T_5_FLD_1='5'
) t
) tt
where pos>100 and pos<200
有必要优化吗?
不过如果你view5很庞大的话,,建议别做view
view的作用可不是拿来提高程序效率的
Select DISTINCT UID, T_0_FLD_0, T_0_FLD_1, T_0_FLD_2, T_1_FLD_0, T_1_FLD_1, T_1_FLD_2 --...
from VIEW_5
where T_0_FLD_0='1' or T_1_FLD_1='2' or T_3_FLD_1='3' or T_4_FLD_1='4' or T_5_FLD_1='5'
修改为:
Select UID, T_0_FLD_0, T_0_FLD_1, T_0_FLD_2, T_1_FLD_0, T_1_FLD_1, T_1_FLD_2 --...
from VIEW_5
where T_0_FLD_0='1'
UNION
Select UID, T_0_FLD_0, T_0_FLD_1, T_0_FLD_2, T_1_FLD_0, T_1_FLD_1, T_1_FLD_2 --...
from VIEW_5
where T_1_FLD_1='2'
UNION
Select UID, T_0_FLD_0, T_0_FLD_1, T_0_FLD_2, T_1_FLD_0, T_1_FLD_1, T_1_FLD_2 --...
from VIEW_5
where T_3_FLD_1='3'
UNION
Select UID, T_0_FLD_0, T_0_FLD_1, T_0_FLD_2, T_1_FLD_0, T_1_FLD_1, T_1_FLD_2 --...
from VIEW_5
where T_4_FLD_1='4'
UNION
Select UID, T_0_FLD_0, T_0_FLD_1, T_0_FLD_2, T_1_FLD_0, T_1_FLD_1, T_1_FLD_2 --...
from VIEW_5
where T_5_FLD_1='5'
试试看看。
你實在要用view,又想提高效率,,
那你就做多個view吧
每個view使用不同的
T_0_FLD_0='1' or T_1_FLD_1='2' or T_3_FLD_1='3' or T_4_FLD_1='4' or T_5_FLD_1='5'
這樣縂能保證時間和效率了吧
還不行,,那就寫存儲過程吧,拼接動態SQL總不會出錯