[20170703]pivot与order by字段.txt

简介: [20170703]pivot与order by字段.txt --//11G开始支持pivot,上午写一个脚本,来自链接http://blog.itpub.net/267265/viewspace-1063539/ --//做了一点点改写.

[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;

目录
相关文章
|
10月前
ROW_NUMBER() OVER()函数用法详解 (分组排序 例子多)
ROW_NUMBER() OVER()函数用法详解 (分组排序 例子多)
146 0
|
SQL Serverless 数据库
【count(列名)、count(1)和 count(星号)有什么区别】
【count(列名)、count(1)和 count(星号)有什么区别】
238 0
[20170114]12c varchar2类型直方图.txt
[20170114]12c varchar2类型直方图.txt --我曾经提到慎用nvarchar2数据类型,链接:http://blog.itpub.net/267265/viewspace-2120925/ --我那里提到数据类型nvarchar2类型,因为1...
883 0
|
SQL 数据库
除了会排序,你对ORDER BY的用法可能一无所知!(一)
小伙伴们在进行SQL排序时,都能很自然的使用到ORDER BY。不管是默认ASC的升序,还是DESC降序,几乎都是信手拈来。 今天给大家分享一些你可能不知道的ORDER BY用法。
除了会排序,你对ORDER BY的用法可能一无所知!(一)
|
XML SQL 数据库
除了会排序,你对ORDER BY的用法可能一无所知!(二)
小伙伴们在进行SQL排序时,都能很自然的使用到ORDER BY。不管是默认ASC的升序,还是DESC降序,几乎都是信手拈来。 今天给大家分享一些你可能不知道的ORDER BY用法。
除了会排序,你对ORDER BY的用法可能一无所知!(二)
|
关系型数据库 数据库
[20180625]10g下查询条件rownum = 0.txt
[20180625]10g下查询条件rownum = 0.txt SCOTT@test> @ &r/ver1 PORT_STRING                    VERSION        BANNER --------------------...
1182 0
|
Java 关系型数据库 Linux
|
Java 关系型数据库 Linux
|
Java 关系型数据库 Linux