oracle 求每行的最小值,Oracle PL / SQL查找每列的最大值和最小值

您可以动态生成从表中选择所有列所需的SQL

SELECT 'SELECT '

|| LISTAGG( 'MIN("' || COLUMN_NAME || '") AS "MIN_' || COLUMN_NAME || '"'

|| ',MAX("' || COLUMN_NAME || '") AS "MAX_' || COLUMN_NAME || '"'

, ',' ) WITHIN GROUP ( ORDER BY COLUMN_ID )

|| ' FROM TBL'

FROM USER_TAB_COLUMNS

WHERE TABLE_NAME = 'YOURTABLENAME';

然后可以复制并运行，以输出每列的最小值和最大值 .

如果你坚持要动态地执行它(假设各种列的数据类型不同)：

Oracle 11g R2 Schema Setup ：

CREATE TABLE tbl (

asdfghjkl NUMBER,

poiuytrew VARCHAR2(3),

oiuytrewq DATE,

sdfghjkla NUMBER,

zxcvbnmcv NUMBER,

mnbvcxznb NUMBER,

qwertyuio NUMBER,

wertyuiop NUMBER

);

INSERT INTO tbl

SELECT LEVEL, LPAD( TO_CHAR( LEVEL ), 3, '0' ), TRUNC( SYSDATE ) - LEVEL, LEVEL+1, 10-LEVEL, LEVEL, ABS(5-LEVEL), POWER( 0.9, LEVEL )

FROM DUAL

CONNECT BY LEVEL < 10;

CREATE TABLE test AS SELECT * FROM tbl WHERE 1 = 0;

Query 1 ：

SELECT * FROM tbl

Results ：

| ASDFGHJKL | POIUYTREW | OIUYTREWQ | SDFGHJKLA | ZXCVBNMCV | MNBVCXZNB | QWERTYUIO | WERTYUIOP |

|-----------|-----------|---------------------------------|-----------|-----------|-----------|-----------|-------------|

| 1 | 001 | December, 15 2013 00:00:00+0000 | 2 | 9 | 1 | 4 | 0.9 |

| 2 | 002 | December, 14 2013 00:00:00+0000 | 3 | 8 | 2 | 3 | 0.81 |

| 3 | 003 | December, 13 2013 00:00:00+0000 | 4 | 7 | 3 | 2 | 0.729 |

| 4 | 004 | December, 12 2013 00:00:00+0000 | 5 | 6 | 4 | 1 | 0.6561 |

| 5 | 005 | December, 11 2013 00:00:00+0000 | 6 | 5 | 5 | 0 | 0.59049 |

| 6 | 006 | December, 10 2013 00:00:00+0000 | 7 | 4 | 6 | 1 | 0.531441 |

| 7 | 007 | December, 09 2013 00:00:00+0000 | 8 | 3 | 7 | 2 | 0.4782969 |

| 8 | 008 | December, 08 2013 00:00:00+0000 | 9 | 2 | 8 | 3 | 0.43046721 |

| 9 | 009 | December, 07 2013 00:00:00+0000 | 10 | 1 | 9 | 4 | 0.387420489 |

Query 2 ：

DECLARE

min_sql CLOB;

max_sql CLOB;

min_rec tbl%ROWTYPE;

max_rec tbl%ROWTYPE;

BEGIN

-- Generate the SQL to find the minimums

SELECT 'SELECT '

|| LISTAGG( 'MIN("' || COLUMN_NAME || '")', ',' )

WITHIN GROUP ( ORDER BY COLUMN_ID )

|| ' FROM TBL'

INTO min_sql

FROM USER_TAB_COLUMNS

WHERE TABLE_NAME = 'TBL';

-- Generate the SQL to find the maximums

SELECT 'SELECT '

|| LISTAGG( 'MAX("' || COLUMN_NAME || '")', ',' )

WITHIN GROUP ( ORDER BY COLUMN_ID )

|| ' FROM TBL'

INTO max_sql

FROM USER_TAB_COLUMNS

WHERE TABLE_NAME = 'TBL';

-- Execute the SQL to find the minimums and put the

-- results into a %ROWTYPE record (to ensure dataypes

-- match).

EXECUTE IMMEDIATE min_sql INTO min_rec;

-- Execute the SQL to find the maximums and put the

-- results into a %ROWTYPE record (to ensure dataypes

-- match).

EXECUTE IMMEDIATE max_sql INTO max_rec;

-- Do something with the minimums

INSERT INTO test VALUES min_rec;

-- Do something with the maximums

INSERT INTO test VALUES max_rec;

END;

Query 3 ：

SELECT * FROM test

Results ：

| ASDFGHJKL | POIUYTREW | OIUYTREWQ | SDFGHJKLA | ZXCVBNMCV | MNBVCXZNB | QWERTYUIO | WERTYUIOP |

|-----------|-----------|---------------------------------|-----------|-----------|-----------|-----------|-------------|

| 1 | 001 | December, 07 2013 00:00:00+0000 | 2 | 1 | 1 | 0 | 0.387420489 |

| 9 | 009 | December, 15 2013 00:00:00+0000 | 10 | 9 | 9 | 4 | 0.9 |