gpt4 book ai didi

sql - 选择具有不匹配 ID 计数的不同元素

转载 作者:数据小太阳 更新时间:2023-10-29 07:59:12 25 4
gpt4 key购买 nike

我不确定我的问题是否可以单独通过查询来解决,还是我需要借助脚本语言来完成此操作

select customer_ref, full_name, email, phone from contacts where customer_ref = '123';

contact_id | customer_ref | full_name | email | phone
------------+----------------+----------------+----------------+--------------
1 | 123 | Jhon | jhon@xyz.com | 1234567890
2 | 123 | Jhon Doe | jhon@xyz.com | 1234567890
3 | 123 | JD | jhon@gmail.com | 1234567890
4 | 123 | Jhon | jhon@xyz.com | 1234567890
5 | 123 | Jhon | jhon@xyz.com | no phone given
6 | 123 | Jhon | jhon@xyz.com | 1234567890

我想要的是将匹配的信息组合在一起,例如

contact_ids|customer_ref  |   full_name    | email          |     phone      | count
-----------+--------------+----------------+----------------+----------------+------
[1, 4, 6] | 123 | Jhon | jhon@xyz.com | 1234567890 | 3
[2] | 123 | Jhon Doe | jhon@xyz.com | 1234567890 | 1
[4] | 123 | JD | jhon@gmail.com | 1234567890 | 1
[5] | 123 | Jhon | jhon@xyz.com | no phone given | 1

目前我正在借助ruby之类的东西进行分组

 contacts = Contact.select('full_name, email, phone').where(:customer_ref => '123')
contacts.inject(Hash.new(0)) { |k,v| k[v] += 1; k }.map {|k,v| {:contact =>k.with_indifferent_access, :count => v }} if contacts.present?

最佳答案

使用 array_agg() 按除 contact_id 之外的所有列分组:

select 
array_agg(contact_id) contact_ids,
customer_ref, full_name, email, phone,
count(*)
from contacts
group by 2, 3, 4, 5
order by 1

contact_ids | customer_ref | full_name | email | phone | count
-------------+--------------+-----------+----------------+----------------+-------
{1,4,6} | 123 | Jhon | jhon@xyz.com | 1234567890 | 3
{2} | 123 | Jhon Doe | jhon@xyz.com | 1234567890 | 1
{3} | 123 | JD | jhon@gmail.com | 1234567890 | 1
{5} | 123 | Jhon | jhon@xyz.com | no phone given | 1
(4 rows)

关于sql - 选择具有不匹配 ID 计数的不同元素,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/33196597/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com