写了一个小程序,用于于公司内部的信息交流,很多权限等限制,再加上相关条件的搜索,就如此复杂了。
请高手看看是不是绕弯了?谢谢!SELECT * FROM 文档信息 WHERE 帐套编号 = 1 and 目录状态='1' and 目录状态1='1' AND 日期 >= '2009-09-01' AND 日期 <= '2009-09-30' AND 发自 LIKE '%zhangsan%' AND (标题 LIKE '%国庆%' or 正文 like '%国庆%' or 文档标识 in (select DISTINCT 补充对象标识 FROM 文档补充信息 where 帐套编号 = 1 AND [t] LIKE '%国庆%')) AND (已读 Not LIKE '%,lin,%' AND 发自 <> 'lin') AND ((发自 = 'lin' and 文档状态 in ('1','2')) OR (文档状态 = '1' AND 有效期开始 <= '2009/9/22' AND 有效期结束 >= '2009/9/22' ) AND (发自 = 'lin' OR (',' + replace(接收人,' ','') + ',') LIKE '%,所有人,%' OR (',' + replace(接收人,' ','') + ',') LIKE '%,lin,%' OR (',' + replace(接收人,' ','') + ',') LIKE '%,所有人,%' OR (',' + replace(接收人,' ','') + ',') LIKE '%,销售部,%')) AND ((目录权限 LIKE '%L_fw{全部}%' OR 目录权限 LIKE '%L_fw{销售部}%' ) OR ((目录权限 is null or RTRIM(目录权限)='') AND (目录权限1 LIKE '%L_fw{全部}%' OR 目录权限1 LIKE '%L_fw{销售部}%' ))) ORDER BY 文档编号 desc
请高手看看是不是绕弯了?谢谢!SELECT * FROM 文档信息 WHERE 帐套编号 = 1 and 目录状态='1' and 目录状态1='1' AND 日期 >= '2009-09-01' AND 日期 <= '2009-09-30' AND 发自 LIKE '%zhangsan%' AND (标题 LIKE '%国庆%' or 正文 like '%国庆%' or 文档标识 in (select DISTINCT 补充对象标识 FROM 文档补充信息 where 帐套编号 = 1 AND [t] LIKE '%国庆%')) AND (已读 Not LIKE '%,lin,%' AND 发自 <> 'lin') AND ((发自 = 'lin' and 文档状态 in ('1','2')) OR (文档状态 = '1' AND 有效期开始 <= '2009/9/22' AND 有效期结束 >= '2009/9/22' ) AND (发自 = 'lin' OR (',' + replace(接收人,' ','') + ',') LIKE '%,所有人,%' OR (',' + replace(接收人,' ','') + ',') LIKE '%,lin,%' OR (',' + replace(接收人,' ','') + ',') LIKE '%,所有人,%' OR (',' + replace(接收人,' ','') + ',') LIKE '%,销售部,%')) AND ((目录权限 LIKE '%L_fw{全部}%' OR 目录权限 LIKE '%L_fw{销售部}%' ) OR ((目录权限 is null or RTRIM(目录权限)='') AND (目录权限1 LIKE '%L_fw{全部}%' OR 目录权限1 LIKE '%L_fw{销售部}%' ))) ORDER BY 文档编号 desc
WHERE
帐套编号 = 1
and 目录状态='1' and 目录状态1='1'
AND 日期 >= '2009-09-01' AND 日期 <= '2009-09-30'
AND 发自 LIKE '%zhangsan%'
AND (标题 LIKE '%国庆%' or 正文 like '%国庆%' or 文档标识 in (select DISTINCT 补充对象标识 FROM 文档补充信息 where 帐套编号 = 1 AND [t] LIKE '%国庆%'))
AND (已读 Not LIKE '%,lin,%' AND 发自 <> 'lin')
AND ((发自 = 'lin' and 文档状态 in ('1','2')) OR (文档状态 = '1' AND 有效期开始 <= '2009/9/22' AND 有效期结束 >= '2009/9/22' )
AND (发自 = 'lin' OR (',' + replace(接收人,' ','') + ',') LIKE '%,所有人,%' OR (',' + replace(接收人,' ','') + ',') LIKE '%,lin,%' OR (',' + replace(接收人,' ','') + ',') LIKE '%,所有人,%' OR (',' + replace(接收人,' ','') + ',') LIKE '%,销售部,%'))
AND (
(目录权限 LIKE '%L_fw{全部}%' OR 目录权限 LIKE '%L_fw{销售部}%' )
OR
((目录权限 is null or RTRIM(目录权限)='') AND (目录权限1 LIKE '%L_fw{全部}%' OR 目录权限1 LIKE '%L_fw{销售部}%' )))
ORDER BY 文档编号 desc
这个软件里分两层目录,每个目录可以分别设置不同用户组的人访问、打印、评论、发表等权限
,还需要设置子目录是否继承上级目录权限。这个sql是用于综合搜索的,还要一些标题、正文、作者、有效期、发布日期等条件,实在头大啊!