SELECT l_title,l_id,l_SUB,l_ED,l_FILE,l_Bln FROM (select * from lctSUB where L_TIME>'2005-1-1') a WHERE ((l_SUB= 'g1yw') AND (l_ED IN ('A', 'B', 'C'))) OR ((l_SUB= 'g1yy') AND (l_ED IN ('C', 'D', 'A'))) OR ((l_SUB= 'g1sx') AND (l_ED IN ('F', 'E', 'G'))) OR ((l_SUB= 'g1wl') AND (l_ED IN ('A', 'F', 'G'))) OR ((l_SUB= 'g1hx') AND (l_ED IN ('G', 'B', 'C'))) OR ((l_SUB= 'g1ls') AND (l_ED IN ('A', 'F', 'C'))) OR ((l_SUB= 'g1dl') AND (l_ED IN ('G', 'H', 'W'))) OR ((l_SUB= 'g1sw') AND (l_ED IN ('A', 'F', 'C'))) OR ((l_SUB= 'g1zz') AND (l_ED IN ('A', 'G', 'C')))
to cnwmj 应该怎么优化更好一点儿??
SELECT l_title,l_id,l_SUB,l_ED,l_FILE,l_Bln into #tmp FROM lctSUB where L_TIME>'2005-1-1'SELECT * FROM #tmp WHERE l_SUB = 'g1yw' AND l_ED = 'A' union all SELECT * FROM #tmp WHERE l_SUB = 'g1yw' AND l_ED = 'B' union all SELECT * FROM #tmp WHERE l_SUB = 'g1yw' AND l_ED = 'C' union all SELECT * FROM #tmp WHERE l_SUB = 'g1yy' AND l_ED = 'C' union all SELECT * FROM #tmp WHERE l_SUB = 'g1yy' AND l_ED = 'D' union all SELECT * FROM #tmp WHERE l_SUB = 'g1yy' AND l_ED = 'A' union all SELECT * FROM #tmp WHERE l_SUB = 'g1sx' AND l_ED = 'F' union all SELECT * FROM #tmp WHERE l_SUB = 'g1sx' AND l_ED = 'E' union all SELECT * FROM #tmp WHERE l_SUB = 'g1sx' AND l_ED = 'G' union all SELECT * FROM #tmp WHERE l_SUB = 'g1wl' AND l_ED = 'A' union all SELECT * FROM #tmp WHERE l_SUB = 'g1wl' AND l_ED = 'F' union all SELECT * FROM #tmp WHERE l_SUB = 'g1wl' AND l_ED = 'G' union all SELECT * FROM #tmp WHERE l_SUB = 'g1hx' AND l_ED = 'G' union all SELECT * FROM #tmp WHERE l_SUB = 'g1hx' AND l_ED = 'B' union all SELECT * FROM #tmp WHERE l_SUB = 'g1hx' AND l_ED = 'C' union all SELECT * FROM #tmp WHERE l_SUB = 'g1ls' AND l_ED = 'A' union all SELECT * FROM #tmp WHERE l_SUB = 'g1ls' AND l_ED = 'F' union all SELECT * FROM #tmp WHERE l_SUB = 'g1ls' AND l_ED = 'C' union all SELECT * FROM #tmp WHERE l_SUB = 'g1dl' AND l_ED = 'G' union all SELECT * FROM #tmp WHERE l_SUB = 'g1dl' AND l_ED = 'H' union all SELECT * FROM #tmp WHERE l_SUB = 'g1dl' AND l_ED = 'W' union all SELECT * FROM #tmp WHERE l_SUB = 'g1sw' AND l_ED = 'A' union all SELECT * FROM #tmp WHERE l_SUB = 'g1sw' AND l_ED = 'F' union all SELECT * FROM #tmp WHERE l_SUB = 'g1sw' AND l_ED = 'C' union all SELECT * FROM #tmp WHERE l_SUB = 'g1zz' AND l_ED = 'A' union all SELECT * FROM #tmp WHERE l_SUB = 'g1zz' AND l_ED = 'G' union all SELECT * FROM #tmp WHERE l_SUB = 'g1zz' AND l_ED = 'C'
即 select * from lctSUB where l_ED = A and (l_SUB = x1 or l_SUB = x2 or lSUB = x3) union all select * from lctSUB where l_ED = B and (l_SUB = x1 or l_SUB = x2 ...) union all ...
建一个表 #tmp 结构如下 l_SUB l_ED g1yw A g1yw B ... ...然后联合查询 select a.* from 表 a where exists(select 1 from #tmp where l_SUB =a.l_SUB and l_ED=a.l_ED)
FROM (select * from lctSUB where L_TIME>'2005-1-1') a
WHERE ((l_SUB= 'g1yw') AND (l_ED IN ('A', 'B', 'C'))) OR
((l_SUB= 'g1yy') AND (l_ED IN ('C', 'D', 'A'))) OR
((l_SUB= 'g1sx') AND (l_ED IN ('F', 'E', 'G'))) OR
((l_SUB= 'g1wl') AND (l_ED IN ('A', 'F', 'G'))) OR
((l_SUB= 'g1hx') AND (l_ED IN ('G', 'B', 'C'))) OR
((l_SUB= 'g1ls') AND (l_ED IN ('A', 'F', 'C'))) OR
((l_SUB= 'g1dl') AND (l_ED IN ('G', 'H', 'W'))) OR
((l_SUB= 'g1sw') AND (l_ED IN ('A', 'F', 'C'))) OR
((l_SUB= 'g1zz') AND (l_ED IN ('A', 'G', 'C')))
into #tmp
FROM lctSUB
where L_TIME>'2005-1-1'SELECT * FROM #tmp WHERE l_SUB = 'g1yw' AND l_ED = 'A'
union all
SELECT * FROM #tmp WHERE l_SUB = 'g1yw' AND l_ED = 'B'
union all
SELECT * FROM #tmp WHERE l_SUB = 'g1yw' AND l_ED = 'C'
union all
SELECT * FROM #tmp WHERE l_SUB = 'g1yy' AND l_ED = 'C'
union all
SELECT * FROM #tmp WHERE l_SUB = 'g1yy' AND l_ED = 'D'
union all
SELECT * FROM #tmp WHERE l_SUB = 'g1yy' AND l_ED = 'A'
union all
SELECT * FROM #tmp WHERE l_SUB = 'g1sx' AND l_ED = 'F'
union all
SELECT * FROM #tmp WHERE l_SUB = 'g1sx' AND l_ED = 'E'
union all
SELECT * FROM #tmp WHERE l_SUB = 'g1sx' AND l_ED = 'G'
union all
SELECT * FROM #tmp WHERE l_SUB = 'g1wl' AND l_ED = 'A'
union all
SELECT * FROM #tmp WHERE l_SUB = 'g1wl' AND l_ED = 'F'
union all
SELECT * FROM #tmp WHERE l_SUB = 'g1wl' AND l_ED = 'G'
union all
SELECT * FROM #tmp WHERE l_SUB = 'g1hx' AND l_ED = 'G'
union all
SELECT * FROM #tmp WHERE l_SUB = 'g1hx' AND l_ED = 'B'
union all
SELECT * FROM #tmp WHERE l_SUB = 'g1hx' AND l_ED = 'C'
union all
SELECT * FROM #tmp WHERE l_SUB = 'g1ls' AND l_ED = 'A'
union all
SELECT * FROM #tmp WHERE l_SUB = 'g1ls' AND l_ED = 'F'
union all
SELECT * FROM #tmp WHERE l_SUB = 'g1ls' AND l_ED = 'C'
union all
SELECT * FROM #tmp WHERE l_SUB = 'g1dl' AND l_ED = 'G'
union all
SELECT * FROM #tmp WHERE l_SUB = 'g1dl' AND l_ED = 'H'
union all
SELECT * FROM #tmp WHERE l_SUB = 'g1dl' AND l_ED = 'W'
union all
SELECT * FROM #tmp WHERE l_SUB = 'g1sw' AND l_ED = 'A'
union all
SELECT * FROM #tmp WHERE l_SUB = 'g1sw' AND l_ED = 'F'
union all
SELECT * FROM #tmp WHERE l_SUB = 'g1sw' AND l_ED = 'C'
union all
SELECT * FROM #tmp WHERE l_SUB = 'g1zz' AND l_ED = 'A'
union all
SELECT * FROM #tmp WHERE l_SUB = 'g1zz' AND l_ED = 'G'
union all
SELECT * FROM #tmp WHERE l_SUB = 'g1zz' AND l_ED = 'C'
对这两个字段分别做索引或联合索引(l_ED,l_SUB)
用union性能可能也不会好 会暂用大量temp空间
select * from lctSUB where l_ED = A and (l_SUB = x1 or l_SUB = x2 or lSUB = x3)
union all
select * from lctSUB where l_ED = B and (l_SUB = x1 or l_SUB = x2 ...)
union all
...
结构如下
l_SUB l_ED
g1yw A
g1yw B
...
...然后联合查询
select a.* from 表 a where exists(select 1 from #tmp where l_SUB =a.l_SUB and l_ED=a.l_ED)