时间戳

首页 > 解决方案 > 如何使用“Stuff and 'For Xml Path'”来合并表格中的行

sql-server - 如何使用“Stuff and 'For Xml Path'”来合并表格中的行

问题描述

请帮助我在表格中获取用逗号分隔的统一行和帐户列表。我不太明白如何使用“Stuff and 'For Xml Path'”。

这是我的查询：

CREATE TABLE invoices
(
invoice VARCHAR(20) NOT NULL,
quantity INT NOT NULL,
price INT NOT NULL,
summ INT NOT NULL, 
account INT NOT NULL,
);
INSERT invoices(invoice, quantity, price, summ, account) 
VALUES ('ty20210110', 2, 100, 200, 1001);
INSERT invoices(invoice, quantity, price, summ, account) 
VALUES ('ty20210110', 3, 100, 300, 1002);
INSERT invoices(invoice, quantity, price, summ, account) 
VALUES ('ty20210110', 1, 250, 250, 1001);
INSERT invoices(invoice, quantity, price, summ, account) 
VALUES ('ty20210110', 2, 120, 240, 1002);
INSERT invoices(invoice, quantity, price, summ, account) 
VALUES ('ty20210110', 4, 100, 400, 1002);
INSERT invoices(invoice, quantity, price, summ, account) 
VALUES ('ty20210114', 3, 100, 300, 1001);
INSERT invoices(invoice, quantity, price, summ, account) 
VALUES ('ty20210114', 5, 80, 400, 1003);
INSERT invoices(invoice, quantity, price, summ, account) 
VALUES ('ty20210114', 5, 100, 500, 1004);


SELECT invoices.invoice, invoices.summ,  accounts = STUFF(
             (SELECT DISTINCT ',' + Convert(varchar, invoices.account, 60) 
              FROM invoices
              FOR XML PATH (''))
             , 1, 1, '')
FROM invoices
GROUP BY invoices.invoice, invoices.summ

这就是我得到的结果：

发票 总和 帐户
ty20210110 200 1001,1002,1003,1004
ty20210110 240 1001,1002,1003,1004
ty20210110 250 1001,1002,1003,1004
ty20210110 300 1001,1002,1003,1004
ty20210110 400 1001,1002,1003,1004
ty20210114 300 1001,1002,1003,1004
ty20210114 400 1001,1002,1003,1004
ty20210114 500 1001,1002,1003,1004

这是我需要得到的结果：

发票 总和 帐户
ty20210110 1390 1001,1002
ty20210114 1200 1003,1004

所以实际上我需要获取 2 张不同发票的总和，并用逗号指定这些发票所涉及的帐户。

在 dbfiddle 也有这些东西：https ://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=7a5de9e680693b5e70ea68cecebef6cc

提前谢谢你们。

标签： sql-serverconcatenationfor-xml-pathstuff

解决方案

summ如果你想总结它，不要分组。使用sum()它。并关联子查询。否则，您将获得所有帐户。

SELECT i1.invoice,
       sum(i1.summ) summ,
       stuff((SELECT DISTINCT
                     concat(',', i2.account)
                     FROM invoices i2
                     WHERE i2.invoice = i1.invoice
                     FOR XML PATH ('')),
             1,
             1,
             '') accounts
       FROM invoices i1
       GROUP BY i1.invoice;

推荐阅读