sql - 根据连接优先级列的最低值选择不同的标识行

Question

简化的表结构，所有 INT 列并且在标识列之外没有 PK:

节点 (n) 表:id

属性(一)表:id、node_id、type_id

类型(t)表:id, priority

我正在尝试选择一组属性，每个属性都具有其各自节点的最低 type.priority。虽然每个 node_id 有多个属性，但我只想选择优先级值最低的一个:

a1 n1 t1 p0 *
a2 n1 t2 p1 
a3 n2 t2 p1 *
a4 n2 t3 p2  

这是我正在处理的基本查询，此时我也遇到了困难:

   SELECT * 
     FROM a 
LEFT JOIN t ON a.type_id = t.id 
 GROUP BY node_id

我的第一个想法是使用聚合 MIN，但是我在将 node_id 的最低优先级与正确的属性相匹配时遇到了问题。

Answer 1

这个问题是“每组最大 n 个”问题的变体，但您正在寻找最少而不是最大，并且您的标准在查找表中(类型) 而不是原理表 (Attributes)。

所以您希望 Attributes 中的行 (a1) 具有相同 node_id 的其他行不会与较低的优先级相关联。

SELECT a1.*
FROM Attributes a1 INNER JOIN Type t1 ON (a1.type_id = t1.id)
LEFT OUTER JOIN (
  (Attributes a2 INNER JOIN Type t2 ON (a2.type_id = t2.id))
  ON (a1.node_id = a2.node_id AND t1.priority > t2.priority)
WHERE a2.node_id IS NULL;

请注意，这可能会导致平局。如果两个属性引用具有相同优先级的类型，您还没有描述如何解决联系。也就是说，在下面的例子中，应该选择哪些属性？

a1 n1 t1 p0 
a2 n1 t1 p0 
a3 n2 t2 p1 
a4 n2 t3 p1 

PS:希望您不介意我在您的问题中添加了“每组最伟大”标签。单击该标签可查看我已类似标记的关于 SO 的其他问题。