--一共查出了13条数据
select * from user_tab_columns where rownum < 22 and table_name like '%MS_PERMISSION%'--下面的sql本来可以查出我想要的,可是一共只查出了5条数据,怎么少了8条
select u.*,t.POSITION as PK from USER_COL_COMMENTS u left join user_cons_columns t on u.column_name = t.column_name
where u.table_name like '%MS_PERMISSION%' and u.column_name like '%%' and u.table_name = t.table_name
order by u.table_name,t.POSITION
分析过程:用的left join on,USER_COL_COMMENTS做的左表,user_cons_columns 做的右表
产生原因:可能是左表中的注释本身就只有5条,导致左连接只有5条
解决方案:调换位置,用user_cons_columns做左表,USER_COL_COMMENTS做的右表,或者改为用right join,再查查试试,不行再留言
select u.*,t.POSITION as PK from USER_COL_COMMENTS u right join user_cons_columns t on u.column_name = t.column_name
where u.table_name like '%MS_PERMISSION%' and u.column_name like '%%' and u.table_name = t.table_name
order by u.table_name,t.POSITION分析的很对,可是还是不好用。还是5条。。MS_PERMISSION PERMISSION_NO
MS_PERMISSION INVALID_FLAG
MS_PERMISSION UPDATE_COUNT
MS_PERMISSION PERMISSION_NO
MS_PERMISSION PERMISSION_CHOICE_FLAG
应该是13条
MS_PERMISSION PERMISSION_NO
MS_PERMISSION TENANT_NO
MS_PERMISSION MASTER_CATEGORY_CD
MS_PERMISSION PERMISSION_NAME
MS_PERMISSION PERMISSION_MESSAGE
MS_PERMISSION REMARKS
MS_PERMISSION PERMISSION_CHOICE_FLAG
MS_PERMISSION INSERT_DATE
MS_PERMISSION INSERT_USER_NO
MS_PERMISSION UPDATE_DATE
MS_PERMISSION UPDATE_USER_NO
MS_PERMISSION UPDATE_COUNT
MS_PERMISSION INVALID_FLAG
SELECT U.*, T.POSITION AS PK
FROM USER_COL_COMMENTS U
RIGHT JOIN USER_CONS_COLUMNS T
ON U.COLUMN_NAME = T.COLUMN_NAME
AND U.TABLE_NAME = T.TABLE_NAME
AND U.TABLE_NAME LIKE '%MS_PERMISSION%'
AND U.COLUMN_NAME LIKE '%%'
ORDER BY U.TABLE_NAME, T.POSITION;
MS_PERMISSION PERMISSION_NO
MS_PERMISSION PERMISSION_NO
MS_PERMISSION PERMISSION_CHOICE_FLAG
MS_PERMISSION INVALID_FLAG
MS_PERMISSION UPDATE_COUNT
我的sql没错,只是user_cons_columns 表中只有5条数据 ,因为存的外建和主建