问题描述
我知道group_concat在SQL Server 2008中不起作用，但是我想执行group_concat.
I know group_concat doesn't work in SQL Server 2008, but I want to do group_concat.
我的样本数据如下:
email address | product code
----------------------+---------------
[email protected] | A123A
[email protected] | AB263
[email protected] | 45632A
[email protected] | 78YU
[email protected] | 6543D
我想要这个结果:
[email protected] | A123A,AB263,6543D
[email protected] | 45632A,78YU
我尝试过的代码:
SELECT
c.EmailAddress,
od.concat(productcode) as Product_SKU
FROM
OrderDetails od
JOIN
Orders o ON od.OrderID = o.OrderID
JOIN
Customers c ON c.CustomerID = o.CustomerID
WHERE
o.OrderDate BETWEEN 01/01/2016 AND GETDATE()
GROUP BY
c.EmailAddress
我得到一个错误:
但是这不起作用.谁能告诉我正确的方法吗?
But this is not working. Can anyone please tell me correct way of doing this?
编辑后我要尝试的代码:
Code I am trying after editing:
SELECT
c.EmailAddress,
productcode = STUFF((SELECT ',' + od.productcode
FROM Orderdetails od
WHERE c.EmailAddress = od.EmailAddress
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM
OrderDetails od
JOIN
Orders o ON od.OrderID = o.OrderID
JOIN
Customers c ON c.CustomerID = o.CustomerID
WHERE
o.OrderDate BETWEEN 01/01/2016 AND GETDATE()
现在我收到此错误:
推荐答案
为您举例说明联接(在这种情况下为自我联接，但所有联接均有效)并为此使用STUFF.注意STUFF中的WHERE子句.这就是将记录链接到正确值的原因.
To give you an illustration of joining (in this case a self join, but all joins work) and using STUFF for this. Notice the WHERE clause inside the STUFF. This is what links the record to the correct values.
declare @test table
(
email varchar(50),
address varchar(50)
)
insert into @test VALUES
('[email protected]','A123A'),
('[email protected]','AB263'),
('[email protected]','45632A'),
('[email protected]','78YU'),
('[email protected]','6543D')
SELECT DISTINCT
email,
Stuff((SELECT ', ' + address
FROM @test t2
WHERE t2.email = t1.email
FOR XML PATH('')), 1, 2, '') Address
FROM @test t1
编辑
好的，所以您想要的(您真正想要的)是:
OK so what you want (what you really, really want) is:
declare @customers table
(
emailaddress varchar(50),
customerid int
)
insert into @customers VALUES
('[email protected]',1),
('[email protected]',2)
declare @orders table
(
orderid int,
customerid int,
orderdate date
)
insert into @orders VALUES
(1, 1, '2017-06-02'),
(2, 1, '2017-06-05'),
(3, 1, '2017-07-13'),
(4, 2, '2017-06-13')
declare @orderdetails table
(
id int,
orderid int,
productcode varchar(10)
)
insert into @orderdetails VALUES
(1, 1, 'apple pie'),
(2, 1, 'bread'),
(3, 2, 'custard'),
(4, 2, 'orange'),
(5, 3, 'orange'),
(6, 4, 'orange')
SELECT DISTINCT c.EmailAddress, productcode=
STUFF((SELECT ',' + odc.productcode FROM
(SELECT DISTINCT emailaddress,productcode FROM
@orders o2 inner join @orderdetails od2 on
o2.orderid = od2.orderid
inner join @customers c2 ON c2.customerid = o2.customerid) odc
WHERE odc.emailaddress=c.emailaddress
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM @OrderDetails od
JOIN @Orders o ON od.OrderID = o.OrderID
JOIN @Customers c ON c.CustomerID=o.CustomerID
WHERE o.OrderDate BETWEEN '2016-01-01' AND getdate()
在此处通知更改.现在，STUFF中的SELECT来自一个子查询，因此您可以按EmailAddress进行分组.
Notice the change here. The SELECT in the STUFF is now from a sub-query, so that you can group by EmailAddress.
其他说明
您的目标是将按客户分组的产品代码(以电子邮件地址表示)串联起来.问题是产品代码在orderdetails表中，而emailaddress在customer表中，但是没有将两者链接的字段.客户表与订单表具有一对多关系，而订单表与订单细节表具有一对多关系.那是抽象层次太多了.因此，我们需要通过提供产品代码和电子邮件地址之间的直接链接来帮助数据库.我们通过子查询来完成.我希望这可以使您更清楚.
Your aim is to have a concatenation of product codes grouped by customer (represented by email address). The problem is that product codes are in the orderdetails table and emailaddress is in the customer table, but there is no field which links the two. The customer table has a one to many relationship with the orders table and the orders table has a one to many relationship with the orderdetails table. That is one level of abstraction too many. So we need to give the database a helping hand by providing a direct link between productcode and emailaddress. This we do via the Sub-query. I hope that this makes it clearer for you.
这篇关于我想在SQL Server中做group_concat的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！