如何将单行与sql中的列中的数字相乘 [英] How to multiply a single row with a number from column in sql

问题描述

就我而言，有订单和订单头寸.每个订单头寸都有一个数量.例如:

In my case there are orders and order positions. Each order position has a quantity. For example:

但现在每个位置元素"都需要一行.这是我想要的输出:

But now I need a single row for each "position element". This is the output I want:

我的想法是用rank()/over() 来得到增量的数字，但是不知道如何用数量作为乘数.

My idea is to use rank() / over() to get the incremental number, but I don't know how to use the quantity as multiplicator.

是否有使用单个列作为行乘法器"的智能解决方案?在我的情况下，sql 函数或循环是不可能的，只是普通的 sql :)

Is there a smart solution to use a single colum as "row multiplicator"? A sql function or a loop is not possible in my case, just plain sql :)

谢谢！:)

通过来自 gvee 的查询，我能够为我的问题创建一个解决方案:

With the query from gvee I was able to create a solution for my problem:

Select 
BelPosId as OrderPositionId, 
Artikelnummer as ProductId,  
Bezeichnung1 as ProductName,
Menge as Quantity,
NumberTable.number+1 as ElementId
FROM KHKVKBelegePositionen 

INNER JOIN 

(SELECT (a.number * 256) + b.number As number
FROM     (
        SELECT number
        FROM   master..spt_values
        WHERE  type = 'P'
        AND    number <= 255
       ) As a
 CROSS
  JOIN (
        SELECT number
        FROM   master..spt_values
        WHERE  type = 'P'
        AND    number <= 255
       ) As b) NumberTable

ON

NumberTable.number < Menge

诀窍是使用小于运算符将数量"列内连接到数字表中的数字"列以模拟乘法器":

The trick was to inner join the column "quantity" to the "number" column from the numbers table with the less than operator to simulate a "multiplicator":

推荐答案

您需要加入数字表！

CREATE TABLE dbo.numbers (
   number int NOT NULL
)

ALTER TABLE dbo.numbers
ADD
   CONSTRAINT pk_numbers PRIMARY KEY CLUSTERED (number)
     WITH FILLFACTOR = 100
GO

INSERT INTO dbo.numbers (number)
SELECT (a.number * 256) + b.number As number
FROM     (
        SELECT number
        FROM   master..spt_values
        WHERE  type = 'P'
        AND    number <= 255
       ) As a
 CROSS
  JOIN (
        SELECT number
        FROM   master..spt_values
        WHERE  type = 'P'
        AND    number <= 255
       ) As b

这是我保存最新脚本的地方:http://gvee.co.uk/files/sql/dbo.numbers%20&%20dbo.calendar.sql

Here's where I keep my latest script: http://gvee.co.uk/files/sql/dbo.numbers%20&%20dbo.calendar.sql

一旦你有了它，你就可以执行一个简单的连接:

Once you have this in place you perform a simple join:

SELECT KHKVKBelegePositionen.BelPosId As OrderPositionId
     , KHKVKBelegePositionen.Artikelnummer As ProductId
     , KHKVKBelegePositionen.Bezeichung1 As ProductName
     , KHKVKBelegePositionen.Menge As Quantity
     , numbers.number As ElemendId
FROM   KHKVKBelegePositionen
 INNER
  JOIN dbo.numbers
    ON numbers.number BETWEEN 1 AND KHKVKBelegePositionen.Menge

这篇关于如何将单行与sql中的列中的数字相乘的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！