使用案例语句中的组中最小值替换值

Question

我有一个表table1

pid_x   pid_y   count
  a       b       5
  a       c      10
  b       a      20
  c       e       3
  d       g       7
  e       f       10
  e       b       20

我也有一个master_table 详细信息

master_id          pid     price_pid
    a               a1       10
    a               b1       20
    a               c1       30
    b               d1       40
    b               e1       50
    c               f1       60
    d               g1       70

对于pid_x中的值等于master_id中的值，我想用最低定价的 pid替换该pid_x和pid_y。

即最终结果。

pid_x   pid_y    count
  a1      d1      5
  a1      f1      10
  d1      a1      20
  f1      e       3
  g1      g       7

等等。

我想要一个案例陈述。

select pidx,pid_y,count from table1 A INNER JOIN master_table m1 ON CASE WHEN A.pid_x = B.master_id then replace that pid_x with cheapest pid dor that mater_id......

Answer 1

我真的希望MySQL支持CTES，以避免两次重写相同的子查询。但是，请尝试以下操作：

SELECT COALESCE(t2.pid, t1.pid_x) pid_x,
  COALESCE(t3.pid, t1.pid_y) pid_y,
  SUM(t1.count) COUNT
FROM table1 t1
LEFT OUTER JOIN
  (SELECT t1.*
  FROM master_table t1
  INNER JOIN
    (SELECT master_id, MIN(pid) pid FROM master_table GROUP BY master_id
    ) t2
  ON t1.master_id  = t2.master_id
  AND t1.pid       = t2.pid
  ) t2 ON t1.pid_x = t2.master_id
LEFT OUTER JOIN
  (SELECT t1.*
  FROM master_table t1
  INNER JOIN
    (SELECT master_id, MIN(pid) pid FROM master_table GROUP BY master_id
    ) t2
  ON t1.master_id  = t2.master_id
  AND t1.pid       = t2.pid
  ) t3 ON t1.pid_x = t3.master_id
GROUP BY COALESCE(t2.pid, t1.pid_x),
  COALESCE(t3.pid, t1.pid_y) ;