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,some@email.com,14272.00,MXN
654,SO654,2015-05 -17,2015-04-15,Edgar,one@email.com,4994.96,MXN
653 SOSO,2015-05-17,2015-04-15,Edgar,one@email.com,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, some@email.com, 14272.00, MXN
654, SO654, 2015-05-17, 2015-04-15, Edgar, one@email.com, 4994.96, MXN
653, SO653, 2015-05-17, 2015-04-15, Edgar, one@email.com, 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, some@email.com
SO653 & SO654, Edgar, one@email.com
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屋!