数据库 求一个 MySQL 查询语句

kai209209 · 2018年04月15日 · 最后由 kai209209 回复于 2018年04月15日 · 742 次阅读

各位大佬,我现在有2个表,一个是orders,一个是packages。 下面的语句是找出orders的state为4,并且order对应的packages状态也为4的所有订单

select
  orders.*
from
  orders, packages
where
  orders.id = packages.order_id
and
  orders.state = 4
and
  packages.state = 4
group by
  packages.order_id
having
  count(packages.id) > 1

但是这个语句找出来的结果是packages包含了state为4所有订单,这些orders对应的所有包裹里除了packages.state为4,也可能还包含了其他状态,我想找出orders对应的所有packages.state里状态只有4的订单,请问一下这个我怎么写这个sql啊? 麻烦大家了

共收到 5 条回复

按照你的描述你写的就是对的

select o.* from orders o left join packages p on o.id = p.order_id where o.status = 4 and p.status = 4

sum case when

对每个 order 而言,找它的 package 的数量和状态为 4 的 package 的数量相等的就行了。

SELECT o.*
FROM orders o
JOIN packages p ON p.order_id = o.id
WHERE o.state = 4
GROUP BY o.id
HAVING COUNT(p.id) = SUM(IF(p.state = 4, 1, 0))
;

为了更贴近 SQL 标准(也方便以后弃坑 MySQL),把 if 换成 case 更好:SUM(CASE WHEN p.state = 4 THEN 1 ELSE 0 END) 。算是补充炮哥言简意赅的回答吧。

谢谢两位 @hooopo @darkbaby123 其实我也想到一个比较流氓的方法的

Order.joins(:packages).where(state: 4).group(:id).having("count(packages.id) > 1 and max(packages.state) = 4 and min(packages.state) = 4")

  => SELECT `orders`.* FROM `orders` INNER JOIN `packages` ON `packages`.`order_id` = `orders`.`id` WHERE `orders`.`state` = 4 GROUP BY `orders`.`id` HAVING (count(packages.id) > 1 and max(packages.state) = 4 and min(packages.state) = 4)
需要 登录 后方可回复, 如果你还没有账号请点击这里 注册