如有表A字段 ip1 ip2
192.168.1.1 192.168.1.1
192.168.1.1 192.168.1.2
192.168.1.3,192.168.1.22 192.168.1.3 想查询得到这样的结果
ip1 ip2 if_ip_changge
192.168.1.1 192.168.1.1 No
192.168.1.1 192.168.1.2 Yes
192.168.1.3,192.168.1.22 192.168.1.3 No
ip1是初始ip ip2是最后修改的ip 如果有改变则为yes 没有改变则为No 再多个ip中 最后修改的ip 与初始ip有一个相同 为No
192.168.1.1 192.168.1.1
192.168.1.1 192.168.1.2
192.168.1.3,192.168.1.22 192.168.1.3 想查询得到这样的结果
ip1 ip2 if_ip_changge
192.168.1.1 192.168.1.1 No
192.168.1.1 192.168.1.2 Yes
192.168.1.3,192.168.1.22 192.168.1.3 No
ip1是初始ip ip2是最后修改的ip 如果有改变则为yes 没有改变则为No 再多个ip中 最后修改的ip 与初始ip有一个相同 为No
解决方案 »
- ORACLE中UTL_HTTP如何使用
- 建触发器时报错
- 有关Oracle区分单引号和不区分单引号的问题
- 请问有什么办法能将ACCESS数据导入ORACLE?
- 郁闷的问题: 连接 Oracle9i 错误: The Network Adapter could not establish the connection
- 请问:数据库显示"high parse to execute ratio"是什么意思?
- 紧急````````
- win2000中的管理工具》服务中没有监听服务是怎么回事啊?
- 进来帮帮忙!!谢谢!!一定给分!!
- oracle 图片存储问题
- oracle 表因为update 大长度字段,导致表空间增长过快问题,高手请进
- merge into的效率问题,寻求帮助
WITH t AS (
SELECT '192.168.1.1' ip1,'192.168.1.1' ip2 FROM DUAL UNION ALL
SELECT '192.168.1.1' ip1,'192.168.1.2' ip2 FROM DUAL UNION ALL
SELECT '192.168.1.3' ip1,'192.168.1.22 192.168.1.3' ip2 FROM DUAL
)
SELECT t.*,CASE WHEN instr(ip2,ip1) = 0 THEN 'yes' ELSE 'no' END FROM t
你的需求应该是ip2 只能有一个ip地址吧
SQL> WITH t AS (
2 SELECT '192.168.1.1' ip1,'192.168.1.1' ip2 FROM DUAL UNION ALL
3 SELECT '192.168.1.1' ip1,'192.168.1.2' ip2 FROM DUAL UNION ALL
4 SELECT '192.168.1.3' ip1,'192.168.1.22 192.168.1.3' ip2 FROM DUAL
5 )
6 select ip1,
7 ip2,
8 case when instr(ip1, ip2) > 0 then 'NO'
9 else 'YES' END CASE
10 from t
11 /IP1 IP2 CASE
----------- ------------------------- ----
192.168.1.1 192.168.1.1 NO
192.168.1.1 192.168.1.2 YES
192.168.1.3 192.168.1.22 192.168.1.3 YESSQL>
为什么都不看 ,这个符号的。 case when instr(ip1, ip2) > 0 then 'YEA' else 'NO' end 这条语句 第三条记录是不行的 因为第三条 是No
SQL> WITH t AS
2 (SELECT '192.168.1.1' ip1, '192.168.1.1' ip2 FROM DUAL
3 UNION ALL
4 SELECT '192.168.1.1' ip1, '192.168.1.2' ip2 FROM DUAL
5 UNION ALL
6 SELECT '192.168.1.3,192.168.1.22' ip1, '192.168.1.3' ip2 FROM DUAL)
7 SELECT t.*,
8 CASE WHEN instr(ip1, ip2) > 0 THEN
9 'no'
10 ELSE
11 'yes'
12 END
13 FROM t;
IP1 IP2 CASEWHENINSTR(IP1,IP2)>0THEN'N
------------------------ ----------- ------------------------------
192.168.1.1 192.168.1.1 no
192.168.1.1 192.168.1.2 yes
192.168.1.3,192.168.1.22 192.168.1.3 no
SQL>
好苦恼
第三条记录
192.168.1.1 192.168.1.1 no
192.168.1.1 192.168.1.2 yes
192.168.1.1,192.168.1.22 92.168.1.1 no
SQL> WITH t AS
2 (SELECT '192.168.1.1' ip1, '192.168.1.1' ip2 FROM DUAL UNION ALL
3 SELECT '192.168.1.1' ip1, '192.168.1.2' ip2 FROM DUAL UNION ALL
4 SELECT '192.168.1.3,192.168.1.22' ip1, '192.168.1.3' ip2 FROM DUAL UNION ALL
5 SELECT '192.168.1.1,192.168.1.22' ip1, '92.168.1.1' ip2 FROM DUAL
6 )
7 SELECT t.*,
8 CASE WHEN instr(','||ip1||',', ','||ip2||',') > 0 THEN
9 'no'
10 ELSE
11 'yes'
12 END if_ip_change
13 FROM t;
IP1 IP2 IF_IP_CHANGE
------------------------ ----------- ------------
192.168.1.1 192.168.1.1 no
192.168.1.1 192.168.1.2 yes
192.168.1.3,192.168.1.22 192.168.1.3 no
192.168.1.1,192.168.1.22 92.168.1.1 yes
SQL>
(SUBSTR(IP2,1,LENGTH(IP1))=IP1 AND SUBSTR(IP2,LENGTH(IP1)+1,1)=',')
OR (SUBSTR(IP2,-LENGTH(IP1))=IP1 AND SUBSTR(IP2,-(LENGTH(IP1)+1),1)=',')
OR INSTR(IP2,','||IP1||',')>0
THEN 'NO' ELSE 'YES' END
from dd