写了如下的sql语句,except前后半句分别执行均成功,只是和起来就有问题了
(SELECT Distinct AgentName FROM exam.agent)
EXCEPT
(SELECT Distinct AgentName, `SellingAgent` FROM exam.agent,exam.homesale WHERE SellPrice>100000 AND agent.AgentID=homesale.SellingAgent);
报错如下:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXCEPT
(SELECT Distinct AgentName, `SellingAgent` FROM exam.agent,exam.homesal' at line 2
请教问题原因!
无论如何,问题已经解决,换个思考角度,
SELECT Distinct AgentName, SellingAgent
FROM exam.agent,exam.homesale
WHERE
(SELECT MAX(SellPrice)
FROM exam.homesale
WHERE agent.AgentID=homesale.SellingAgent)<1000000
AND agent.AgentID=homesale.SellingAgent;