我有两个表:

+-----------------------+
| Tables_in_my_database |
+-----------------------+
| orders                |
| orderTaken            |
+-----------------------+


在订单中,有属性

orderId, orderName, isClosed and orderCreationTime.


为了订购,有一些属性

userId, orderId and orderStatus.


比方说什么时候

orderStatus = 1 --> the customer has taken the order
orderStatus = 2 --> the order has been shipped
orderStatus = 3 --> the order is completed
orderStatus = 4 --> the order is canceled
orderStatus = 5 --> the order has an exception


基本上,我的项目的运行机制是这样的:具有唯一userId的用户将能够从网页上获取订单,其中每个订单也具有自己的唯一orderId。完成后,orderTaken表将记录userId,orderId并初始设置orderStatus =1。然后,商店将根据各种情况更新orderStatus。商店更新了isClosed = 1之后,无论用户是否接受,该订单都不会显示(没有意义,但在查询中只是isClosed == 0)。

现在,我想构建一个网页,该网页将显示用户尚未使用的新订单(应该是未在该用户的userId下的orderTaken表中记录其orderId的订单)以及用户已经使用了显示的orderStatus,但是orderStatus不是4或5,而是按orderCreationTime DESC分组(是的,如果我没有orderTakenTime,也许就没有意义了,但让我们保持这种方式),例如:

OrderId 4
Order Name: PetPikachu
orderStatus = 1
CreationTime: 5am

OrderId 3
Order Name: A truck of hamsters
orderStatus = 3
CreationTime: 4am

OrderId 2
New order
Order Name: Macbuk bull
CreationTime: 3am

OrderId 1
Order Name: Jay Chou's Album
orderStatus = 2
CreationTime: 2am


我根据我所学的知识编写了此查询:

SELECT * FROM orders A WHERE A.isClosed == '0' FULL OUTER JOIN orderTaken B  WHERE B.userId = '4' AND (B.orderStatus<>'4' OR B.orderStatus<>'5') ORDER BY A.orderCreationTime DESC;


显然,此查询无法正常工作,但我担心

ON A.orderId = B.orderId


从那时起,返回的表将消除orderTaken B中未记录orderId的新订单。我也尝试过NOT NOT子句,例如

SELECT * FROM orders A WHERE A.isClosed = '0' AND A.orderId NOT IN (SELECT orderId FROM orderTaken B WHERE B.userId = '$userId' AND (B.orderStatus='4' OR B.orderStatus='5')) ORDER BY creationTime DESC;


该查询有效,但在返回的表中没有来自orderTaken B的orderStatus字段。我当时正在考虑在此查询之后添加另一个JOIN orderTaken B子句以从B获取字段,但是我认为这不是编写查询的好方法。

我只是想将“ NOT IN”和“ FULL JOIN”组合在一起。有人可以帮我吗?谢谢!

最佳答案

您似乎想在orders中找到未分配给用户的记录(即在orderTaken中没有相关记录)加上分配给用户的记录,但是orderStatus不是4或5,

这样就不需要完全的外部联接,因为orderTaken中将没有记录,而orders中将没有相关记录。 Left inner join可以用于查找orders中的所有记录,on子句将包含来自orderTaken的相关记录中的数据,然后where子句可以过滤掉其他用户采取的命令,或者其中orderStatus为4或5:

SELECT o.*, ot.userID, ot.orderStatus
FROM orders o
LEFT JOIN orderTaken ot
ON ot.orderID = o.orderID
WHERE o.isClosed = 0
AND (ot.userID IS NULL OR ot.userID = $userID AND ot.orderStatus NOT IN (4,5))
ORDER BY o.creationTime DESC

09-18 18:37