mysql - 删除多个连接上的重复行

Question

我有以下查询

select m.movementid, m.orderid, m.pickupdate, m.pickupnotes, 
b.bin_size, b.bin_type, 
l.address, l.suburb, l.postcode, l.state, 
if(rs.run_action = 'Pick', if (r.run_state = 'Active' or r.run_state='Ready', 1, 0), 0) as active_on_pick
from bb_movement m
inner join bb_bins b on b.bin_id = m.bin_id
inner join bb_location l on l.locationid = m.locationid
inner join bb_runsheet rs on rs.movement_id = m.movementid
inner join bb_run r on r.run_id = rs.run_id
where m.mvtstate = 'Active'
order by m.locationid, m.pickupdate

我想要生成一个结果，其中 active_on_pick 列包含每个 moveid 的 0 或 1。当给定的 moveid 添加到 bb_run 时，bb_runsheet 表中存在一条记录(一个bb_run可以有很多bb_runsheet记录，其中主要包含一个movementid)

我遇到的问题是，当运动正在运行时(即:具有与条件匹配的 bb_run 记录和 bb_runsheet 表中的条目)，我在结果数据集中得到 2 行 - 例如:

mvt_id active_on_pick
21     0
21     1

我想要的只是 21 和 1。我尝试了子查询和其他变体(例如按 moveid 分组)，但似乎可以解决它。我正在使用mysql

Answer 1

我认为，如果您将一些逻辑移动到连接中(更改为左连接)，您可以获得答案，将更多逻辑移动到另一个左连接中

select m.movementid, m.orderid, m.pickupdate, m.pickupnotes, 
b.bin_size, b.bin_type, 
l.address, l.suburb, l.postcode, l.state, 

IF(r.run_id IS NULL, 0, 1) as active_on_pick

from bb_movement m
inner join bb_bins b on b.bin_id = m.bin_id
inner join bb_location l on l.locationid = m.locationid

LEFT join bb_runsheet rs 
 ON rs.movement_id = m.movementid
    AND rs.run_action = 'Pick'

LEFT join bb_run r 
  ON r.run_id = rs.run_id
    AND (r.run_state = 'Active' or r.run_state='Ready')

where m.mvtstate = 'Active'
order by m.locationid, m.pickupdate