枢轴排入列Firebird 2.1

发布于 2025-01-26 21:43:40 字数 377 浏览 4 评论 0原文

我有一张桌子，里面有几公里大约100公里公里表

我需要将行旋转成这样的列。枢轴行列为列

列的列数固定在行中= 10公里，
可以重复并具有10公里差距，示例：263、263、264、265、270 ..

我认为使用“递归”，但网上几乎没有例子（如果您能帮助我解决问题，我将非常感激！

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

掐死时间 2025-02-02 21:43:40

正如我在评论中提到的那样，这可能是您的演示层中更好的解决方案，而不是通过查询来解决。我不确定是否可以使用（递归）CTE解决此问题，但是我可以使用Execute Block在PSQL中为您提供解决方案（这也可以以存储过程的形式完成）。

该解决方案将行映射到列，并在填充所有列后启动新行。

execute block
returns (km_1 smallint, km_2 smallint, km_3 smallint, km_4 smallint, km_5 smallint,
  km_6 smallint, km_7 smallint, km_8 smallint, km_9 smallint, km_10 smallint)
as
  declare column_count smallint = 1;
  declare km smallint;
begin
  for select km from kms order by km into km do
  begin
    -- map value to column
    if (column_count = 1) then km_1 = km;
    else if (column_count = 2) then km_2 = km;
    else if (column_count = 3) then km_3 = km;
    else if (column_count = 4) then km_4 = km;
    else if (column_count = 5) then km_5 = km;
    else if (column_count = 6) then km_6 = km;
    else if (column_count = 7) then km_7 = km;
    else if (column_count = 8) then km_8 = km;
    else if (column_count = 9) then km_9 = km;
    else if (column_count = 10) then km_10 = km;
    
    if (column_count < 10) then
      column_count = column_count + 1;
    else
    begin
      -- row complete, output
      column_count = 1;
      suspend;
    end
  end
  if (column_count > 1) then
  begin
    -- partial row
    while (column_count <= 10) do
    begin
      -- null remaining columns
      if (column_count = 2) then km_2 = null;
      else if (column_count = 3) then km_3 = null;
      else if (column_count = 4) then km_4 = null;
      else if (column_count = 5) then km_5 = null;
      else if (column_count = 6) then km_6 = null;
      else if (column_count = 7) then km_7 = null;
      else if (column_count = 8) then km_8 = null;
      else if (column_count = 9) then km_9 = null;
      else if (column_count = 10) then km_10 = null;
      column_count = column_count + 1;
    end
    -- output partial row
    suspend;
  end
end

在Firebird 4.0中，您可以通过窗口功能获得相同的结果。

with col_row as (
  select 
    km, 
    mod(row_number() over(order by km) - 1, 10) as column_group, 
    (row_number() over(order by km) - 1) / 10 row_group
  from kms
)
select 
  min(km) filter (where column_group = 0),
  min(km) filter (where column_group = 1),
  min(km) filter (where column_group = 2),
  min(km) filter (where column_group = 3),
  min(km) filter (where column_group = 4),
  min(km) filter (where column_group = 5),
  min(km) filter (where column_group = 7),
  min(km) filter (where column_group = 8),
  min(km) filter (where column_group = 9)
from col_row
group by row_group

对于firebird 3.0，替换min（km）过滤器（column_group = n）用min（decode（column_group，n，km））>或column_group = n然后km）（其中n为0，1，..，9）。

As I mentioned in the comments, this is probably something better solved in your presentation layer, instead of through a query. I'm not sure if this can be solved with a (recursive) CTE, but I can offer you a solution in PSQL using EXECUTE BLOCK (this can also be done in the form of a stored procedure).

This solution maps rows to columns, and starts a new row when all columns have been filled.

execute block
returns (km_1 smallint, km_2 smallint, km_3 smallint, km_4 smallint, km_5 smallint,
  km_6 smallint, km_7 smallint, km_8 smallint, km_9 smallint, km_10 smallint)
as
  declare column_count smallint = 1;
  declare km smallint;
begin
  for select km from kms order by km into km do
  begin
    -- map value to column
    if (column_count = 1) then km_1 = km;
    else if (column_count = 2) then km_2 = km;
    else if (column_count = 3) then km_3 = km;
    else if (column_count = 4) then km_4 = km;
    else if (column_count = 5) then km_5 = km;
    else if (column_count = 6) then km_6 = km;
    else if (column_count = 7) then km_7 = km;
    else if (column_count = 8) then km_8 = km;
    else if (column_count = 9) then km_9 = km;
    else if (column_count = 10) then km_10 = km;
    
    if (column_count < 10) then
      column_count = column_count + 1;
    else
    begin
      -- row complete, output
      column_count = 1;
      suspend;
    end
  end
  if (column_count > 1) then
  begin
    -- partial row
    while (column_count <= 10) do
    begin
      -- null remaining columns
      if (column_count = 2) then km_2 = null;
      else if (column_count = 3) then km_3 = null;
      else if (column_count = 4) then km_4 = null;
      else if (column_count = 5) then km_5 = null;
      else if (column_count = 6) then km_6 = null;
      else if (column_count = 7) then km_7 = null;
      else if (column_count = 8) then km_8 = null;
      else if (column_count = 9) then km_9 = null;
      else if (column_count = 10) then km_10 = null;
      column_count = column_count + 1;
    end
    -- output partial row
    suspend;
  end
end

In Firebird 4.0 you can achieve the same result with window functions.

with col_row as (
  select 
    km, 
    mod(row_number() over(order by km) - 1, 10) as column_group, 
    (row_number() over(order by km) - 1) / 10 row_group
  from kms
)
select 
  min(km) filter (where column_group = 0),
  min(km) filter (where column_group = 1),
  min(km) filter (where column_group = 2),
  min(km) filter (where column_group = 3),
  min(km) filter (where column_group = 4),
  min(km) filter (where column_group = 5),
  min(km) filter (where column_group = 7),
  min(km) filter (where column_group = 8),
  min(km) filter (where column_group = 9)
from col_row
group by row_group

For Firebird 3.0, replace min(km) filter (where column_group = n) with min(decode(column_group, n, km)) or min(case when column_group = n then km) (where n is 0, 1, .., 9).

回复收藏 0 原文

~没有更多了~