优化一条sql语句
SELECT *
FROM VDISCUSSIONMEETING_PRE
WHERE
Sys_U_Code = 'ww@km.com.cn '
OR
(DMP_Type IN
(SELECT DISTINCT BM_MeetingTypeCode
FROM Sys_User_Role
WHERE Sys_U_Code = 'ww@km.com.cn ' AND Sys_R_Code IN ( '2 ', '3 ', '4 ') AND DomainShortName = 'km ') AND
(SYS_DEPT_SYS_D_NOSEND = 'km '
OR
Sys_D_No = 'km ') AND Sys_U_Code <> 'ww@km.com.cn ')
OR
(DMP_Type IN
(SELECT DISTINCT BM_MeetingTypeCode
FROM Sys_User_Role
WHERE Sys_U_Code = 'ww@km.com.cn ' AND Sys_R_Code IN ( '2 ', '3 ', '4 ') AND DomainShortName = 'sh ') AND
(SYS_DEPT_SYS_D_NOSEND = 'sh '
OR
Sys_D_No = 'sh ') AND Sys_U_Code <> 'ww@km.com.cn ')
OR
(DMP_Type IN
(SELECT DISTINCT BM_MeetingTypeCode
FROM Sys_User_Role
WHERE Sys_U_Code = 'ww@km.com.cn ' AND Sys_R_Code IN ( '2 ', '3 ', '4 ') AND DomainShortName = 'bj ') AND
(SYS_DEPT_SYS_D_NOSEND = 'bj '
OR
Sys_D_No = 'bj ') AND Sys_U_Code <> 'ww@km.com.cn ')
表和字段名字等好多都有点乱，经过好多人的手了，几年前的东西，这个一开始做的时候也不正规。类似于这种sql语句能优化吗？这条语句有个规律可以一直or下去。。各位帮忙，谢谢
[解决办法]
用union 代替 or 如
SELECT *
FROM VDISCUSSIONMEETING_PRE
WHERE
Sys_U_Code = 'ww@km.com.cn '
OR
(DMP_Type IN
(SELECT DISTINCT BM_MeetingTypeCode
FROM Sys_User_Role
WHERE Sys_U_Code = 'ww@km.com.cn ' AND Sys_R_Code IN ( '2 ', '3 ', '4 ') AND DomainShortName = 'km ') AND
(SYS_DEPT_SYS_D_NOSEND = 'km '
可改为
SELECT * FROM VDISCUSSIONMEETING_PRE WHERE Sys_U_Code = 'ww@km.com.cn '
union
SELECT * FROM VDISCUSSIONMEETING_PRE WHERE (DMP_Type IN (SELECT DISTINCT BM_MeetingTypeCodeFROM Sys_User_Role WHERE Sys_U_Code = 'ww@km.com.cn ' AND Sys_R_Code IN ( '2 ', '3 ', '4 ') AND DomainShortName = 'km ') AND SYS_DEPT_SYS_D_NOSEND = 'km '
------解决方案--------------------
一直OR下去？
可否分开来查询，再合并。。。
Sys_R_Code、Sys_U_Code、Sys_D_no加索引
[解决办法]
--try
SELECT *
FROM VDISCUSSIONMEETING_PRE a
WHERE exists
(SELECT 1 FROM Sys_User_Role
WHERE Sys_U_Code = 'ww@km.com.cn ' AND Sys_R_Code IN ( '2 ', '3 ', '4 ')
AND (DomainShortName = a.SYS_DEPT_SYS_D_NOSEND or DomainShortName = a.Sys_D_No)
)
