MySQL-分组多行，同时保留差异 [英] MySQL - GROUP multiple rows while retaining differences

问题描述

我有一系列复杂的MySQL选择查询/联接，它们产生的结果与此类似:

I have a series of complicated MySQL select queries / joins that produce results similar to this:

|----|---------|----------|----------|---------------|---------------|
| id | company | city     | province | manager_email | staff_email   |
|----|---------|----------|----------|---------------|---------------|
| 1  | aaa     | toronto  | ON       | [e-mail]  |               |
| 1  | aaa     | toronto  | ON       |               | [e-mail] |
| 2  | bbb     | sudbury  | ON       | [e-mail]  |               |
| 3  | ccc     | hamilton | ON       | [e-mail]  |               |
| 3  | ccc     | hamilton | ON       |               | [e-mail] |
|----|---------|----------|----------|---------------|---------------|

• 大多数公司"都有两行，除了电子邮件之外，其余两行都是相同的
• manager_email和staff_email永远不会出现在同一行
• 在某些情况下，公司"只能排一行
• most "companies" have two rows, which are identical other than the emails
• manager_email and staff_email will never appear in the same row
• there are some cases where a "company" will only have one row

是否可以使用GROUP BY或类似的语句将所有这些重复的行进行分组，同时保留这两封电子邮件?例如

Is there a GROUP BY or a similar statement that I can use to group all of these duplicate rows, while retaining both emails? e.g.

|----|---------|----------|----------|---------------|---------------|
| id | company | city     | province | manager_email | staff_email   |
|----|---------|----------|----------|---------------|---------------|
| 1  | aaa     | toronto  | ON       | [e-mail]  | [e-mail] |
| 2  | bbb     | sudbury  | ON       | [e-mail]  |               |
| 3  | ccc     | hamilton | ON       | [e-mail]  | [e-mail] |
|----|---------|----------|----------|---------------|---------------|

我愿意在需要时分享更多细节，但在这一点上，我认为这只会增加混乱.

I'm willing to share more details if needed, but at this point I think it'll just add confusion.

推荐答案

您可以利用大多数聚合函数忽略null的事实:

You can take advantage of the fact that most aggregate functions ignore null:

select 
    id, 
    company, 
    city, 
    province, 
    max(manager_email) manager_email, 
    max(staff_email) staff_email
from mytable
group by id, company, city, province

这篇关于MySQL-分组多行，同时保留差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！