mysql - 具有by和where子句的多个列组的每个列的最新值

给定这个超过200万的条目表，ID自动递增，index1（MainId，SubID，Column1）
索引2（MainId，SubID，Column2）：

ID  MainID  SubID  Column1    Column2
--------------------------------------
1     1       A   1A_data_1
2     1       A              1A_data_2
3     2       B   2B_data_1
4     2       B              2B_data_2
5     1       A   ignore_me
6     1       A              1A_data_3

我可以使用以下索引获取包含所需列值的行ID：

Select max(ID)
From table where column1 is not null and column1 <>'ignore_me'
Group By MainID,SubID

Select max(id)
From table where column2 is not null and column2 <>'ignore_me'
Group By MainID,SubID

但我不能找到一个有效的方法，将它们与MainID、SubID组结合起来，得到以下结果：

MainID  SubID  Column1    Column2
--------------------------------
  1       A   1A_data_1  1A_data_3
  2       B   2B_data_1  2B_data_2

我试过很多不同的方法，但没有什么是不需要永远的。我需要另一个索引吗？我觉得我忽略了一些简单的事情，因为按查询分组非常快。有人能指点我正确的方向吗？

最佳答案

可以使用条件聚合计算单个查询中的两个ID：

SELECT
  MainID,
  SubID,
  MAX(CASE WHEN Column1 <> 'ignore_me' THEN ID END) AS ID1,
  MAX(CASE WHEN Column2 <> 'ignore_me' THEN ID END) AS ID2
FROM atable
GROUP BY
  MainID,
  SubID
;

您还可以显式地将AND ColumnN IS NOT NULL添加到WHEN条件，但这不是必需的，空值无论如何都将被忽略。
现在，您只需将上面的子查询作为派生表执行两个左联接：

SELECT
  tm.MainID,
  tm.SubID,
  t1.Column1,
  t2.Column2
FROM (
  SELECT
    MainID,
    SubID,
    MAX(CASE WHEN Column1 <> 'ignore_me' THEN ID END) AS ID1,
    MAX(CASE WHEN Column2 <> 'ignore_me' THEN ID END) AS ID2
  FROM atable
  GROUP BY
    MainID,
    SubID
) tm
  LEFT JOIN atable t1 ON tm.ID1 = t1.ID
  LEFT JOIN atable t2 ON tm.ID2 = t2.ID
;

更新（转换为视图，以响应注释）
到目前为止，我只能看到一个有利于观景的选择：

SELECT
  MainID,
  SubID,
  (
    SELECT Column1
    FROM atable
    WHERE MainID = t.MainID
      AND SubID  = t.SubID
      AND Column1 <> 'ignore_me'
    ORDER BY ID DESC
    LIMIT 1
  ) AS ID1,
  (
    SELECT Column2
    FROM atable
    WHERE MainID = t.MainID
      AND SubID  = t.SubID
      AND Column2 <> 'ignore_me'
    ORDER BY ID DESC
    LIMIT 1
  ) AS ID2
FROM atable t
GROUP BY
  MainID,
  SubID
;

不过，这个查询可能比前一个查询慢：它使用两个相关的子查询，而且我不确定在MySQL中，带有相关子查询的查询（特别是视图）是否有效。适当的索引可能会有所帮助。一般来说，你可能需要自己测试一下。