分组后，MySQL获得第一个非空值 [英] MySQL get first non null value after group by

问题描述

我有一张大数据表，其数据不是唯一的，但需要。该表是多重联合选择的结果，因此不是实际的表。由于其他原因，我无法将其作为实际表格。

所有UNION表格都有一个电子邮件列，它最终将是唯一的。结果记录如下所示：

  1 [e-mail] Ozzy 
 2 [e-mail] Tony 
 3 [e-mail]史蒂夫
 4 [e-mail] 
 13 [e-mail]托尼
 14 [e-mail]奥兹
 15雅虎考试@雅虎.com Dave 
 16 [e-mail] Tim 
  

正如您所看到的，一些电子邮件用不同的名字或不存在的名字出现多次。当我在最后添加 GROUP BY电子邮件子句时，结果如下所示：

  1 [e-mail] Ozzy 
 2 [e-mail] Tony 
 3 [e-mail] Steve 
 4 [e-mail] 
 13 tony @ gmail.com Tony 
  

正如您所见，电子邮件4没有名称，因为它选择了用 NULL 作为名字的第一项。然后我尝试使用 GROUP_CONCAT ，结果如下所示：

  1 [e-mail] Ozzy 
 14 [e-mail] Ozzy，Tony $ b $ 15 [e-mail] Dave，Steve $ b $ 16 [e-mail] Tim 
 13 [e-mail] Tony 
  

正如你所看到的，现在每个人都有一个名字，但有些行更多然后一个名字concatinated。我想要做的是 GROUP BY电子邮件并从理论上为每行选择每列的第一个 NOT NULL 条目看起来像这样：

  1 [e-mail] Ozzy 
 2 [e-mail] Tony 
 3 [e-mail] Steve 
 4 [e-mail] Tim 
 13 [e-mail] Tony 
  

我尝试过使用 COALESCE ，但它不能按预期工作。我目前的查询如下所示：

 选择
 id，
电子邮件，
`名称`
 FROM 
（
 SELECT 
 email，
`name` 
 FROM 
 multiple_tables_and_unions 
）AS电子邮件
 
 GROUP BY电子邮件
  

我从临时表中删除了代码，因为它包含许多表但所有选择电子邮件和名称列。基本上我需要像 GROUP_COALESCE 这样的函数，但不幸的是它不存在。我的选择是什么？

谢谢 使用 MAX ，如下所示：

  SELECT 
 email，
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 $ b $ AS电子邮件
 
 GROUP BY电子邮件
  

I have a large table with data that is not unique but needs to be. This table is a result of multiple union selects so is not an actual table. I cannot make it an actual table for other reasons.

All of the UNION'd tables have an email column which will eventually be unique. The resulting records look like this:

1   [e-mail]   Ozzy
2   [e-mail]   Tony
3   [e-mail]  Steve
4   [e-mail]   
13  [e-mail]  Tony
14  [e-mail]   Ozzy
15  [e-mail]  Dave
16  [e-mail]   Tim

As you can see, some emails appear more then once with different names or non existent names. When I add a GROUP BY email clause at the end, the results look like this:

1   [e-mail]   Ozzy
2   [e-mail]   Tony
3   [e-mail]  Steve
4   [e-mail]   
13  [e-mail]  Tony

As you can see, email 4 does not have a name because it chose the first entry with NULL for a name. Then I tried to use GROUP_CONCAT which made the results look like this:

1   [e-mail]   Ozzy
14  [e-mail]   Ozzy,Tony
15  [e-mail]  Dave,Steve
16  [e-mail]   Tim
13  [e-mail]  Tony

As you can see, now everyone has a name but some rows have more then one name concatinated. What I want to do is GROUP BY email and choose the first NOT NULL entry of each column for each row to theoretically look like so:

1   [e-mail]   Ozzy
2   [e-mail]   Tony
3   [e-mail]  Steve
4   [e-mail]   Tim
13  [e-mail]  Tony

I have tried using COALESCE but it doesnt work as intended. My current query looks like so:

SELECT
    id,
    email,
    `name`
FROM
(
    SELECT
        email,
        `name`
    FROM
        multiple_tables_and_unions
) AS emails

GROUP BY email

I have removed the code from the temporary table as it contains many tables but all select the email and name column. Essentially I need a function like GROUP_COALESCE but unfortunately it does not exist. What are my options?

Thanks

解决方案

Try using MAX, like this:

SELECT
    email,
    MAX(`name`)
FROM
(
    SELECT
        email,
        `name`
    FROM
        multiple_tables_and_unions
) AS emails

GROUP BY email

这篇关于分组后，MySQL获得第一个非空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！