如何编写查询以删除除按ID分组的最大值以外的所有内容？

Question

我试图编写一个查询，以便根据ID和值删除重复的记录。有多行具有相同的ID。获取结果的条件是(以及我根据我的理解编写的查询)，

1. 查找值列中ID列(SELECT * FROM TABLE WHERE VALUE IN (SELECT MAX(VALUE) FROM TABLE GROUP BY ID)的最大可用值

示例：

表数据：

ID值

甲-一

阿-2

A-3

B-2

C-3

输出：

ID值

A-3

B-2

C-3

1. 忽略表中第1点的结果(SELECT * FROM TABLE WHERE NOT EXISTS ((SELECT * FROM TABLE WHERE VALUE IN (SELECT MAX(VALUE) FROM TABLE GROUP BY ID))

编辑:我编写了一个查询，最终输出第2点所需的结果。

SELECT t1.* FROM TABLE t1 
  LEFT JOIN
  ( 
    SELECT 1 AS aux, * FROM (SELECT * FROM TABLE
    WHERE VALUE IN 
    (SELECT MAX(VALUE) FROM TABLE group by ID))
  ) t2
  ON 
  t2.ID= t1.ID
  and 
  t2.VALUE= t1.VALUE
  WHERE t2.aux IS NULL

示例：

表数据：

ID值

甲-一

阿-2

A-3

B-2

C-3

输出：

ID值

甲-一

阿-2

1. 使用点2的查询从表中删除行(DELETE FROM TABLE WHERE (ID,VALUE) IN (SELECT * FROM TABLE WHERE NOT EXISTS ((SELECT * FROM TABLE WHERE VALUE IN (SELECT MAX(VALUE) FROM TABLE GROUP BY ID)))

示例：

表数据：

ID值

甲-一

阿-2

A-3

B-2

C-3

表数据：

ID值

A-3

B-2

C-3

第2点不起作用，没有结果。当选中来自第2点的查询的总行和表的总行时，就会有差异。

由于第2点不起作用，第3点也失败了。我做错了什么？

Answer 1

经过我们的讨论，我了解到您的目标是选择许多行的数据，这些数据符合过滤器id和max(value)。因此，我可以建议您使用以下脚本：

SELECT
  DISTINCT a.*
FROM
  `test-proj-261014.sample.id_value` a
RIGHT JOIN (
  SELECT
    id,
    MAX(value) AS max_val
  FROM
    `test-proj-261014.sample.id_value`
  GROUP BY
    id
  ORDER BY
    id) b
ON
  a.id = b.id
  AND a.value = b.max_val
WHERE
  a.value IS NOT NULL
ORDER BY
  id;

不是我使用选择不同，它不会选择复制的数据。此外，由于存在null值的可能性，我添加了condition *其中a.value不是NULL*，它不会选择不尊重条件的行。

上面的查询应该可以解决这个问题，但是如果您发现与预期的行数有任何出入，我鼓励您探索您的数据集，并检测为什么会有额外的或更少的行。您可以使用不同类型的联接来完成此操作，其中一个示例是以下查询：

SELECT
  a.*
FROM
  `test-proj-261014.sample.id_value` a
LEFT JOIN (
  SELECT
    id,
    MAX(value) AS max_val
  FROM
    `test-proj-261014.sample.id_value`
  GROUP BY
    id
  ORDER BY
    id) b
ON
  a.id = b.id
  AND a.value = b.max_val
WHERE
  b.max_val IS NULL
ORDER BY
  id;

此查询检索第一个查询生成的最终输出中不存在的所有值。这将帮助您更好地理解您正在处理的数据。

希望能帮上忙。