ORA-00907:缺少右括号-嵌套选择NOT IN

Question

很抱歉再次发布此主题。这方面有4页的结果，很难深入了解具体的解决方案。

有人能看到这个SQL出了什么问题吗？

SELECT evntmst_id, count(evntmst_id) AS idcount
FROM evntcond 
WHERE EVNTMST_ID NOT IN (
SELECT DISTINCT c.EVNTMST_ID 
FROM evntcond c 
LEFT JOIN evntmst m 
ON c.EVNTMST_ID = m.EVNTMST_ID
WHERE m.EVNTMST_TYPE IN (1, 7) -- Group or Subset
AND m.EVNTMST_ID IS NOT NULL   -- remove data integrity issues
GROUP BY c.EVNTMST_ID
ORDER BY c.EVNTMST_ID
)
GROUP BY EVNTMST_ID
ORDER BY count(evntmst_id) Desc

这将产生ORA-00907: missing right parenthesis

如果我使用嵌套的SELECT DISTINCT并将其替换为1,2，那么一切都很好。

SELECT evntmst_id, count(evntmst_id) AS idcount
FROM evntcond 
WHERE EVNTMST_ID NOT IN (
1,2
)
GROUP BY EVNTMST_ID
ORDER BY count(evntmst_id) Desc

如果我自己运行嵌套查询，一切都会很好。

SELECT DISTINCT c.EVNTMST_ID 
FROM evntcond c 
LEFT JOIN evntmst m 
ON c.EVNTMST_ID = m.EVNTMST_ID
WHERE m.EVNTMST_TYPE IN (1, 7) -- Group or Subset
AND m.EVNTMST_ID IS NOT NULL   -- remove data integrity issues
GROUP BY c.EVNTMST_ID
ORDER BY c.EVNTMST_ID

Oracle的特点。这是个虫子吗？

Answer 1

这看起来太复杂了。我想你打算：

SELECT c.evntmst_id, COUNT(c.evntmst_id) AS idcount
FROM evntcond c
WHERE NOT EXISTS (SELECT 1
FROM evntmst m 
WHERE m.EVNTMST_ID = c.EVNTMST_ID AND
m.EVNTMST_TYPE IN (1, 7) 
)
GROUP BY EVNTMST_ID
ORDER BY count(evntmst_id) DESC;

这会过滤掉具有相应"1"或"7"记录的EVNTMST_ID。

我应该补充一点，在您的查询中没有明显的语法错误。