sv9avCkP

1DROP TABLE IF EXISTS OfferedProducts;
2CREATE TABLE OfferedProducts (
3    ProductId int
4        primary key
5        references Product (ProductID),
6    pair1 int references Product (ProductID),
7    pair2 int references Product (ProductID),
8    pair3 int references Product (ProductID)
9);
10 
11DROP PROCEDURE IF EXISTS CreateOfferedProducts;
12
13DELIMITER // 
14CREATE PROCEDURE CreateOfferedProducts(singleProductId int)
15BEGIN
16	DECLARE cursorDone, pid1, pid2, lastPid, numRows INT DEFAULT 0;
17    DECLARE myCursor CURSOR FOR
18		SELECT sod1.ProductID AS ProductID, sod2.ProductID as bought_with
19		FROM SalesOrderDetail sod1
20			INNER JOIN SalesOrderDetail sod2 ON sod1.SalesOrderID = sod2.SalesOrderID AND
21						sod1.ProductID != sod2.ProductID
22		GROUP BY sod1.ProductID, sod2.ProductID
23		ORDER BY sod1.ProductID, count(1) DESC;
24	DECLARE CONTINUE HANDLER FOR NOT FOUND SET cursorDone = 1;
25    
26    IF singleProductId > 0 THEN
27		DELETE FROM OfferedProducts WHERE ProductId = singleProductId;
28	ELSE
29		DELETE FROM OfferedProducts;
30	END IF;
31    
32    OPEN myCursor;
33    FETCH myCursor INTO pid1, pid2;
34    read_loop: WHILE cursorDone = 0 DO
35		IF singleProductId>0 AND singleProductId<>pid1 THEN
36			FETCH myCursor INTO pid1, pid2;
37			ITERATE read_loop; -- vs LEAVE
38        END IF;
39        
40        IF lastPid<>pid1 THEN
41			INSERT INTO OfferedProducts VALUES (pid1, pid2, NULL, NULL);
42            SET numRows = 0;
43        END IF;
44        IF numRows = 1 THEN
45			UPDATE OfferedProducts SET pair2 = pid2 WHERE ProductId = pid1;
46        END IF;
47        IF numRows = 2 THEN
48			UPDATE OfferedProducts SET pair3 = pid2 WHERE ProductId = pid1;
49        END IF;
50        
51        SET numRows = numRows + 1;
52        SET lastPid = pid1;
53        
54        FETCH myCursor INTO pid1, pid2;
55    END WHILE;
56    CLOSE myCursor;
57END
58//
59DELIMITER ;
60
61CALL CreateOfferedProducts(0);
62SELECT * FROM offeredproducts;