我有两个表:
+-----------------------+
| 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