mysql 嵌套查询不适用于 AND 条件

Question

我不明白这一点:以下两个查询几乎相同，唯一不同的是“and o.shop_id=1”部分，但它们不会产生预期的结果。

第一个查询:

select count(*) as count
from ek_order o        
where 1


and  
(
  select
  s.code
  from ek_order_status s
  inner join ek_order_has_order_status h on h.order_status_id=s.id
  where o.id=h.order_id

  order by h.date DESC
  limit 0,1

) in ('preparing_order')

查询结果:1

第二个查询:

select count(*) as count
from ek_order o        
where 1

and o.shop_id=1


and  
(
  select
  s.code
  from ek_order_status s
  inner join ek_order_has_order_status h on h.order_status_id=s.id
  where o.id=h.order_id

  order by h.date DESC
  limit 0,1

) in ('preparing_order')

查询结果:0(我期望 1!)

我的架构结构如下所示:

ek_order:

• id:PK
• shop_id:可为空 fk
• ...其他与此处不相关的字段

ek_order_has_order_status:

• id:pk(我注意到不需要，但为时已晚......)
• 订单 ID:fk
• order_status_id:fk
• 日期:日期时间
• ...其他与此处不相关的字段

ek_order_status:

• id:PK
• 代码:str
• ...其他与此处不相关的字段

数据

ek_order_has_order_status 表中有一行如下所示:

• id:182
• 订单 ID:283，
• order_status_id:7(这是 ek_order_status 中的“preparing_order”代码)

在ek_order表中我们可以发现shop_id确实是1:

• id:283
• shop_id:1

所以我不明白为什么第二个查询返回 0 而不是 1。我分开测试了查询的所有位，它们似乎有效(我首先测试了内部查询，然后测试了外部查询，均成功)，但是添加“and o.shop_id=1”部分会使整个查询失败.

有人知道为什么吗？和/或进行修复。(我可以进行两次查询，但为了提高我的 sql 知识，我更愿意了解其底层)。

Ps:基本上，我试图获取包含“preparing_order”状态的订单数量作为其最终/最新状态，因为知道一个订单可以有多个状态。

Answer 1

在第二个查询中:

SELECT COUNT(*) as count
FROM ek_order o        
WHERE 1
AND o.shop_id=1
...

您正在对整个 ek_order 表进行计数，因此谈论单个记录的 shop_id 值甚至没有意义。我怀疑您的意图是，如果您找到 shop_id 值为 1 的任何记录，那么这将作为逻辑 true 传递。如果是这样，那么您可以使用EXISTS:

SELECT COUNT(*) as count
FROM ek_order o        
WHERE 1
AND EXISTS (SELECT 1 FROM ek_order WHERE shop_id=1)
AND ...

如果这不起作用，那么另一种选择是采用 shop_id 的最大值:

SELECT COUNT(*) as count
FROM ek_order o        
WHERE 1
AND MAX(shop_id) = 1
AND ...

如果您确定该表只有一条记录并且您只想将其值与计数一起内联，那么这是有意义的。