MySQL组通过排序/另一列的优先级 [英] MySQL group by with ordering/priority of another column

问题描述

我已经看过这两个问题：

•   SELECT * 
  从测试
   JOIN（SELECT'west'AS方向，4 AS重量
   UNION 
   SELECT'north'，3 
   UNION 
   SELECT'south'，2 
   UNION 
   SELECT'east'，1）优先权
   ON priority.direction = test.direction 
   JOIN（
   SELECT route，MAX（weight）AS weight 
   FROM test 
   JOIN（SELECT'west'AS方向，4 AS权重
   UNION 
   SELECT'north'，3 
   UNION 
   SELECT'south'，2 
   UNION 
   SELECT'east'，1）优先权
   ON priority.direction = test.direction 
   GROUP BY路由
  ）AS t1 
   ON t1.route = test.route 
   AND t1.weight = priority.weight 
    

  
  

  I've already looked at these two questions:

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

  However both of them use an aggregate function MAX in order to obtain the highest or filled in value, which doesn't work for my case.

  For the purposes of this question, I've simplified my situation. Here is my current data:

  I'd like to obtain the operator name for each route, but with respect to direction of travel (i.e. ordering or "preferring" values). This is my pseudo-code:

  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`
  

  My current SQL query is:

  SELECT route, operatorName
  FROM test
  GROUP BY route
  

  This gives me the grouping, but wrong operator for my purposes:

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

  I have tried applying an ORDER BY clause but GROUP BY takes precedence. What my desired result is:

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

  I cannot do MAX() here as "north" comes before "south" in alphabetical order. How do I explicitly state my preference/ordering before the GROUP BY clause is applied?

  Also keep in mind that empty strings are not preferred.

  Please note that this is a simplified example. The actual query selects a lot more fields and joins with three other tables, but there are no aggregate functions in the query.

  解决方案

  You can use that MAX example, you just needed to "fake it". See here: 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组通过排序/另一列的优先级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！