« 选择了错误的实现方法, 其实很简单! »
DBA » http://www.anysql.net/dba/choose_wrong_function.html 2007-04-24今天写了下面这一段SQL语句, 在写之前总觉得有更简便的方法, 可就是没有想出来:
SELECT
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TABLE_NAME,’0′,’#'),
’1′,’#') ,’2′,’#') ,’3′,’#') ,’4′,’#')
,’5′,’#') ,’6′,’#') ,’7′,’#') ,’8′,’#') ,’9′,’#') PATTERN_NAME
FROM USER_TABLES
下班时坐地铁, 突然想到了Translate函数, 原来可以这么简单:
SELECT
TRANSLATE(TABLE_NAME,’0123456789′,’##########’) PATTERN_NAME
FROM USER_TABLES
我写这个SQL是用来找出一些表结构应当相同的表, 最终的SQL应当如下:
SELECT TABLE_NAME FROM USER_TABLES
WHERE TRANSLATE(TABLE_NAME,’0123456789′,’##########’) IN
(SELECT
TRANSLATE(TABLE_NAME,’0123456789′,’##########’) PATTERN_NAME
FROM USER_TABLES
GROUP BY TRANSLATE(TABLE_NAME,’0123456789′,’##########’)
HAVING COUNT(*) > 1)
看来选择最佳的方法是很必要的.
还有更简洁的, 运用正则表达式 替换 或者 查找:
select REGEXP_REPLACE(TABLE_NAME, ‘[1-9]‘, ‘#’ )
from cat;
还没有全部到10g啊, 学了Perl后, 正则表达式是第一个想到的.
提供一个只需要访问一次use_tables的方法
SELECT table_name FROM(
SELECT TABLE_NAME,RATIO_TO_REPORT (1) over(PARTITION BY TRANSLATE(TABLE_NAME,’0123456789′,’##########’) ) AS r
FROM USER_TABLES
) WHERE r < 1
改进型:
SELECT * FROM(
SELECT TABLE_NAME, count(*) over (PARTITION BY REGEXP_REPLACE(TABLE_NAME, ‘[0-9]+’, ‘#’ ) ) AS tab_cnt
, REGEXP_REPLACE(TABLE_NAME, ‘[0-9]+’, ‘#’ ) tran_tab
FROM USER_TABLES
) WHERE tab_cnt > 1
优点:
1) 两位数,3位数,n位数 都可以.
2) Count(*) over (PARTITION BY)
更容易理解, RATIO_TO_REPORT 分析函数有的用过了.
我最近正在学习 SQL Analytic Functions, 感谢 rollingpig 给出RATIO_TO_REPORT的这个用法.
REGEXP_REPLACE(TABLE_NAME, ‘[1-9]‘, ‘#’ )
更改成,
REGEXP_REPLACE(TABLE_NAME, ‘[1-9]+’, ‘#’ )
替换1到任意多个数字.
运行结果:
TABLE_NAME TAB_CNT TRAN_TAB
– — —
LOGMNR_HEADER2$ 2 LOGMNR_HEADER#$
LOGMNR_HEADER1$ 2 LOGMNR_HEADER#$
PARTS2 2 PARTS#
PARTS1 2 PARTS#
T101 4 T#
T2 4 T#
T1 4 T#
T3 4 T#
这个sql能找出表结构相同的表吗？
“这个SQL是用来找出一些表结构应当相同的表”
对照做了一下，不能啊
这个要根据公司的表命名规范来看.
当时我们相同表结构的表就是有相同的名字,只是序号不同.