MySQL组与另一列的排序/优先级

我已经看过这两个问题了：

> Grouping by Column with Dependence on another Column
> MySQL GROUP BY with preference

但是它们都使用聚合函数MAX来获得最高值或填充值,这对我的情况不起作用.

出于这个问题的目的,我简化了我的情况.这是我目前的数据：

我想获得每条路线的操作符名称,但是关于旅行方向(即订购或“优先”价值).这是我的伪代码：

if(`direction` = 'west' AND `operatorName` != '') then select `operatorName`
else if(`direction` = 'north' AND `operatorName` != '') then select `operatorName`
else if(`direction` = 'south' AND `operatorName` != '') then select `operatorName`
else if(`direction` = 'east' AND `operatorName` != '') then select `operatorName`

我当前的SQL查询是：

SELECT route,operatorName
FROM test
GROUP BY route

这给了我分组,但我的目的是错误的运算符：

route | operatorName
--------------------
  95  | James
  96  | Mark
  97  | Justin

我尝试过应用ORDER BY子句但GROUP BY优先.我想要的结果是什么：

route | operatorName
--------------------
  95  | Richard
  96  | Andrew
  97  | Justin

我不能在这里做MAX(),因为“north”按字母顺序排在“south”之前.在应用GROUP BY子句之前,如何明确说明我的偏好/排序？

另请注意,空字符串不是首选.

请注意,这是一个简化的示例.实际查询选择了更多字段并与其他三个表连接,但查询中没有聚合函数.

最佳答案

你可以使用那个MAX例子,你只需要“伪造它”.见：http://sqlfiddle.com/#!2/58688/5

SELECT *
FROM test
JOIN (SELECT 'west' AS direction,4 AS weight
      UNION
      SELECT 'north',3
      UNION
      SELECT 'south',2
      UNION
      SELECT 'east',1) AS priority
  ON priority.direction = test.direction
JOIN (
      SELECT route,MAX(weight) AS weight
      FROM test
      JOIN (SELECT 'west' AS direction,4 AS weight
            UNION
            SELECT 'north',3
            UNION
            SELECT 'south',2
            UNION
            SELECT 'east',1) AS priority
        ON priority.direction = test.direction
      GROUP BY route
) AS t1
  ON t1.route = test.route
    AND t1.weight = priority.weight

MySQL组与另一列的排序/优先级

猜你在找的MySQL相关文章