本文介绍了如何使用"Stuff and'For Xml Path'"统一表格中的行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

请帮助我获得统一的行和表的列表,并用逗号分隔.我不太了解如何使用"Stuff and'For Xml Path'"为此.

Please help me to get united rows and list of accounts separated by commas in table. I don't quite understand how to use "Stuff and 'For Xml Path'" for it.

这是我的查询

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张不同发票的总和,并用逗号指定涉及这些发票的帐户.

So actually I need to get sums for 2 different invoices and to specify accounts by commas which involved to those invoices.

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

Also have this stuff at dbfiddle here: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=7a5de9e680693b5e70ea68cecebef6cc

预先感谢您.

推荐答案

如果要求和,请勿按 sum 分组.在其上使用 sum().并关联子查询.否则,您将获得所有帐户.

Don't group by summ if you want to sum it. Use sum() on it. And correlate the subquery. Otherwise you'll just get all accounts.

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;

这篇关于如何使用"Stuff and'For Xml Path'"统一表格中的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-21 13:33