Select * from dep where DEP_CLASS=1 AND dep_code in (SELECT DISTINCT DEP1 FROM depview WHERE dep3 in ([color=#FFFF00]SELECT dep_code FROM person_limits WHERE upper(account)='" + UserID.ToUpper() + "') [/color] ) Order By LDAPI in的语法不用解释了吧
这是两个子查询的嵌套,先从最内层说起: SELECT dep_code FROM person_limits WHERE upper(account)='" + UserID.ToUpper() + "'这句的意思是在表person_limits 中查出account为界面上UserID大写的dep_code 值SELECT DISTINCT DEP1 FROM depview WHERE dep3 in (SELECT dep_code FROM person_limits WHERE upper(account)='" + UserID.ToUpper() + "') 这句的意思是,在上一条语句的前提下,在表depview 中查出DEP1的值,筛选条件为dep3 的值在上一条结果中,而且DISTINCT 可以去掉重复的记录Select * from dep where DEP_CLASS=1 AND dep_code in (SELECT DISTINCT DEP1 FROM depview WHERE dep3 in (SELECT dep_code FROM person_limits WHERE upper(account)='" + UserID.ToUpper() + "') )Order By LDAP 最后整体来看,就是从表dep中查出所有的记录,筛选条件为DEP_CLASS=1并且dep_code的值在上条语句查询出来的结果中,再按照LDAP从小到大排序
1.查出account为UserID(大写),所有DEP1不同的值
2.再查所有DEP_CLASS=1 并且dep_code等于上面查询出来的DEP1的值的数据
根据LDAP从小到大排序
(SELECT DISTINCT DEP1 FROM depview WHERE dep3 in
([color=#FFFF00]SELECT dep_code FROM person_limits WHERE upper(account)='" +
UserID.ToUpper() + "')
[/color] )
Order By LDAPI
in的语法不用解释了吧
SELECT dep_code FROM person_limits WHERE upper(account)='" + UserID.ToUpper() + "'这句的意思是在表person_limits 中查出account为界面上UserID大写的dep_code 值SELECT DISTINCT DEP1 FROM depview WHERE dep3 in (SELECT dep_code FROM person_limits WHERE upper(account)='" + UserID.ToUpper() + "') 这句的意思是,在上一条语句的前提下,在表depview 中查出DEP1的值,筛选条件为dep3 的值在上一条结果中,而且DISTINCT 可以去掉重复的记录Select * from dep where DEP_CLASS=1 AND dep_code in (SELECT DISTINCT DEP1 FROM depview WHERE dep3 in (SELECT dep_code FROM person_limits WHERE upper(account)='" + UserID.ToUpper() + "') )Order By LDAP
最后整体来看,就是从表dep中查出所有的记录,筛选条件为DEP_CLASS=1并且dep_code的值在上条语句查询出来的结果中,再按照LDAP从小到大排序