每个父级的内部联接限制 [英] Limit on inner join per parent

问题描述

我有一个名为 weathers 的表:

+--------+-------+---------+|身份证 |温度 |id_freg |+--------+-------+---------+|第337话12.36 |1 ||3556 |11.46 |2 ||6775 |9.30 |3 ||10210 |8.55 |1 ||13429 |9.69 |2 |

还有一个名为freguesias的表(葡萄牙的小地方):

+----+-----------+|身份证 |姓名 |+----+-----------+|1 |Cabeção ||2 |帕维亚 ||3 |布罗塔斯 ||4 |莫拉 |

从这里我需要做一个内部连接并限制每个 freguesia 的天气结果数量，所以如果我想要 3 个 freguesias 的天气，我想要为每个order desc limit 1，换句话说，您可以在where子句中说这是每个freguesias的当前天气.>

我此时的查询是:

选择天气.*从天气内连接freguesias打开weathers.id_freg = freguesias.id其中weathers.id_freg IN (2,1)限制 1；

好吧，这不起作用，因为我需要为每个结果一个结果:一个用于 id_freg = 1，另一个用于 id_freg = 2，每个都按 weathers.id DESC

解决方案

假设 weathers.id 指定了最近的值，那么您可以计算每个 freguensia 的最近值.在这种情况下，我会推荐一个相关的子查询:

选择 w.*从天气 w其中 w.id_freg 在 (2, 1) 和w.id = (选择最大(w2.id)从天气 w2其中 w2.id_freg = w.id_freg);

您需要 weathers(id_freq, id) 的索引以提高性能.

I have a table named weathers:

+--------+-------+---------+
| id     | temp  | id_freg |
+--------+-------+---------+
|    337 | 12.36 |       1 |
|   3556 | 11.46 |       2 |
|   6775 |  9.30 |       3 |
|  10210 |  8.55 |       1 |
|  13429 |  9.69 |       2 |

And a table named freguesias (small places in portugal):

+----+-----------+
| id | name      |
+----+-----------+
|  1 | Cabeção   |
|  2 | Pavia     |
|  3 | Brotas    |
|  4 | Mora      |

What I need from here is to do an INNER join and limit the the number of weathers results per freguesia, so if I want the weather for 3 freguesias I want to order desc limit 1 for each, in other words you can say that is the current weather for each freguesias in the where clause.

My query at this moment is:

select weathers.* 
from weathers 
inner join freguesias 
    ON weathers.id_freg = freguesias.id 
where weathers.id_freg IN (2,1) 
LIMIT 1;

Well this doesn't work since I need one result for each: one for id_freg = 1, and another for id_freg = 2, each one ordered by weathers.id DESC

解决方案

Assuming that weathers.id specifies the most recent value, then you can calculate the most recent value for each freguensia. In this case I would recommend a correlated subquery:

select w.* 
from weathers w
where w.id_freg in (2, 1) and
      w.id = (select max(w2.id) 
              from weathers w2 
              where w2.id_freg = w.id_freg
             );

You want an index on weathers(id_freq, id) for performance.

这篇关于每个父级的内部联接限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！