我已经看过这两个问题了：
> 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