正如标题所说,我正在努力寻找已经下了订单但还没有付款的客户。
我有三张桌子;
Customers, Payments, Orders到目前为止,我的sql给了我(嵌套查询)所有没有付款的客户,然后外部查询尝试用订单连接所有客户,并检查这些客户是否不在我的内表中?
SELECT customerWOpayments.customerNumber FROM
ClassicModels.Customers c
INNER JOIN ClassicModels.Orders o ON c.customerName = o.customerNumber
NOT IN
(SELECT distinct c.customerNumber
FROM ClassicModels.Customers c
LEFT OUTER JOIN ClassicModels.Payments p ON c.customerNumber = p.customerNumber
WHERE p.customerNumber IS NULL) customerWOpayments;我在第8行收到mysql语法错误,但找不到原因?
发布于 2012-02-12 12:26:13
这应该返回有订单但没有匹配付款的客户,假设您在原始示例中加入的所有键都是正确的(例如,c.customerName = o.customerNumber似乎可疑)。
SELECT c.customerNumber
FROM ClassicModels.Customers c
INNER JOIN ClassicModels.Orders o
ON c.customerNumber = o.customerNumber
LEFT OUTER JOIN ClassicModels.Payments p
ON c.customerNumber = p.customerNumber
WHERE p.customerNumber IS NULL;发布于 2012-02-12 12:23:38
基本上,您错过了WHERE子句。而且你的问题缺乏信息。请提供您的表的Schema。谢谢!
试试这个:
你确定这个条件是ON c.customerName = o.customerNumber吗?
SELECT customerWOpayments.customerNumber
FROM ClassicModels.Customers c INNER JOIN ClassicModels.Orders o
ON c.customerName = o.customerNumber -- Please check this out
WHERE o.customerNumber NOT IN
(SELECT distinct c.customerNumber
FROM ClassicModels.Customers c LEFT JOIN ClassicModels.Payments p
ON c.customerNumber = p.customerNumber
WHERE p.customerNumber IS NULL);或无子查询
SELECT a.*
FROM Customers a INNER JOIN Orders b ON
a.CustomerName = b.CustomerNumber -- Please check this line
LEFT JOIN Payments c ON
b.CustomerNumber = c.CustomerNumber
WHERE c.CustomerNumber IS NULL我认为这是a.CustomerName = b.CustomerNumber上的拼写错误,而不是a.CustomerNumber = b.CustomerNumber
我不能确切地说出这是否是因为您没有为表的schema提供一些dummy records。
希望这能有所帮助。
发布于 2012-02-12 13:09:49
与其他解决方案不同,当客户有多个订单时,此解决方案不会产生重复的客户编号。
SELECT C.customerNumber
FROM ClassicModels.Customers C
WHERE
EXISTS(
-- customer has orders
SELECT *
FROM ClassicModels.Orders AS O
WHERE O.customerNumber = C.customerNumber
)
AND NOT EXISTS(
-- customer does not have payments
SELECT *
FROM ClassicModels.Payments P
WHERE P.customerNumber = C.customerNumber
)https://stackoverflow.com/questions/9246410
复制相似问题