我有一个包含约一百万个条目的表,如下所示:
customer_id | purchased_at | product
1 | 2012-06-01 00:00 | apples
1 | 2012-09-02 00:00 | apples
1 | 2012-10-01 00:00 | pears
2 | 2012-06-01 00:00 | apples
2 | 2012-07-01 00:00 | apples
3 | 2012-09-02 00:00 | pears
3 | 2012-10-01 00:00 | apples
3 | 2012-10-01 01:00 | bananas
我想将产品连接到一行,DISTINCT 并按照购买的顺序
在 MySQL 中我只是使用
select customer_id, min(purchased_at) as first_purchased_at,
group_concat(DISTINCT product order by purchased_at) as all_purchased_products
from purchases group by customer_id;
to get
customer_id | first_purchased_at | all_purchased_products
1 | 2012-06-01 00:00 | apples, pears
2 | 2012-06-01 00:00 | apples
3 | 2012-09-02 00:00 | pears, apples, bananas
我怎样才能在 SQL Server 2012 中做到这一点?
我尝试了以下“黑客”,它有效,但它有点矫枉过正,并且在长桌上表现不佳
select
customer_id,
min(purchased_at) as first_purchased_at,
stuff ( ( select ',' + p3.product
from (select p2.product, p2.purchased_at,
row_number() over(partition by p2.product order by p2.purchased_at) as seq
from purchases p2 where
p2.customer_id = p1.customer_id ) p3
where p3.seq = 1 order by p3.purchased_at
for XML PATH('') ), 1,1,'') AS all_purchased_products
from purchases p1
group by customer_id;
我能做什么来解决这个问题?