MYSQL:不存在语法错误的地方
MYSQL:不存在语法错误的地方
提问于 2014-02-15 22:13:52
我想知道如何在mysql中使用WHERE NOT EXISTS，因为下面的query.It给了mysql语法错误，我尝试了所有可能的方法，但是没有任何帮助。
SELECT *
FROM (
(
SELECT l.pid
FROM lineitem l
WHERE l.oid IN (
SELECT o1.oid
FROM customer c
JOIN (
SELECT o.oid,
o.cid
FROM orders o
) o1
ON c.cid=o1.cid
AND c.city='Newark'
)
GROUP BY l.pid
)
WHERE not exists (
SELECT l.pid
FROM lineitem l
WHERE l.oid IN (
SELECT o1.oid
FROM customer c
JOIN (
SELECT o.oid,
o.cid
FROM orders o
)o1
ON c.cid=o1.cid
AND c.city<>'Newark'
)
)
)我的表格是:顾客(名字，名称，城市，州)订单( pid，pid，date)产品(pid，价格，产品名称)线项目(盖子，号码，pid，pid，总价)
我想选择所有仅由Newark.My用户购买的产品，逻辑是(选择客户在纽瓦克购买的产品)-(选择所有其他客户购买的产品).I am使用WHERE NOT EXISTS，因为.I关键字在mysql中不可用。
回答 2
Stack Overflow用户
回答已采纳
发布于 2014-02-15 22:31:56
这里有一个更好的解决方案：
SELECT ProductID FROM (
SELECT p.pid ProductID
FROM lineitem l
JOIN product p
ON l.pid = p.pid
JOIN orders o
ON l.oid = o.oid
WHERE o.cid IN (
SELECT c.cid
FROM customer c
WHERE c.city = 'Newark'
)
) t1
WHERE ProductID NOT IN (
SELECT p.pid
FROM lineitem l
JOIN product p
ON l.pid = p.pid
JOIN orders o
ON l.oid = o.oid
WHERE o.cid IN (
SELECT c.cid
FROM customer c
WHERE c.city <> 'Newark'
)
);我还相信，由于嵌套查询较少，所以性能会更好。
查询中的问题是括号。你是说
SELECT *
FROM (
( ... )
WHERE NO EXISTS
( ... )
);而你应该说
SELECT *
FROM ( ... )
WHERE NO EXISTS ( ... );由于它可以工作，下面进一步简化(删除第2部分子查询中的派生表t1和JOIN product p )：
SELECT p.pid ProductID
FROM lineitem l
JOIN product p
ON l.pid = p.pid
JOIN orders o
ON l.oid = o.oid
WHERE o.cid IN (
SELECT c.cid
FROM customer c
WHERE c.city = 'Newark'
)
AND p.pid NOT IN (
SELECT l.pid
FROM lineitem l
JOIN orders o
ON l.oid = o.oid
WHERE o.cid IN (
SELECT c.cid
FROM customer c
WHERE c.city <> 'Newark'
)
);Stack Overflow用户
发布于 2014-02-15 22:59:54
Select l.pid from lineitem l
inner join Orders o on l.oid = o.oid
inner join Customers c on o.cid = c.cid and c.city = 'Newark'
where not in
(
Select ol.pid from lineitem ol
inner join Orders oo on ol.oid = oo.oid
inner join Customers oc on oo.cid = oc.cid and oc.city <> 'Newark'
)我想..。如果不存在，则需要相关的子查询。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/21804352
复制相关文章
相似问题
腾讯云开发者
