我试图得到一个零件矩阵，以找出哪些零件经常一起销售。也许有人有办法帮帮我。
我拥有的表集:
- 表是包含所有零件编号的列表，仅包含一列"Gin"。
- 表是一个消费表，包含2列&;Order number&;和"Gin">
目标是最后的一个矩阵，它在列和行中包含相同的产品编号，并显示哪个部件与哪个部件一起发货以及发货的频率。它需要是动态的，因为零件号可以改变，而且订单肯定会继续
表GIN:
| GinNo | Gin1 |
|---|
| Gin2 |
| Gin3 |
| Gin4 |
这是一个有趣的挑战!恐怕我的代码比你的长得多，而且可能有一些方法可以使它更有效，但它确实有效。希望你觉得这有用!
/* -- Sample data setup code
-- Note to OP: DO NOT execute the lines in this code comment
-- in your production environment unless you want to lose
-- your tables or modify their data! For testing only.
DROP TABLE IF EXISTS GIN, Consumption;
CREATE TABLE GIN (GinNo CHAR(4));
CREATE TABLE Consumption ([Order] CHAR(6), GinNo CHAR(4));
INSERT INTO GIN VALUES ('Gin1'),('Gin2'),('Gin4'),('Gin3');
INSERT INTO Consumption VALUES ('Order1', 'Gin1'),('Order1', 'Gin2'),('Order1', 'Gin3')
,('Order2', 'Gin3'),('Order2', 'Gin1'),('Order3', 'Gin4'),('Order3', 'Gin2')
*/
IF OBJECT_ID(N'tempdb..#GIN', N'U') IS NOT NULL DROP TABLE #GIN;
-- Add version with identity column to make it easier to loop through
CREATE TABLE #GIN (Id INT IDENTITY, GinNo VARCHAR(50));
INSERT INTO #GIN (GinNo)
SELECT GinNo FROM GIN ORDER BY GinNo;
DECLARE @cols NVARCHAR(MAX) = '', @colNo INT = 1, @colName VARCHAR(50);
---- single & double quote
DECLARE @sq CHAR(1) = '''';
DECLARE @dq CHAR(2) = @sq + @sq;
WHILE @colNo <= (SELECT MAX(Id) FROM #GIN)
BEGIN
SET @colName = (SELECT REPLACE(GinNo, @sq, @dq) FROM #GIN WHERE Id = @colNo); -- sanitise inputs
SET @cols += CONCAT(
',ISNULL(CAST(SUM(CASE WHEN grp.GinNo2 = ''',@colName
,''' THEN grp.quantity END) AS VARCHAR), CASE WHEN grp.GinNo1 = '''
,@colName,''' THEN ''-'' ELSE ''0'' END) AS ', @colName
,CHAR(13) -- for better formatting when using PRINT
);
SET @colNo += 1;
END
DECLARE @sql NVARCHAR(MAX) = '
;WITH grp
AS (
SELECT a.GinNo AS GinNo1, b.GinNo AS GinNo2, COUNT(*) AS quantity
FROM Consumption AS a
JOIN Consumption AS b ON a.[Order] = b.[Order] AND a.GinNo <> b.GinNo
GROUP BY a.GinNo, b.GinNo
)
SELECT gin.GinNo AS [Gin''s]
' + @cols + '
FROM #GIN AS gin
LEFT JOIN grp ON gin.GinNo = grp.GinNo1
GROUP BY gin.GinNo, grp.GinNo1;';
--PRINT(@sql); -- Always review dynamic sql before execution
EXEC (@sql);
/* Re-test code after inserting more values. As expected.
INSERT INTO GIN VALUES ('Gin5')
INSERT INTO Consumption VALUES ('Order4', 'Gin5'),('Order4', 'Gin2')
*/
有相似之处，但我认为最好把它写为一个新的答案，因为它的工作方式与我的另一个答案相当不同。我发现了一种更有效的方法来实现相同的结果，它不需要循环或临时表。
/* -- Sample data setup code
-- Note to OP: DO NOT execute the lines in this code comment
-- in your production environment unless you want to lose
-- your tables or modify their data! For testing only.
DROP TABLE IF EXISTS GIN, Consumption;
CREATE TABLE GIN (GinNo CHAR(4));
CREATE TABLE Consumption ([Order] CHAR(6), GinNo CHAR(4));
INSERT INTO GIN VALUES ('Gin1'),('Gin2'),('Gin4'),('Gin3');
INSERT INTO Consumption VALUES ('Order1', 'Gin1'),('Order1', 'Gin2'),('Order1', 'Gin3')
,('Order2', 'Gin3'),('Order2', 'Gin1'),('Order3', 'Gin4'),('Order3', 'Gin2')
*/
-- single & double quote
DECLARE @sq CHAR(1) = '''';
DECLARE @dq CHAR(2) = @sq + @sq;
DECLARE @sql NVARCHAR(MAX) = '
;WITH grp
AS (
SELECT a.GinNo AS GinNo1, b.GinNo AS GinNo2, COUNT(*) AS quantity
FROM Consumption AS a
INNER JOIN Consumption AS b ON a.[Order] = b.[Order] AND a.GinNo <> b.GinNo
GROUP BY a.GinNo, b.GinNo
)
SELECT GIN.GinNo AS [Gin''s]';
SELECT @sql += CONCAT(',ISNULL(CAST(SUM(CASE WHEN grp.GinNo2 = ''',rGinNo,
''' THEN grp.quantity END) AS VARCHAR), CASE WHEN grp.GinNo1 = ''',rGinNo,
''' THEN ''-'' ELSE ''0'' END) AS ', rGinNo, CHAR(13))
FROM GIN
CROSS APPLY (
SELECT REPLACE(GinNo, @sq, @dq) AS rGinNo
) AS t
ORDER BY GinNo;
SET @sql += '
FROM GIN
LEFT JOIN grp ON GIN.GinNo = grp.GinNo1
GROUP BY GIN.GinNo, grp.GinNo1;';
--PRINT(@sql); -- Always review dynamic sql before execution
EXEC (@sql);
/* Re-test code after inserting more values. As expected.
INSERT INTO GIN VALUES ('Gin5')
INSERT INTO Consumption VALUES ('Order4', 'Gin5'),('Order4', 'Gin2')
*/