日期:2014-05-18  浏览次数:20390 次

帮忙看一下这个查询怎么优化呀?
ABCD四个字段,如果A符合条件,其他为0,或者A符合条件,B符合条件,其他为0

目前是这样查询的,怎么样可以优化一下呢?

(A=a AND B=0 AND C=0 AND D=0) OR (A=a AND B=b AND C=0 AND D=0) OR (A=a AND B=b AND C=c AND D=0) OR (A=a AND B=b AND C=c AND D=d)

------解决方案--------------------
SQL code
where (A=a and B=0 or A=a and B=b) and C=0 and D=0

------解决方案--------------------
探讨
ABCD四个字段,如果A符合条件,其他为0,或者A符合条件,B符合条件,其他为0

目前是这样查询的,怎么样可以优化一下呢?

(A=a AND B=0 AND C=0 AND D=0) OR (A=a AND B=b AND C=0 AND D=0) OR (A=a AND B=b AND C=c AND D=0) OR (A=a AND B=b AND C=c AND D=d)

------解决方案--------------------
SQL code
WHERE A=A AND (B=0 OR (B=B AND (C=0 OR C=C AND (D=0 OR D=D))))

--一级一级提取公因式,虽然这样语句少了,但逻辑很难理解,楼主不要为了优化,把程序弄得难以理解

------解决方案--------------------
直接说你的需求...可能会好点..说不定这个逻辑不是你要的需求哪
------解决方案--------------------
用union all改写

SQL code
select ...
where (A=a AND B=0 AND C=0 AND D=0) 
union all
select ...
where (A=a AND B=b AND C=0 AND D=0) 
union all
select ...
where (A=a AND B=b AND C=c AND D=0) 
union all
select ...
where (A=a AND B=b AND C=c AND D=d)

------解决方案--------------------
楼主觉得 (a and b) or c 与 (a or c) and (b or c) 哪一个更优化吗?