带有子查询的 SQL GROUP BY

debugcn 发表于 Dev

斯特罗姆

订单历史 ID、IdOrder、状态

和一个表 Order OrderId , ConfirmationDate , LastState

订单历史示例

1, 1000, 50
2, 1000, 60
3, 1000, 90
4, 1001, 50
5, 1001, 90

命令

1000, '2018-03-01', 90
1001, '2018-03-01', 90

我会接受所有在状态 60 中从未通过的订单。我尝试了很多查询，但我仍然得到状态 60 的订单。

SELECT oh.idOrdine  
FROM OrderHistory  oh 
where oh.state not in (60) 
and oh.OrderId in (Select OrderID 
                   From Orders 
                   where ConfirmationDate >= '2017-03-01' 
                     and state= 90) 
group by oh.OrderId 
order by oh.OrderId desc


SELECT * 
FROM Orders o 
  join OrderHistory oh on oh.OrderId = o.ORderId 
where o.ConfirmationDate>= '2017-03-01' 
  and o.state= 90 
  and oh.OrderId in (SELECT OrderId 
                     from OrderHistory 
                     WHERE State not in (60) 
                       and State in (95) 
                     group by ORderID)

怎么了？

卡尔达萨

相反not in，您应该使用Not Exists. 通过使用 Not in，您只需消除该行，而不是该 OrderID 的所有行。

SELECT * 
FROM Orders o 
  join OrderHistory oh on oh.OrderId = o.ORderId 
  where o.ConfirmationDate>= '2017-03-01' 
  and o.state= 90 
  and oh.OrderId in (SELECT oh1.OrderId 
                     from OrderHistory oh1
                     WHERE Not Exists (select *
                                       From OrderHistory oh2
                                       where oh2.OrderId=oh1.OrderId and 
                                       oh2.State=60)
                      )

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。