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
--......为什么??????
的结果如下,This table contains the details of each and every field ...居然和上面的地一条和第4条不一样?
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
问题出在:or tab.table_name like'%W%'
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.ownerunionselect 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这个没问题,再想想为什么?
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 '%YYQ4%'and tab.table_name = comm.table_name
and tab.owner = comm.owner
----------------------------------
ower TABLE_NAME COMMENTS
CASKOKYOTEST YYQ4
CASKOKYO YYQ4
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;
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 里面没有听说关键字还有优先级啊, 有吗?-- 觉得还有些问题没相通 想想想想想想想想。
and相当于集合中的交集.
肯定是先算交集再算并集啊.
遇到不确定优先等级的运算就加括号吧