SQL 在重复记录上使用 XMLAGG 中的 distinct
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13306144/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using distinct in XMLAGG on duplicating records
提问by Fr_nkenstien
I am facing a problem using XMLAGG in sql.
我在 sql 中使用 XMLAGG 时遇到问题。
I have a table with multiple records which can be repeated. the Table contains Customer Addresses and Customer Name.
我有一个包含多条记录的表，可以重复。该表包含客户地址和客户名称。
Create Table cust_data(
cust_name varchar2(30),
cust_addr_line1 varchar2(300),
cust_addr_line2 varchar2(300),
cust_addr_line3 varchar2(300),
cust_addr_type varchar2(3));
The table may contain multiple records for a Single Customer Name and Different Address Types.
该表可能包含单个客户名称和不同地址类型的多个记录。
Also, a single customer may have multiple addresses of the same type also.
此外，单个客户也可能有多个相同类型的地址。
so a customer may have addresses like
所以客户可能有这样的地址
cust1 address1 curr_address
cust1 address2 old_address
cust1 address3 old_address
cust1 address4 old_address
cust2 address5 curr_address
cust2 address6 old_address
I have a select where i want to take out all the customer names with old_addresses in a comma separated format.
我有一个选择，我想以逗号分隔的格式取出所有带有 old_addresses 的客户名称。
using the same i used the following sql
使用相同的我使用了以下 sql
select XMLAGG(XMLELEMENT(E, cust_name || ',')).EXTRACT('//text()')
from cust_data where cust_addr_type ='old_address';
I get the following output:
我得到以下输出：
cust1,cust1,cust1,cust2,
cust1,cust1,cust1,cust2,
how do get the output as
如何获得输出
cust1,cust2
客户 1,客户 2
Please help.
请帮忙。
Edit#1:
编辑#1：
The other tables can be taken like this:
其他表可以这样取：
Create Table cust_info(
cust_name varchar2(30),
Cust_account varchar2(300),
cust_amount_paid varchar2(300),
cust_amount_pend varchar2(300),
cust_payment_type varchar2(300));
Create Table payment_master_info(
pmnt_type varchar2(30),
pmnt_desc varchar2(300),
pmnt_rate varchar2(300),
pmnt_tenure varchar2(300));
The query is like this:
查询是这样的：
SELECT XMLAGG(XMLELEMENT(E, CUST_NAME || ',')) .EXTRACT('//text()'),
CD.CUST_ADDR_LINE1,
CD.CUST_ADDR_LINE2,
CD.CUST_ADDR_LINE3,
CI.CUST_AMOUNT_PAID,
CI.CUST_AMOUNT_PEND,
CI.CUST_ACCOUNT
FROM CUST_INFO CI, PAYMENT_MASTER_INFO PM, CUST_DATA CD
WHERE CD.CUST_NAME = CI.CUST_NAME
AND CI.CUST_PAYMENT_TYPE = PM.PMNT_TYPE
AND CUST_ADDR_TYPE = 'old_address';
Now the data in this huge. the data in the pmnt_type ranges from 10000-15000 data and in other tables it ranges from 2100000-5000000
现在这个数据量巨大。pmnt_type 中的数据范围为 10000-15000 数据，其他表中的数据范围为 2100000-5000000
If I apply inner queries for distinct data, the performance drops exponentially.
如果我对不同的数据应用内部查询，则性能会呈指数下降。
is there any other way?
还有其他方法吗？
Edit#2: Also while using this inner query, during executing, i getting an ORA-19011(: Character string buffer too small.) error. can there be any reason why...??
编辑#2：同样在使用这个内部查询时，在执行过程中，我收到一个 ORA-19011（：字符串缓冲区太小。）错误。有什么理由为什么......？
回答by Vincent Malgrat
You can use a DISTINCT in an inner query:
您可以在内部查询中使用 DISTINCT：
SQL> select XMLAGG(XMLELEMENT(E, cust_name || ',')).EXTRACT('//text()')
2 from (SELECT distinct cust_name, cust_addr_type FROM cust_data)
3 where cust_addr_type ='old_address';
XMLAGG(XMLELEMENT
-----------------
cust1,cust2,
