為範圍 sql 之間的值創建列

我想要實現的是基於範圍之間的 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 中執行此操作？

我想出了我的問題的答案。以下工作正常

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 $$, 這將用於格式。

引用自：https://dba.stackexchange.com/questions/244112