Oracle SQL如何在case中绕过group by子句

Question

我在返回唯一记录时遇到了问题，因为必须对特定字段使用GROUP BY，因为它在CASE WHEN子句中。下面是查询：

SELECT L.COLUMN1                 AS COL1,
ROUND(L.COLUMN2 * 1000) AS COL2,
CASE
  WHEN L.COLUMN3 >= 20 AND L.COLUMN4 = 3
  THEN 'Y'
  ELSE 'N'
END AS COMPLETED,
CASE
  WHEN COUNT(DISTINCT VC.COLUMN5)= 0
  OR (COUNT(DISTINCT VC.COLUMN5) > 0
  AND SUM(VC.COLUMN10)              =SUM(VC.COLUMN11))
  THEN 'Y'
  WHEN COUNT(DISTINCT VC.COLUMN5) > 0      
  AND SUM(VC.COLUMN10)             <>SUM(VC.COLUMN11)
  AND VC.COLUMN8 IN (SELECT S.CODE_ID FROM TABLE5 S
  WHERE S.COLUMN6 = 'C' AND S.COLUMN7 = 'SVS')
  THEN 'S'
  ELSE 'N'
END AS YNS,
NVL(
CASE
 WHEN COUNT(DISTINCT LL.COLUMN12)> 0
 THEN 'N'
 ELSE 'Y'
 END,'Y') AS YN
FROM TABLE1 L
 LEFT OUTER JOIN TABLE2 VC
 ON L.COLUMN1 = VC.COLUMN5
 LEFT OUTER JOIN TABLE3 LL
 ON L.COLUMN9 = LL.COLUMN9
 AND LL.COLUMN1 = L.COLUMN1
WHERE L.OIIN='O'
 AND L.COLUMN3 < 90
GROUP BY L.COLUMN1,
 L.COLUMN2 ,
 L.COLUMN3,
 L.COLUMN4, 
 VC.COLUMN8;

这里导致重复的部分是

WHEN COUNT(DISTINCT VC.COLUMN5) > 0      
  AND SUM(VC.COLUMN10)             <>SUM(VC.COLUMN11)
  AND VC.COLUMN8 IN (SELECT S.CODE_ID FROM TABLE5 S
  WHERE S.COLUMN6 = 'C' AND S.COLUMN7 = 'SVS')
  THEN 'S'

因为它在最后有一个由VC.COLUMN8组成的组。如果我删除group by，那么我得到的错误是这不是一个group by表达式。如何删除group by，并在表中仅返回COL1的唯一记录，如果COL1的任何记录满足以下要求，我仍然会得到一个"S“填充？

WHEN COUNT(DISTINCT VC.COLUMN5) > 0      
  AND SUM(VC.COLUMN10)             <>SUM(VC.COLUMN11)
  AND VC.COLUMN8 IN (SELECT S.CODE_ID FROM TABLE5 S
  WHERE S.COLUMN6 = 'C' AND S.COLUMN7 = 'SVS')
  THEN 'S'

Answer 1

您应该了解，在GROUP BY子句中没有VC.COLUMN8的查询中，查询的每个输出行可能有多个VC.COLUMN8值。因此，查询无法解析VC.COLUMN8是否在由子查询定义的列表中。

您必须解决这个逻辑问题才能知道如何继续--您的意思是要测试子查询中是否至少有一个VC.COLUMN8的值，或者所有值都必须在子查询中？