GROUP_CONCAT用于PostgreSQL中的类似记录 [英] GROUP_CONCAT for similar records in PostgreSQL

问题描述

我在PostgreSQL中有以下查询：

  SELECT sale_order.id，sale_order.name AS orden，now（） ::日期AS hoy，
 sale_order.create_date :: date，split_part（res_partner.name，''，1）AS客户，
 res_partner.email，sale_order.amount_total，res_currency.name来自
 sale_order，product_pricelist，res_currency，res_partner，其中
 sale_order.partner_id = res_partner.id 
 AND sale_order.pricelist_id = product_pricelist.id 
 AND product_pricelist.currency_id = res_currency.id 
 AND sale_order .state ='已发送'
 ORDER BY sale_order.create_date DESC; 
  

，结果集如下所示：

  658，SO658，2015-05-17，2015-04-16，Alejandro，[e-mail]，14272.00，MXN 
 654，SO654，2015-05 -17，2015-04-15，Edgar，[e-mail]，4994.96，MXN 
653 SOSO，2015-05-17，2015-04-15，Edgar，[e-mail]，3007.29， USD 
  

如您所见，第二条记录和第三条记录以某种方式重复，因为它是同一用户但具有不同的ID，订单名称，日期等。

我想做的是忽略日期和金额，但使用GROUP_CONCAT订单名称，因此最终结果将是看起来像这样：

  SO658，Alejandro，some @ email.com 
 SO653& SO654，Edgar，one @ email.com 
  

如您所见，我删除了不必要的列和GROUP_CONCAT属于同一用户的订单名称，因此最终每个用户只有一条记录显示其所有订单名称。

如何在PostgreSQL中做到这一点？ / p>

谢谢！

解决方案

尝试没有表定义或SQL提琴的查询有点困难，但是：

您可以 GROUP BY cliente 和 email ，然后使用 array_agg ，这将返回 ARRAY 。如果您确实想要一个字符串，可以使用 string_agg（sale_order.name，'&'），但是数组通常更易于使用。

  SELECT array_agg（sale_order.name）AS Orden，
 split_part（res_partner.name，''，1）AS cliente，
 res_partner.email 
来自sale_order，product_pricelist，res_currency，res_partner 
 WHERE sale_order.partner_id = res_partner.id 
 AND sale_order.pricelist_id = product_pricelist.id 
 AND product_pricelist.currency_id = res_currency。 id 
 AND sale_order.state ='已发送'
 GROUP BY客户，电子邮件； 
  

I have a the following query in PostgreSQL:

SELECT sale_order.id,sale_order.name AS orden, now()::date AS hoy,
sale_order.create_date::date, split_part(res_partner.name,' ',1) AS cliente,
res_partner.email,sale_order.amount_total,res_currency.name FROM
sale_order,product_pricelist,res_currency,res_partner WHERE 
sale_order.partner_id = res_partner.id 
AND sale_order.pricelist_id = product_pricelist.id
AND product_pricelist.currency_id = res_currency.id 
AND sale_order.state = 'sent' 
ORDER BY sale_order.create_date DESC;

and the resulting set is looks like this:

658, SO658, 2015-05-17, 2015-04-16, Alejandro, [e-mail], 14272.00, MXN
654, SO654, 2015-05-17, 2015-04-15, Edgar, [e-mail], 4994.96, MXN
653, SO653, 2015-05-17, 2015-04-15, Edgar, [e-mail], 3007.29, USD

As you can see, the 2nd and 3rd records are somehow duplicated, somehow because its the same user but has different id, order name, dates, etc.

How I would like to do is disregard the dates and amounts but GROUP_CONCAT the order names, so the end result would look like this:

SO658, Alejandro, [e-mail]
SO653 & SO654, Edgar, [e-mail]

As you can see, I dropped the unnecessary columns and GROUP_CONCAT the order names that belongs to the same user so in the end each user would have only one record showing all their order names.

How can I do this in PostgreSQL?

Thank you!

解决方案

It is a bit hard trying out a query without table definitions or an SQL fiddle, but :

You can GROUP BY both cliente and email and then use array_agg, which will return an ARRAY. You can use string_agg(sale_order.name, ' & ') if you want really want a string, but arrays are usually easier to work with.

SELECT array_agg(sale_order.name) AS orden,
       split_part(res_partner.name,' ',1) AS cliente,
       res_partner.email
FROM  sale_order,product_pricelist,res_currency,res_partner
WHERE sale_order.partner_id = res_partner.id 
  AND sale_order.pricelist_id = product_pricelist.id
  AND product_pricelist.currency_id = res_currency.id 
  AND sale_order.state = 'sent'
GROUP BY cliente, email;

这篇关于GROUP_CONCAT用于PostgreSQL中的类似记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！