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

Simplified table structures, all INT columns and no PKs outside of the identity columns:

简化的表结构，所有的INT列和没有PKs在标识列之外:

Nodes (n) table: id

节点(n)表:id

Attributes (a) table: id, node_id, type_id

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

Type (t) table: id, priority

类型(t)表:id，优先级

I'm trying to select a set of attributes, each of which has the lowest type.priority for its respective node. Though there are multiple attributes per node_id, I only want to select the one with the lowest priority value:

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

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

This is the basic query that I'm working from, at which point I'm also getting stuck:

这是我工作的基本问题，在这一点上我也被卡住了:

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

My first thought was to use an aggregate, MIN, but I'm then having problems matching up the lowest priority for a node_id with the correct attribute.

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

2 个解决方案

#1

This question is a variation of the "greatest-n-per-group" problem, but you're looking for the least instead of the greatest, and your criteria are in a lookup table (Type) instead of the principle table (Attributes).

这个问题是“每个组最大”问题的变体，但是您要查找的是最小而不是最大的问题，并且您的标准是在查找表(类型)中，而不是在主表(属性)中。

So you want the rows (a1) from Attributes such that no other row with the same node_id is associated with a lower priority.

因此，需要从属性中获取行(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;

Note that this can result in ties. You haven't described how you would resolve ties if two Attributes referenced Types with the same priority. In other words, in the following examples, which attributes should be selected?

注意，这会导致领带。如果两个属性引用的类型具有相同的优先级，那么您还没有描述如何解决关系。换句话说，在下面的例子中，应该选择哪些属性?

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

PS: I hope you don't mind I added the "greatest-n-per-group" tag to your question. Click that tag to see other questions on SO that I have tagged similarly.

PS:我希望你不介意我在你的问题中加入了“每组最伟大的人”的标签。单击该标记查看其他问题，以便我进行类似的标记。

#2

Use tie-breaker query (not tested):

使用连接断路器查询(未测试):

SELECT      n.*, a.*
FROM        Nodes n
LEFT JOIN   Attributes a
        ON  a.id = (SELECT      x.id --//TOP 1 x.id
                    FROM        Attributes x
                    INNER JOIN  Type t
                            ON  x.type_id = t.id
                    WHERE       x.node_id = n.id
                    ORDER BY    t.priority ASC,
                                --//just in case there are 2 attributes 
                                --//with the same priority, order also on x.id
                                x.id ASC
                    LIMIT 1
                    )

#1