创建基于将范围转换为行的 sql 视图 [英] Create a sql view based converting ranges into rows

问题描述

我有一个这样结构的表格

I have a table structured like so

ColA|ColB|LowRange|HighRange
----------------------------
  1   A     1         5

我想创建一个视图，以下列格式提供数据

I would like to create a view that will make the data available in the following format

ColA|ColB|RangeNumber
----------------------
  1   A       1
  1   A       2 
  1   A       3
  1   A       4
  1   A       5

我对视图不够熟悉，所以我需要一些指导.

I'm not familiar enough with views so I need some direction.

谢谢

推荐答案

您可以使用递归 CTE 来完成此操作

You can accomplish this using a recursive CTE

CREATE TABLE ranges (
    ColA int,
    ColB char,
    LowRange int,
    HighRange int,
);

INSERT INTO ranges
VALUES (1, 'A', 1, 5),
(2, 'B', 5, 10);
GO

CREATE VIEW range_view
AS
WITH each AS
(
    SELECT ColA, ColB, LowRange AS n, HighRange
      FROM ranges
    UNION ALL
    SELECT ColA, ColB, n + 1, HighRange
      FROM each
     WHERE n + 1 <= HighRange
)
SELECT ColA, ColB, n
FROM each
GO

SELECT * FROM range_view
DROP VIEW range_view
DROP TABLE ranges;

这篇关于创建基于将范围转换为行的 sql 视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！