是否可以在聚合查询中向 MAX() 调用添加条件？

Question

背景

我的典型用例：

# Table

id     category    dataUID
---------------------------
0         A        (NULL)
1         B        (NULL)
2         C        text1
3         C        text1
4         D        text2
5         D        text3

# Query

SELECT MAX(`id`) AS `id` FROM `table`
GROUP BY `category`

这很好；它会删除正在处理的记录集中的所有“重复类别”，为每个类别提供“最高”ID。

然后我可以继续使用此 ID 再次提取所有数据：

# Query

SELECT * FROM `table` JOIN (
   SELECT MAX(`id`) AS `id` FROM `table`
   GROUP BY `category`
) _ USING(`id`)

# Result

id     category    dataUID
---------------------------
0         A        (NULL)
1         B        (NULL)
3         C        text1
5         D        text3

请注意，这与以下内容不同：

SELECT MAX(`id`) AS `id`, `category`, `dataUID` FROM `table`
GROUP BY `category`

Per 文档：

在标准 SQL 中，包含 GROUP BY 子句的查询不能引用 选择列表中未命名的非聚合列 GROUP BY 子句。例如，这个查询在标准 SQL 中是非法的 因为选择列表中的名称列不会出现在 分组依据：

SELECT o.custid, c.name, MAX(o. payment) FROM 订单 AS o, 客户
AS c WHERE o.custid = c.custid GROUP BY o.custid;

为了使查询合法，必须从查询中省略名称列 选择列表或在 GROUP BY 子句中命名。

MySQL扩展了GROUP BY的使用，使得选择列表可以引用 未在 GROUP BY 子句中命名的非聚合列。 这意味着 前面的查询在 MySQL 中是合法的。您可以使用此功能 通过避免不必要的列排序来获得更好的性能 分组。 但是，这主要是当每个值中的所有值 未在 GROUP BY 中命名的非聚合列对于每个列都是相同的 组。

[..]

此扩展假定未分组的列将具有相同的分组值。否则，结果是不确定的。

因此，我会得到 dataUID 的未指定值 - 例如，text2 或 text3 表示 id 5 的结果。

在我的实际案例中，这实际上是其他领域的问题；碰巧的是，对于 dataUID 列，通常我并不真正关心我得到哪个值。

---

问题

然而！

如果给定 category 的任何行具有 NULL dataUID，并且至少另一行具有非 NULL< /code> dataUID，我希望 MAX 忽略 NULL 的。

所以：

id     category    dataUID
---------------------------
4         D        text2
5         D        (NULL)

目前，由于我选择了 ID 最大的行，所以我得到：

5         D        (NULL)

但是，因为 dataUID 是 NULL，所以我想要：

4         D        text2

如何我明白了吗？如何在聚合 MAX 的使用中添加条件逻辑？

---

我想也许可以将 MAX 交给一个元组并提取 id之后从中取出：

GET_SECOND_PART_SOMEHOW(MAX((IF(`dataUID` NOT NULL, 1, 0), `id`))) AS `id`

但我不认为 MAX 会接受这样的任意表达式，更不用说元组了，而且我不知道如何在之后检索元组的第二部分-事实。

Answer 1

对 @ypercube 的答案稍作调整。要获取您可以使用的id

SELECT COALESCE(MAX(CASE
                      WHEN dataUID IS NOT NULL THEN id
                    END), MAX(id)) AS id
FROM   table
GROUP  BY category  

，然后将其插入join

Answer 2

最终，这比我想象的要容易，因为事实证明 MySQL将接受 MAX 内的任意表达式。

我可以通过在 id 中注入一个前导字符作为排序提示来获得我想要的排序：

SUBSTRING(MAX(IF (`dataUID` IS NULL, CONCAT('a',`id`), CONCAT('b',`id`))) FROM 2)

---

演练：

id     category    dataUID    IF (`dataUID` IS NULL, CONCAT('a',`id`), CONCAT('b',`id`)
--------------------------------------------------------------------------------------
0         A        (NULL)                             a0
1         B        (NULL)                             a1
2         C        text1                              b2
3         C        text1                              b3
4         D        text2                              b4
5         D        (NULL)                             a5

所以：

SELECT
   `category`, MAX(IF (`dataUID` IS NULL, CONCAT('a',`id`), CONCAT('b',`id`)) AS `max_id_with_hint`
FROM `table`
GROUP BY `category`

category   max_id_with_hint
------------------------------
A          a0
B          a1
C          b3
D          b4

然后再次删除排序提示就很简单了。

特别感谢 @JlStone 通过 COALESCE 为我设置了在 MAX 调用中嵌入表达式的路径，并直接操作提供给 MAX< 的值/代码>。

Answer 3

据我所知，您可以使用 COALESCE分组语句内部。例如。

SELECT MAX(COALESCE(`id`,1)) ...

---

嗯，看来我第一次读得很快。我想也许你想要这样的东西？

SELECT * FROM `table` JOIN (
   SELECT MAX(`id`) AS `id` FROM `table`
   WHERE `dataUID` IS NOT NULL
   GROUP BY `category`
) _ USING(`id`)

或者也许

SELECT MAX(`id`) AS `id`, 
  COALESCE (`dataUID`, 0) as `dataUID`
FROM `table`
GROUP BY `category`

Answer 4

select * 
from t1
join (
  select max(id) as id,
    max(if(dataGUID is NULL, NULL, id)) as fallbackid,
    category
  from t1 group by category) as ids
on if(ids.id = fallbackid or fallbackid is null, id, fallbackid) = t1.id; 

Answer 5

SELECT t.*
FROM table AS t
  JOIN
  ( SELECT DISTINCT category
    FROM table
  ) AS tdc
  ON t.id = 
  COALESCE(
    ( SELECT MAX(id) AS id 
      FROM table 
      WHERE category = tdc.category
        AND dataUID IS NOT NULL
    ) 
  , ( SELECT MAX(id) AS id 
      FROM table 
      WHERE category = tdc.category
        AND dataUID IS NULL
    ) 
  )

Answer 6

您需要子句 OVER

SELECT id, category,dataUID 
FROM
 (
    SELECT ROW_NUMBER() OVER (PARTITION BY category ORDER BY id desc, dataUID desc ) rn, 
    id, category,dataUID FROM table 
 ) q
WHERE rn=1

考虑按 desc 排序最后会移动空值。