我这有个存储过程,能不能把它优化一下??
select t.*,case when (EXISTS(SELECT * FROM planadmin where
planadmin.file_ not in ('xx') and charindex('、'+t.F27+'、','、'+planadmin.retwo+'、')>0 and t.F7 is not null)) then 1 else 0 end as keyid,
case when (EXISTS(SELECT * FROM planadmin where
planadmin.file_ not in ('xx')
and planadmin.watersupply_population > t.F7 and t.F7 is not null))
then 1 else 0 end as keyid1 FROM t where t.F7 is not null order by t.city_name
select t.*,case when (EXISTS(SELECT * FROM planadmin where
planadmin.file_ not in ('xx') and charindex('、'+t.F27+'、','、'+planadmin.retwo+'、')>0 and t.F7 is not null)) then 1 else 0 end as keyid,
case when (EXISTS(SELECT * FROM planadmin where
planadmin.file_ not in ('xx')
and planadmin.watersupply_population > t.F7 and t.F7 is not null))
then 1 else 0 end as keyid1 FROM t where t.F7 is not null order by t.city_name
select t.*,case when (EXISTS(SELECT file_ FROM planadmin where
planadmin.file_ <> ('xx')
and charindex('、'+t.F27+'、','、'+planadmin.retwo+'、')>0 and t.F7 is not null)) then 1 else 0 end as keyid,
case when (EXISTS(SELECT file_ FROM planadmin where
planadmin.file_ <> ('xx') and planadmin.watersupply_population > t.F7 and t.F7 is not null))
then 1 else 0 end as keyid1
FROM t
where t.F7 is not null order by t.city_name 尽量不要用否定运算,尽量不要select *,其他的要看看你的业务要求了,
(
EXISTS
(
SELECT * FROM planadmin where
(
and t.F7 is not null
and t.city_name=planadmin.city_name
and t.country_name= planadmin.county_name
planadmin.file_ not in ('xx')
and planadmin.construct_spot like '%'+substring(t.F6,0,8)+'%'
)
or
(
and t.F7 is not null
and planadmin.watersupply_population > t.F7
and t.city_name=planadmin.city_name
and t.country_name= planadmin.county_name
planadmin.file_ not in ('xx')
and planadmin.construct_spot like '%'+substring(t.F6,0,8)+'%'
)
)
)
then 1 else 0 end as keyid
FROM t
where t.F7 is not null
order by t.city_name