求一個sql語法--列出目前PK的欄位名稱

显示全部楼层 · 2008-6-30 12:48:39

各位大虾們~~
小妹我求一個sql語法：
若我想列出目前PK的欄位名稱，不過不是水平列示~~是垂直列法的話~該怎麼寫SQL？
希望結果如下：
CONSTRAINT_NAME COL1 COL2 COL3
PK_APPLE ID ID2 PRO_ID
PK_MERCH MEM_ID CUID PRO_ID
請問我要如何怎麼寫才能像結果那樣？
試了好久~~就是試不出來！
急~~~
求~~~~

千问 · 2008-6-30 12:48:39

繁体字看的好难受，一看就是台资企业，(很多字不认识)，呵呵

千问 · 2008-6-30 12:48:39

[PHP]select a.constraint_name,
max(decode(a.position, 1, a.column_name)) col1,
max(decode(a.position, 2, a.column_name)) col2,
max(decode(a.position, 3, a.column_name)) col3
from user_cons_columns a
group by a.constraint_name[/PHP]

千问 · 2008-6-30 12:48:39

怎么group by 看不到呢。。。奇怪。。。。
select a.constraint_name,
max(decode(a.position, 1, a.column_name)) col1,
max(decode(a.position, 2, a.column_name)) col2,
max(decode(a.position, 3, a.column_name)) col3
from user_cons_columns a
group by a.constraint_name

千问 · 2008-6-30 12:48:39

感謝

oradbHome大師解救~~~

其實我後來也找到一些相關的sql，不過大多推見pl/sql，後來也看到類似的sql：
select a.table_name AS table_name ,a.column_name AS col1,b.column_name AS col2,c.column_name AS col3, d.column_name AS col4
from (select TABLE_name,COLUMN_NAME from user_cons_columns where position=1 and constraint_name LIKE'PK_%') a,
(select TABLE_name,COLUMN_NAME from user_cons_columns where position=2 and constraint_nameLIKE'PK_%') b,
(select TABLE_name,COLUMN_NAME from user_cons_columns where position=3 and constraint_nameLIKE'PK_%') c,
(select TABLE_name,COLUMN_NAME from user_cons_columns where position=4 and constraint_nameLIKE'PK_%') d
where a.table_name=b.table_name(+) and a.table_name=c.table_name(+) and a.table_name=d.table_name(+)
and a.table_name LIKE 'TB%'
ORDER by a.table_name ;
不過跟oradbHome比起來的sql，還是你比較牛！！

千问 · 2008-6-30 12:48:39

如果要找PK ，其实还要连接User_Constraints视图，来查找PK
SELECT a.constraint_name,
MAX(decode(a.position, 1, a.column_name)) col1,
MAX(decode(a.position, 2, a.column_name)) col2,
MAX(decode(a.position, 3, a.column_name)) col3
FROM user_cons_columns a, User_Constraints b
WHERE a.constraint_name = b.constraint_name
AND b.constraint_type = 'P'
GROUP BY a.constraint_name

千问 · 2008-6-30 12:48:39

感謝大虾~~~
小女子受教了！