SELECT TABLE_NAME,COLUMN_ID,COLUMN_NAME,DATA_TYPE,CHAR_LENGTH,NULLABLE,DATA_DEFAULT,count(*)
FROM user_tab_columns
GROUP BY TABLE_NAME;
这个会出错啊,怎么回事?我就是想它按照table name 分组而已...SELECT TABLE_NAME,count(*) FROM user_tab_columns
GROUP BY TABLE_NAME;
这个又没错...在线等!
AVG
COUNT
MAX
MIN
STDDEV
SUM
VARIANCEFYI:
http://blog.csdn.net/j2ee_fan/archive/2008/05/03/2373845.aspx
现在发现应该是我用错语句了,group by 是完全达不到我要的效果的。我想要从user_tab_column表中,取得每个表的列信息,然后将这些列信息按照表名分组显示出来
好像是要用compute by 语句,但是试了一下还是出错了。
我的代码如下:
SELECT TABLE_NAME,COLUMN_ID,COLUMN_NAME,DATA_TYPE,CHAR_LENGTH,NULLABLE,DATA_DEFAULT
FROM user_tab_columns
order by table_name
compute by table_name;谢谢各位先!
FROM user_tab_columns
where table_name in(SELECT TABLE_NAME FROM user_tab_columns group by table_name)
我运行了,得不到我要的结果,我要那些信息按照table_name分段显示出来
SELECT
NULL "COLUMN_ID",
NULL "COLUMN_NAME",
nULL "DATA_TYPE",
NULL "CHAR_LENGTH",
NULL "NULL_ABLE",
TABLE_NAME
FROM USER_TAB_COLUMNS
UNION SELECT
COLUMN_ID,COLUMN_NAME,DATA_type,CHAR_LENGTH,NULLABLE,TABLE_NAME
FROM USER_TAB_COLUMNS
ORDER BY TABLE_NAME,6 DESC;
SELECT DISTINCT TABLE_NAME,
NULL "COLUMN_ID",
NULL,
NULL,
NULL,
NULL,
NULL
FROM USER_TAB_COLUMNS
UNION
SELECT UT.TABLE_NAME,COLUMN_ID,UT.COLUMN_NAME,DATA_TYPE,CHAR_LENGTH,NULLABLE,DECODE(UT.COLUMN_NAME,CS.COLUMN_NAME,'Y','N') "IS_PAR_KEY"
FROM USER_TAB_COLUMNS UT,
(
SELECT UC.TABLE_NAME,
UCS.COLUMN_NAME
FROM USER_CONSTRAINTS UC,
USER_CONS_COLUMNS UCS
WHERE UC.TABLE_NAME = UCS.TABLE_NAME
AND UC.CONSTRAINT_NAME = UCS.CONSTRAINT_NAME
)CS
WHERE UT.TABLE_NAME = CS.TABLE_NAME(+)
ORDER BY TABLE_NAME,2 DESC;
SELECT TABLE_NAME,
NULL "COLUMN_ID",
NULL,
NULL,
NULL,
NULL,
NULL
FROM USER_TAB_COLUMNS
UNION
SELECT UT.TABLE_NAME,COLUMN_ID,UT.COLUMN_NAME,DATA_TYPE,CHAR_LENGTH,NULLABLE,DECODE(UT.COLUMN_NAME,CS.COLUMN_NAME,'Y','N') "IS_PAR_KEY"
FROM USER_TAB_COLUMNS UT,
(
SELECT UC.TABLE_NAME,
UCS.COLUMN_NAME
FROM USER_CONSTRAINTS UC,
USER_CONS_COLUMNS UCS
WHERE UC.TABLE_NAME = UCS.TABLE_NAME
AND UC.CONSTRAINT_NAME = UCS.CONSTRAINT_NAME
AND UC.CONSTRAINT_TYPE = 'P'
)CS
WHERE UT.TABLE_NAME = CS.TABLE_NAME(+)
ORDER BY TABLE_NAME,2 DESC;