17,086
社区成员
发帖
与我相关
我的任务
分享
select tab.owner,tab.table_name,comm.comments
from all_tables tab,
all_tab_comments comm
where tab.table_name like '%M%' or tab.table_name like'%W%'
and tab.table_name = comm.table_name
and tab.owner = comm.owner
order by owner
-- 在两个表中同一个owner 下 table_name 应该是唯一的吧
-- 例如:select * from all_tab_comments where table_name = 'SRMFORMDETAILS'
-- 只有一条记录,
-- select * from all_tables where table_name = 'SRMFORMDETAILS'
-- 也只有一条记录,
-- 两个表用table name 连接的话,应该也只有一条记录出现,
-- 可是结果却有n 条, 为什么?
--1 CRMUSER SRMFORMDETAILS This table holds all the address ......
--2 CRMUSER SRMFORMDETAILS
--3 CRMUSER SRMFORMDETAILS
--4 CRMUSER SRMFORMDETAILS This table is for internal use. ......
--5 CRMUSER SRMFORMDETAILS
--6 CRMUSER SRMFORMDETAILS
--......
select tab.owner,tab.table_name,comm.comments
from all_tables tab,
all_tab_comments comm
where tab.table_name like '%MF%'
or
(tab.table_name like '%WMS%'
and tab.table_name = comm.table_name
and tab.owner = comm.owner)
order by owner
-- 这段代码和最开始的那段结果是一样的,
-- 是不是可以按优先级来理解解: and 的优先级比or的要高,
-- oracle 里面没有听说关键字还有优先级啊, 有吗?
-- 觉得还有些问题没相通 想想想想想想想想。。。。
SELECT * FROM
(select tab.owner,tab.table_name,comm.comments
from all_tables tab,
all_tab_comments comm
where tab.table_name like '%M%'
UNION ALL
select tab.owner,tab.table_name,comm.comments
from all_tables tab,
all_tab_comments comm
where tab.table_name like'%W%'
and tab.table_name = comm.table_name
and tab.owner = comm.owner)
ORDER BY OWNER;
--我这里不会重复的,只有一条啊
select tab.owner,tab.table_name,comm.comments
from all_tables tab,
all_tab_comments comm
where tab.table_name like '%YYQ4%'
and tab.table_name = comm.table_name
and tab.owner = comm.owner
----------------------------------
ower TABLE_NAME COMMENTS
CASKOKYOTEST YYQ4
CASKOKYO YYQ4
select tab.owner,tab.table_name,comm.comments
from all_tables tab,
all_tab_comments comm
where (tab.table_name like '%M%' or tab.table_name like'%W%' )
and tab.table_name = comm.table_name
and tab.owner = comm.owner
order by owner
select tab.owner,tab.table_name,comm.comments
from all_tables tab,
all_tab_comments comm
where tab.table_name like '%M%'
and tab.table_name = comm.table_name
and tab.owner = comm.owner
union
select tab.owner,tab.table_name,comm.comments
from all_tables tab,
all_tab_comments comm
where tab.table_name like'%W%'
and tab.table_name = comm.table_name
and tab.owner = comm.owner
order by owner
select tab.owner,tab.table_name,comm.comments
from all_tables tab,
all_tab_comments comm
where tab.table_name like '%M%' or tab.table_name like'%W%'
-- 这不是笛卡尔积啊
and tab.table_name = comm.table_name
and tab.owner = comm.owner
order by owner