--id 列上有索引的情况下select * from table1 where id = 1 union all select * from table1 where id = 2 union all select * from table1 where id = 3 union all select * from table1 where id = 4 union all select * from table1 where id = 5 union all select * from table1 where id = 6 union all select * from table1 where id = 7 union all select * from table1 where id = 8 union all select * from table1 where id = 9
select * from #temp where exists( select NULL from( select 1 as ID UNION select 2 as ID UNION select 3 as ID UNION select 4 as ID UNION select 5 as ID UNION select 6 as ID UNION select 7 as ID UNION select 8 as ID UNION select 9) a where a.ID=#temp.ID )
select * from #temp where exists( select NULL from( select 1 as ID UNION select 2 as ID UNION select 3 as ID UNION select 4 as ID UNION select 5 as ID UNION select 6 as ID UNION select 7 as ID UNION select 8 as ID UNION select 9) a where a.ID=#temp.ID )
select * from table1 where id <10
楼主让我想起了 大于 小于 between ... and 以及 join 的各种神马连接。
select * from table1 where id = 1 or id = 2 or id =3 这样效率会高很多
union all
select * from table1 where id = 2
union all
select * from table1 where id = 3
union all
select * from table1 where id = 4
union all
select * from table1 where id = 5
union all
select * from table1 where id = 6
union all
select * from table1 where id = 7
union all
select * from table1 where id = 8
union all
select * from table1 where id = 9
或者创建带索引的表,存放in里面的数据。
然后与table1 inner Join 也可以。 inner join 相关字段加好索引
此时即使id上有索引也是没用的,基本上无法优化.
select * from #temp where
exists(
select NULL from(
select 1 as ID UNION
select 2 as ID UNION
select 3 as ID UNION
select 4 as ID UNION
select 5 as ID UNION
select 6 as ID UNION
select 7 as ID UNION
select 8 as ID UNION
select 9) a where a.ID=#temp.ID
)
select * from #temp where
exists(
select NULL from(
select 1 as ID UNION
select 2 as ID UNION
select 3 as ID UNION
select 4 as ID UNION
select 5 as ID UNION
select 6 as ID UNION
select 7 as ID UNION
select 8 as ID UNION
select 9) a where a.ID=#temp.ID
)
这样效率会高很多