给定这个200万+条目表,ID自动递增,索引1(MainId,SubID,Column1)index2 (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条件中,但这不是必需的,NULL值无论如何都会被忽略。
现在您可以简单地将上述子查询作为派生表进行两次左连接:
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
;
UPDATE(转换为视图,以回复注释)
到目前为止,我只能看到一个视图友好的替代方案:
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中是否有效。适当的索引可能会有所帮助。一般来说,您可能需要亲自测试。