主页 / dba / 问题 / 244112
Accepted
Christopher Karsten
Asked: 2019-07-31 01:26:32 +0800 CST

为范围 sql 之间的值创建列

  • 772

我想要实现的是基于范围之间的 num_extents 创建一个新列，并且只打印下面的表名。

这就是我所拥有的 [table1]：

在此处输入图像描述

这就是我想要的[table2]：

在此处输入图像描述

我使用了一个 case 语句，它允许使用以下内容获取范围之间的值：

select sum(case when num_of_extents between 0 and 9 then 1 else 0 end) as one,
       sum(case when num_of_extents between 10 and 19 then 1 else 0 end) as two,
       sum(case when num_of_extents between 20 and 29 then 1 else 0 end) as three,
       sum(case when num_of_extents between 30 and 39 then 1 else 0 end) as four,
       sum(case when num_of_extents between 40 and 49 then 1 else 0 end) as five,
       sum(case when num_of_extents >= 50 then 1 else 0 end) as six
from (
select
a.tabname,
count(*) num_of_extents,
sum( pe_size ) total_size_pg,
round(sum( pe_size*4),2) total_size_kb,
round(sum( pe_size*4/1024 ),2) total_size_mb,
round(sum( pe_size *4/1024/1024),2) total_size_gb
from systabnames a, sysptnext b, sysdatabases c
where a.partnum = b.pe_partnum
and a.dbsname = c.name
and a.dbsname = 'system'
-- a.tabname not like ' %'
group by 1
order by 3 desc, 4 desc
) a

上面的输出：

one    2
two    1
three  1
four   5
five   0
six    2

请忽略使用的列的命名，但我不想在范围之间求和，而是想打印出表的名称，而不是像 [table2] 中那样。如何更改我的 sql case 语句以在 informix 中执行此操作？

informix

1 个回答

  1. Best Answer

    我想出了我的问题的答案。以下工作正常

    select
case when num_of_extents between 0 and 9 then a.tabname end as one,
case when num_of_extents between 10 and 19 then a.tabname end two,
case when num_of_extents between 20 and 29 then a.tabname end three,
case when num_of_extents between 30 and 39 then a.tabname end four,
case when num_of_extents between 40 and 49 then a.tabname end five,
case when num_of_extents >= 50 then a.tabname end six
from (
select
a.tabname,
count(*) num_of_extents,
sum( pe_size ) total_size_pg,
round(sum( pe_size*4),2) total_size_kb,
round(sum( pe_size*4/1024 ),2) total_size_mb,
round(sum( pe_size *4/1024/1024),2) total_size_gb
from systabnames a, sysptnext b, sysdatabases c
where a.partnum = b.pe_partnum
and a.dbsname = c.name
and a.dbsname = 'system'
-- a.tabname not like ' %'
group by 1
order by 3 desc, 4 desc
) a group by 1,2,3,4,5,6

    dbaccess 中的输出如下所示：

    one
two    bc_storage_idx2
three
four
five
six

one
two
three
four   tock_mvmt
five
six

one
two
three  rd_stats
four
five
six

one
two    tock_mvmt_idx1
three
four
five
six
...

    请注意，我没有使用与上面相同的表格数据。以上只是如何在 dbaccess 中呈现信息的示例。由于列格式的数量，它将无法根据需要显示，但可以在提取时输出到电子表格时进行过滤。

    如果我在 AIX 中弄乱我的环境变量，下面的格式会起作用：

           select
       (case when num_of_extents between 0 and 9 then a.tabname end)[2,20] as one,
       case when num_of_extents between 10 and 19 then a.tabname[2,20] end two,
       case when num_of_extents between 20 and 29 then a.tabname[2,20] end three,
       case when num_of_extents between 30 and 39 then a.tabname[2,20] end four,
       case when num_of_extents between 40 and 49 then a.tabname[2,20] end five,
       case when num_of_extents >= 50 then a.tabname[2,20] end six
from (
select
a.tabname,
count(*) num_of_extents,
sum( pe_size ) total_size_pg,
round(sum( pe_size*4),2) total_size_kb,
round(sum( pe_size*4/1024 ),2) total_size_mb,
round(sum( pe_size *4/1024/1024),2) total_size_gb
from systabnames a, sysptnext b, sysdatabases c
where a.partnum = b.pe_partnum
and a.dbsname = c.name
and a.dbsname = 'system'
-- a.tabname not like ' %'
group by 1
order by 3 desc, 4 desc
) a group by 1,2,3,4,5,6

    我不知道如何使该部分在 stackexchange 上以粗体显示，但请参阅 [2,20]，它将用于格式。

    • 1

相关问题