[20170703]pivot与order by字段.txt-优快云博客

本文介绍了一个Oracle SQL查询示例，展示了如何使用Pivot操作来汇总统计数据，并按特定字段进行排序。示例中使用了v$segment_statistics视图，并针对多个统计指标进行了聚合。

[20170703]pivot与order by字段.txt

--//11G开始支持pivot,上午写一个脚本,来自链接http://blog.itpub.net/267265/viewspace-1063539/
--//做了一点点改写.
select * from
(
WITH pivot_stats
     AS (SELECT owner,
                object_name,
                statistic_name,
                VALUE
           FROM v$segment_statistics)
SELECT *
FROM pivot_stats PIVOT (SUM (VALUE)
                   FOR statistic_name
                  IN ('logical reads',
                       'buffer busy waits',
                       'gc buffer busy',
                       'db block changes',
                       'physical reads',
                       'physical writes',
                       'physical read requests',
                       'physical write requests',
                       'physical reads direct',
                       'physical writes direct',
                       'optimized physical reads',
                       'gc cr blocks received',
                       'gc current blocks received',
                       'ITL waits',
                       'row lock waits',
                       'space used',
                       'space allocated',
                       'segment scans'))
where owner =upper('&&1') order by &&2
)
where rownum<=20;

--//我发现这样的输出字段命名如下:
SCOTT@book> @ &r/seg_stat1 scott 3
old 30: where owner =upper('&&1') order by &&2
new 30: where owner =upper('scott') order by 3

OWNER OBJECT_NAME          'logical reads' 'buffer busy waits' 'gc buffer busy' 'db block changes' 'physical reads' 'physical writes' 'physical read requests' 'physical write requests' 'physical reads direct' 'physical writes direct'
------ -------------------- --------------- ------------------- ---------------- ------------------ ---------------- ----------------- ------------------------ ------------------------- ----------------------- ------------------------
'optimized physical reads' 'gc cr blocks received' 'gc current blocks received' 'ITL waits' 'row lock waits' 'space used' 'space allocated' 'segment scans'
-------------------------- ----------------------- ---------------------------- ----------- ---------------- ------------ ----------------- ---------------
SCOTT DEPT                              32                   0                0                  0                6                 0                        3                         0                       0                        0
                         0                       0                            0           0                0            0                 0               0

SCOTT PK_DEPT 48 0 0 0 2 0 2 0 0 0
0 0 0 0 0 0 0 0

--//我这里指定第3个字段排序.如何使用字段名呢?尝试才发现使用单引号写才ok.做一个记录:

select * from
(
WITH pivot_stats
     AS (SELECT owner,
                object_name,
                statistic_name,
                VALUE
           FROM v$segment_statistics)
SELECT *
FROM pivot_stats PIVOT (SUM (VALUE)
                   FOR statistic_name
                  IN ('logical reads',
                       'buffer busy waits',
                       'gc buffer busy',
                       'db block changes',
                       'physical reads',
                       'physical writes',
                       'physical read requests',
                       'physical write requests',
                       'physical reads direct',
                       'physical writes direct',
                       'optimized physical reads',
                       'gc cr blocks received',
                       'gc current blocks received',
                       'ITL waits',
                       'row lock waits',
                       'space used',
                       'space allocated',
                       'segment scans'))
where owner =upper('&&1') order by 'logical reads'
)
where rownum<=20;