我的数据库中的一个表包含带有申请编号和其他相关信息的行.我正在尝试创建第二个表(使用 INSERT INTO 语句填充),该表复制这些行并根据 QuantityOrdered 列中的值添加一个系列值.>
例如第一个表格如下所示:
+-------------+---------+|征用 |数量 |+------------+------------+|10001_01_AD |4 |+------------+------------+我希望输出如下:
+-------------+----------+----------+|征用 |数量 |系列 |+-------------+----------+---------+|10001_01_AD |4 |1 ||10001_01_AD |4 |2 ||10001_01_AD |4 |3 ||10001_01_AD |4 |4 |+-------------+----------+---------+我一直在尝试使用 Row_Number() 对值进行排序,但它根据 Requisition 值的实例而不是 Quantity 值对行进行编号.
解决方案非递归方式:
SELECT *从选项卡 t交叉申请(选择 nFROM (SELECT ROW_NUMBER() OVER(ORDER BY 1/0) AS nFROM master..spt_values s1) AS subWHERE sub.n <= t.Quantity) AS s2(Series);db<>小提琴演示
One of my tables in my database contains rows with requisition numbers and other related info. I am trying to create a second table (populated with an INSERT INTO statement) that duplicates these rows and adds a series value based on the value in the QuantityOrdered column.
For example, the first table is shown below:
+-------------+----------+ | Requisition | Quantity | +-------------+----------+ | 10001_01_AD | 4 | +-------------+----------+and I would like the output to be as follows:
+-------------+----------+----------+ | Requisition | Quantity | Series | +-------------+----------+----------+ | 10001_01_AD | 4 | 1 | | 10001_01_AD | 4 | 2 | | 10001_01_AD | 4 | 3 | | 10001_01_AD | 4 | 4 | +-------------+----------+----------+I've been attempting to use Row_Number() to sequence the values but it's numbering rows based on instances of Requisition values, not based on the Quantity value.
解决方案Non-recursive way:
SELECT * FROM tab t CROSS APPLY (SELECT n FROM (SELECT ROW_NUMBER() OVER(ORDER BY 1/0) AS n FROM master..spt_values s1) AS sub WHERE sub.n <= t.Quantity) AS s2(Series);db<>fiddle demo
更多推荐
根据列值生成行
发布评论