度量快速开发平台-专业、快速的软件定制快开平台
标题:
oracle规则表达式趣题 (转)
[打印本页]
作者:
张兴康
时间:
2020-7-23 17:31
标题:
oracle规则表达式趣题 (转)
我有一张表记录了我们系统的登录记录:谁在什么时间从哪个主机登录
create table plch_logins (
when timestamp(0) not null
, who varchar2(10) not null
, host varchar2(30) not null
)
/
insert into plch_logins values (
timestamp '2016-02-01 10:00:10', 'John', '192.168.200.155'
)
/
insert into plch_logins values (
timestamp '2016-02-01 10:01:20', 'Jill', '10.0.0.42'
)
/
insert into plch_logins values (
timestamp '2016-02-01 10:02:30', 'Jack', 'pc42.plchhost.org'
)
/
insert into plch_logins values (
timestamp '2016-02-01 10:03:40', 'Jeff', 'cubic.com10.net'
)
/
insert into plch_logins values (
timestamp '2016-02-01 10:04:50', 'John', '192.168.200.155'
)
/
commit
/
有时候host是主机名字,有时候是IP地址。
我想知道每个主机的登录次数,但仅仅是对使用了IP地址的记录(而不是主机名字)。为此我有这个未完成的查询:
select host
, count(*) logins
from plch_logins
##REPLACE##
group by host
order by logins desc, host
/
哪些选项可用来取代##REPLACE##使得查询返回如下输出:
HOST LOGINS
------------------------------ ----------
192.168.200.155 2
10.0.0.42 1
(A)
where regexp_like(host, '^\d{1,3}(\.\d{1,3}){3}$')
(B)
where regexp_like(host, '^\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}$')
(C)
where regexp_like(host, '^\d\d?\d?\.\d\d?\d?\.\d\d?\d?\.\d\d?\d?$')
(D)
where regexp_like(
host
, '^[[:digit:]]{1,3}\.[[:digit:]]{1,3}\.' ||
'[[:digit:]]{1,3}\.[[:digit:]]{1,3}$'
)
(E)
where regexp_substr(host, '^\d{1,3}(\.\d{1,3}){3}$') = host
(F)
where regexp_substr(host, '^\d{1,3}(\.\d{1,3}){3}$') is not null
(G)
where regexp_replace(host, '^\d{1,3}(\.\d{1,3}){3}$') is null
(H)
where instr(host, '.', 1, 1) between 2 and 4
and instr(host, '.', 1, 2) between instr(host, '.', 1, 1) + 2
and instr(host, '.', 1, 1) + 4
and instr(host, '.', 1, 3) between instr(host, '.', 1, 2) + 2
and instr(host, '.', 1, 2) + 4
and instr(host, '.', 1, 3) >= length(host) - 3
and instr(host, '.', 1, 4) = 0
and regexp_replace(host, '[^0123456789.]') = host
(I)
where instr(host, '.', 1, 1) between 2 and 4
and instr(host, '.', 1, 2) between instr(host, '.', 1, 1) + 2
and instr(host, '.', 1, 1) + 4
and instr(host, '.', 1, 3) between instr(host, '.', 1, 2) + 2
and instr(host, '.', 1, 2) + 4
and instr(host, '.', 1, 3) >= length(host) - 3
and instr(host, '.', 1, 4) = 0
and translate(
host
, '#' || translate(host, '#0123456789.', '#')
, '#'
) = host
作者:
万望
时间:
2020-7-23 18:06
有点高深
作者:
张兴康
时间:
2020-7-24 13:17
万望 发表于 2016-2-23 18:06
有点高深
慢慢看看总能看懂的
欢迎光临 度量快速开发平台-专业、快速的软件定制快开平台 (http://p.delit.cn/)
Powered by Discuz! X3.2