时间戳

首页 > 解决方案 > 带有子查询的 SQL GROUP BY

sql - 带有子查询的 SQL GROUP BY

问题描述

订单历史 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)

怎么了？

标签： sql

解决方案

而不是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)
                      )

推荐阅读