select session.* from hacref.session,hacconf.device,hacconf.orgunit where serverIP=device.ip and device.ou_id=orgunit.id and (orgunit.ou_id&(select ou_id from hacconf.orgunit where id=4))=(select ou_id from hacconf.orgunit where id=4);
有两条语句,分别根据username,serverTP过滤条件,我想实现两者同时过滤,直接两条件AND会是空,需要帮助
15 个解决方案
#1
用or
#2
用OR的话达不到效果,
#3
你语句里面的这个是干嘛的?
select session.* from hacref.session,hacconf.user,hacconf.orgunit where session.username=user.username and user.ou_id=orgunit.id and (orgunit.ou_id&(select ou_id from hacconf.orgunit where id=4))=(select ou_id from hacconf.orgunit where id=4);
select session.* from hacref.session,hacconf.device,hacconf.orgunit where serverIP=device.ip and device.ou_id=orgunit.id and (orgunit.ou_id&(select ou_id from hacconf.orgunit where id=4))=(select ou_id from hacconf.orgunit where id=4);
报错,无法帮你改
select session.* from hacref.session,hacconf.user,hacconf.orgunit where session.username=user.username and user.ou_id=orgunit.id and (orgunit.ou_id&(select ou_id from hacconf.orgunit where id=4))=(select ou_id from hacconf.orgunit where id=4);
select session.* from hacref.session,hacconf.device,hacconf.orgunit where serverIP=device.ip and device.ou_id=orgunit.id and (orgunit.ou_id&(select ou_id from hacconf.orgunit where id=4))=(select ou_id from hacconf.orgunit where id=4);
报错,无法帮你改
#4
这个是组织的字段,通过 位与 判断user,device是否是id=4的user下,如果是过滤出来,不是不过滤,
#5
俩个查询的字段列表都不一样,怎么合并呢.
#6
我也纳闷,mssql有这个用法吗
#7
有是有这种语法,只是语句写的貌似不合理,特别是最后=(select ... where id=4)那段,比较危险,一旦没控制好,数据出现2条以上就报错了,而且两个查询的表都不一样,合并不了的,要么就用union all合起来。
#8
是啊,我的困难也在这啊,user对应一个OU_id需要判断过滤,device也对应一个Ou_Id也需要判断过滤,还要把两者过滤出来的显示出来,不一定非是一条语句,功能实现就行,一条最好
#9
如果你2个结果是要合起来用的,那使用union 或者union all把结果集合起来就可以了。由于你的表都不一样,又需要同时2个查询都执行。搞不了一起的
#10
那我的功能就实现不了了,第一个只过滤了用户,设备不在组织的也会有,第二个只过滤设备,用户不在组织的也会有,两者加起来相当于没有过滤,难道这个思路走不通,那我得重新找思路了
#11
可以用union all ,然后怎么只把两者的重复的过滤出来;
#12
union 本身就会过滤重复数据。还有except这些也是可以过滤重复数据的,不知道你要怎么过滤。你自己看看联机丛书
#13
SELECT A1.*
FROM hacref.session AS A1
JOIN hacconf.user AS A2 ON (A1.username=A2.username)
JOIN hacconf.orgunit AS A3 ON (A2.ou_id=A3.ID)
JOIN hacconf.device AS A4 ON (A1.serverIP=A4.IP AND A4.OU_ID=A3.ID)
WHERE (A3.OU_ID & (select ou_id from hacconf.orgunit where id=4)) =
(select ou_id from hacconf.orgunit where id=4)
#14
逻辑结构不明显,你只写了你的语句,如果你的语句是对的 那就应该是and了
#15
首先是两条合并 考虑union 而不是or或者and 但从字意上理解就可以区别出来 使用union 必须将两个sql语句的 列数量保持一致 ,写代码细心很重要 ,
#1
用or
#2
用OR的话达不到效果,
#3
你语句里面的这个是干嘛的?
select session.* from hacref.session,hacconf.user,hacconf.orgunit where session.username=user.username and user.ou_id=orgunit.id and (orgunit.ou_id&(select ou_id from hacconf.orgunit where id=4))=(select ou_id from hacconf.orgunit where id=4);
select session.* from hacref.session,hacconf.device,hacconf.orgunit where serverIP=device.ip and device.ou_id=orgunit.id and (orgunit.ou_id&(select ou_id from hacconf.orgunit where id=4))=(select ou_id from hacconf.orgunit where id=4);
报错,无法帮你改
select session.* from hacref.session,hacconf.user,hacconf.orgunit where session.username=user.username and user.ou_id=orgunit.id and (orgunit.ou_id&(select ou_id from hacconf.orgunit where id=4))=(select ou_id from hacconf.orgunit where id=4);
select session.* from hacref.session,hacconf.device,hacconf.orgunit where serverIP=device.ip and device.ou_id=orgunit.id and (orgunit.ou_id&(select ou_id from hacconf.orgunit where id=4))=(select ou_id from hacconf.orgunit where id=4);
报错,无法帮你改
#4
这个是组织的字段,通过 位与 判断user,device是否是id=4的user下,如果是过滤出来,不是不过滤,
#5
俩个查询的字段列表都不一样,怎么合并呢.
#6
我也纳闷,mssql有这个用法吗
#7
有是有这种语法,只是语句写的貌似不合理,特别是最后=(select ... where id=4)那段,比较危险,一旦没控制好,数据出现2条以上就报错了,而且两个查询的表都不一样,合并不了的,要么就用union all合起来。
#8
是啊,我的困难也在这啊,user对应一个OU_id需要判断过滤,device也对应一个Ou_Id也需要判断过滤,还要把两者过滤出来的显示出来,不一定非是一条语句,功能实现就行,一条最好
#9
如果你2个结果是要合起来用的,那使用union 或者union all把结果集合起来就可以了。由于你的表都不一样,又需要同时2个查询都执行。搞不了一起的
#10
那我的功能就实现不了了,第一个只过滤了用户,设备不在组织的也会有,第二个只过滤设备,用户不在组织的也会有,两者加起来相当于没有过滤,难道这个思路走不通,那我得重新找思路了
#11
可以用union all ,然后怎么只把两者的重复的过滤出来;
#12
union 本身就会过滤重复数据。还有except这些也是可以过滤重复数据的,不知道你要怎么过滤。你自己看看联机丛书
#13
SELECT A1.*
FROM hacref.session AS A1
JOIN hacconf.user AS A2 ON (A1.username=A2.username)
JOIN hacconf.orgunit AS A3 ON (A2.ou_id=A3.ID)
JOIN hacconf.device AS A4 ON (A1.serverIP=A4.IP AND A4.OU_ID=A3.ID)
WHERE (A3.OU_ID & (select ou_id from hacconf.orgunit where id=4)) =
(select ou_id from hacconf.orgunit where id=4)
#14
逻辑结构不明显,你只写了你的语句,如果你的语句是对的 那就应该是and了
#15
首先是两条合并 考虑union 而不是or或者and 但从字意上理解就可以区别出来 使用union 必须将两个sql语句的 列数量保持一致 ,写代码细心很重要 ,