SQL Server:改进过程而不使用游标

I am looking for a way to write the below procedure without using a CURSOR or just to find a better performing query.

我正在寻找一种方法来编写下面的过程，而不使用游标或仅仅是为了找到一个性能更好的查询。

CREATE TABLE #OrderTransaction (OrderTransactionId int, ProductId int, Quantity int);
CREATE TABLE #Product (ProductId int, MediaTypeId int);
CREATE TABLE #OrderDelivery (OrderTransactionId int, MediaTypeId int);

INSERT INTO #Product (ProductId, MediaTypeId) VALUES (1,1);
INSERT INTO #Product (ProductId, MediaTypeId) VALUES (2,2);
INSERT INTO #OrderTransaction(OrderTransactionId, ProductId, Quantity) VALUES (1,1,1);
INSERT INTO #OrderTransaction(OrderTransactionId, ProductId, Quantity) VALUES (2,2,6);

DECLARE @OrderTransactionId int, @MediaTypeId int, @Quantity int; 

DECLARE ordertran CURSOR FAST_FORWARD FOR 
    SELECT  OT.OrderTransactionId, P.MediaTypeId, OT.Quantity
    FROM    #OrderTransaction OT WITH (NOLOCK)
        INNER JOIN  #Product P WITH (NOLOCK)
            ON OT.ProductId = P.ProductId


OPEN ordertran;
FETCH NEXT FROM ordertran INTO @OrderTransactionId, @MediaTypeId, @Quantity;

WHILE @@FETCH_STATUS = 0 
BEGIN
    WHILE @Quantity > 0 
    BEGIN
        INSERT INTO #OrderDelivery ([OrderTransactionId], [MediaTypeId])
        VALUES (@OrderTransactionId, @MediaTypeId)

        SELECT @Quantity = @Quantity - 1;
    END 

    FETCH NEXT FROM ordertran INTO @OrderTransactionId, @MediaTypeId, @Quantity;
END 

CLOSE ordertran;
DEALLOCATE ordertran;


SELECT  *   FROM    #OrderTransaction 
SELECT  *   FROM    #Product
SELECT  *   FROM    #OrderDelivery

DROP TABLE #OrderTransaction;
DROP TABLE #Product;
DROP TABLE #OrderDelivery;

5 个解决方案

#1

Begin with a Numbers table that is large enough to handle the maximum order amount:

以一个大到足以处理最大订单量的数字表开始:

CREATE TABLE Numbers (
   Num int NOT NULL PRIMARY KEY CLUSTERED
)

-- SQL 2000 version
INSERT Numbers VALUES (1)
SET NOCOUNT ON
GO
INSERT Numbers (Num) SELECT Num + (SELECT Max(Num) FROM Numbers) FROM Numbers
GO 15

-- SQL 2005 and up version
WITH
   L0 AS (SELECT c = 1 UNION ALL SELECT 1),
   L1 AS (SELECT c = 1 FROM L0 A, L0 B),
   L2 AS (SELECT c = 1 FROM L1 A, L1 B),
   L3 AS (SELECT c = 1 FROM L2 A, L2 B),
   L4 AS (SELECT c = 1 FROM L3 A, L3 B),
   L5 AS (SELECT c = 1 FROM L4 A, L4 B),
   N AS (SELECT Num = ROW_NUMBER() OVER (ORDER BY c) FROM L5)
INSERT Numbers(Num)
SELECT Num FROM N
WHERE Num <= 32768;

Then, immediately after your INSERT statements:

然后，在插入语句之后:

INSERT #OrderDelivery (OrderTransactionId, MediaTypeId)
SELECT
   OT.OrderTransactionId,
   P.MediaTypeId
FROM
   #OrderTransaction OT
   INNER JOIN #Product P ON OT.ProductId = P.ProductId
   INNER JOIN Numbers N ON N.Num BETWEEN 1 AND OT.Quantity

That should do it!

应该做到!

If for some reason you have qualms about putting a permanent Numbers table in your database (which I don't understand as it is a wonderful tool), then you can simply join to the CTE given instead of the table itself. In SQL 2000 you can create a temp table and use a loop, but I would advise against this strongly.

如果出于某种原因，您对在数据库中放置一个永久性数字表(我不理解，因为它是一个很好的工具)感到不安，那么您可以简单地加入给定的CTE，而不是表本身。在SQL 2000中，您可以创建一个临时表并使用一个循环，但是我强烈建议不要这样做。

A Numbers table is highly recommended. There is no concern about some future change breaking it (the set of whole numbers won't change any time soon). Some people use a Numbers table with a million numbers in it, which is only around 4MB of storage.

强烈推荐使用数字表。不用担心将来会有什么变化破坏它(整组数字不会很快改变)。有些人使用一个包含100万个数字的数字表，这个表只有大约4MB的存储空间。

To answer critics of the Numbers table: if the database design uses a numbers table, then that table won't need to change. It is like any other table in the database and can be relied on. You don't worry too much about queries against an Orders table failing because some day the table might not exist, so I don't see why there would be any similar concern about another table that is required and depended on.

要回答对数字表的批评:如果数据库设计使用数字表，那么该表将不需要更改。它与数据库中的任何其他表一样，都可以依赖。您不必过分担心对Orders表的查询，因为有一天表可能不存在，所以我不明白为什么会有类似的担心，因为另一个表是必需的，并且依赖于它。

UPDATE

更新

In the time since writing this answer I have learned about the master.dbo.spt_values table which has a number column. When queried with where type='P' you get 0 - 255 in SQL 2000 and 0 - 8191 in SQL 2005 and up. (There are also potentially useful low and high columns.) You can cross join this table to itself a couple of times if necessary to get, even in SQL 2000, a bunch of rows very quickly.

写完这个答案后，我了解了master.dbo。spt_values表，它有一个数字列。当查询类型='P'时，SQL 2000中的值为0 - 255,SQL 2005中为0 - 8191。(也有可能有用的低柱和高柱。)如果需要的话，您可以多次将该表与自身交叉连接，即使是在SQL 2000中，也可以很快地获得一堆行。

#2

The trick is to introduce a table of values (named, in the example below, MyTableOfIntegers) which contains all the integer values between 1 and (at least) some value (in the case at hand, that would be the biggest possible Quantity value from OrderTransaction table).

诀窍是引入一个值表(在下面的示例中命名为MyTableOfIntegers)，它包含1到(至少)某个值之间的所有整数值(在本文中，这将是OrderTransaction表中可能的最大数量值)。

INSERT INTO #OrderDelivery ([OrderTransactionId], [MediaTypeId])
  SELECT  OT.OrderTransactionId, P.MediaTypeId
  FROM  #OrderTransaction OT WITH (NOLOCK)
  INNER JOIN  #Product P WITH (NOLOCK)
    ON OT.ProductId = P.ProductId
  JOIN MyTableOfIntegers I ON I.Num <= OT.Quantity
  --WHERE some optional conditions 

Essentially the extra JOIN on MyTableOfIntegers, produces as many duplicate rows as OT.Quantity, and that seems to be what the purpose of the cursor was: to insert that many duplicated rows in the OrderDelivery table.

从本质上说，MyTableOfIntegers上的额外连接产生的行数和OT一样多。数量，这似乎就是游标的目的:在OrderDelivery表中插入那么多重复的行。

I didn't check the rest of the logic with the temporary tables and all (I'm assuming these are temp tables for the purpose of checking the logic rather than being part of the process proper), but it seems that the above is the type of construct needed to express the needed logic in declarative fashion only, without any cursor or even any loop.

我没有检查其余的逻辑与临时表和所有(我假设这些都是临时表为目的的检查逻辑而不是过程的一部分),但似乎上述类型的构建需要表达所需的逻辑以声明的方式,没有光标,甚至任何循环。

#3

Here is a slight variation on the previous answers, that avoids a permanent numbers table (though I am not sure why people are so afraid of this construct), and allows you to build a run-time CTE that contains exactly the set of numbers you'll need to perform the correct number of inserts (by checking for the highest quantity). I commented out the CROSS JOIN in the initial CTE, but you can use it if your quantity for any given order can exceed the number of rows in sys.columns. Hopefully that is an unlikely scenario. Note that this is for SQL Server 2005 and up ... it is always useful to let us know which specific version(s) you are targeting.

较前一个答案,这是一个轻微的变化,避免永久性数据表(虽然我不明白为什么人们如此害怕这种构造),并允许您构建一个运行时CTE包含准确的数量你需要执行正确的插入(通过检查最高数量)。我注释掉了初始CTE中的交叉连接，但是如果任意给定订单的数量可以超过sys.columns中的行数，则可以使用它。希望这是一个不太可能的情况。注意，这是针对SQL Server 2005和up…让我们知道您要针对哪个特定的版本总是很有用的。

DECLARE @numsNeeded INT;

SELECT @numsNeeded = MAX(Quantity) FROM #OrderTransaction;

WITH n AS 
(
    SELECT TOP (@numsNeeded) i = ROW_NUMBER()
    OVER (ORDER BY c.[object_id])
    FROM sys.columns AS c  --CROSS JOIN sys.columns AS c2
)
INSERT #OrderDelivery
(
    OrderTransactionID,
    MediaTypeID
)
SELECT t.OrderTransactionID, p.MediaTypeID
    FROM #OrderTransaction AS t
    INNER JOIN #Product AS p
    ON t.ProductID = p.ProductID
    INNER JOIN n
    ON n.i <= t.Quantity;

#4

INSERT INTO #OrderDelivery ([OrderTransactionId], [MediaTypeId])
SELECT  OT.OrderTransactionId, P.MediaTypeId,
FROM    #OrderTransaction OT
INNER JOIN  #Product P
ON OT.ProductId = P.ProductId
WHERE OT.Quantity > 0

I feel like i'm misreading the logic here, but isn't that the equivelant?

我觉得我误解了这里的逻辑，但这不是一种模棱两可的说法吗?

#5

This still uses a loop but it has gotten rid of the cursor. Short of creating a table of numbers to join on, I think this is the best answer.

这仍然使用一个循环，但是它已经删除了游标。除了创建一个要加入的数字表之外，我认为这是最好的答案。

DECLARE @Count AS INTEGER

SET @Count = 1

WHILE (1 = 1)
BEGIN

    INSERT INTO #OrderDelivery ([OrderTransactionId], [MediaTypeId]) 
    SELECT  OT.OrderTransactionId, P.MediaTypeId, OT.Quantity 
    FROM    #OrderTransaction OT WITH (NOLOCK) 
        INNER JOIN  #Product P WITH (NOLOCK) 
            ON OT.ProductId = P.ProductId 
    WHERE OT.Quantity > @Count

    IF @@ROWCOUNT = 0 
        BREAK

    SET @COUNT = @COUNT + 1
END