本文介绍了SQL Server 2012 中的 group_concat 与 ORDER BY 另一列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个包含大约一百万个这样的条目的表:
I have a table containing ~ a million entries like this:
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 并按购买的_at 的顺序
I want to concatenate the products to one row, DISTINCT and in order of the purchased_at
在 MySQL 中我只使用
In MySQL I just use
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;
得到
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 中做到这一点?
How can I do that in SQL Server 2012?
我尝试了以下hack",它有效,但它是一种矫枉过正,并且在长桌子上表现不佳
I tried the following 'hack', which works, but it's an overkill and doesn't perform well on a long table
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;
我该怎么做才能解决这个问题?
What can I do to solve this?
推荐答案
我不确定这是否会更快,但这里有一个替代版本,您无需加入两次购买
在 STUFF()
中:
I am not sure if this will be any faster, but here is an alternate version where you don't join on purchases
twice in the STUFF()
:
select customer_id,
min(purchased_at) as first_purchased_at,
stuff ((select ',' + p2.product
from
(
select product, customer_id,
ROW_NUMBER() over(partition by customer_id, product order by purchased_at) rn,
ROW_NUMBER() over(partition by customer_id order by purchased_at) rnk
from purchases
) p2
where p2.customer_id = p1.customer_id
and p2.rn = 1
group by p2.product, rn, rnk
order by rnk
for XML PATH('') ), 1,1,'') AS all_purchased_products
from purchases p1
group by customer_id;
结果:
| CUSTOMER_ID | FIRST_PURCHASED_AT | ALL_PURCHASED_PRODUCTS |
---------------------------------------------------------------------------
| 1 | June, 01 2012 00:00:00+0000 | apples,pears |
| 2 | June, 01 2012 00:00:00+0000 | apples |
| 3 | September, 02 2012 00:00:00+0000 | pears,apples,bananas |
这篇关于SQL Server 2012 中的 group_concat 与 ORDER BY 另一列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!