sql - 如何在查询中获取逗号分隔值？

Question

您好，我有一个名为 tbdSales 的表

Brand     Cust_ID  Prd_ID

Aftron  44301   T3485
Aftron  44301   T0628
Aftron  44301   T2952
Aftron  44301   T1958
Aftron  44302   T1940
Aftron  44302   T1939
Aftron  44303   T2419
Aftron  44303   T2045

在此表中，我希望 Product_ID 以逗号分隔，并由 Brand 和 Cust_ID

我生成了如下查询:

SELECT DISTINCT 
      Brand
    , Cust_ID
    , (
        SELECT DISTINCT second_id + ', ' 
        FROM tbdSales t2
        WHERE t2.Brand = t1.Brand AND t2.Cust_ID = t1.Cust_ID
        FOR XML PATH('')
    ) AS prd_ID into SalReport
FROM tbdSales t1 
GROUP BY Brand,Cust_ID

上面的查询正在给出结果。但是，如果记录更多(10,000)，那么它会花费很多时间，比如 5 分钟。

请告诉我任何其他减少查询完成时间的方法。

Answer 1

试试这个 SQLFiddle example .它使用 recursive query with CTE .为了更快地获得结果，您需要在 Brand、Cust_ID、Prd_ID 上建立索引:

with t2 as 
( select t0.*,
  row_number() over (partition by Brand,Cust_id order by Prd_id asc) G_id
  from
 (
  select distinct Brand,Cust_id,Prd_id from tbdSales
 ) t0 

 ),
  t1 as (
    select t.*,
           cast(Prd_id as varchar(max)) as m2
     from t2 t where g_id=1

 union all
 select b.*,
        cast(c.m2+','+b.Prd_id as varchar(max)) as m2
     from t2 b
         inner join t1 c
             on (c.g_id+1 = b.G_id) 
                and (b.Brand=c.Brand)
                and (b.Cust_id=c.Cust_Id)


)
  select brand,cust_id,M2 as Prd_id from 
  (
  select t1.*, 
         row_number() over (partition by Brand,Cust_id order by g_id desc) rn 
             from t1
  ) t3 where rn=1
 order by Brand,Cust_id