如何使用case语句和min()与group？

Question

执行以下查询时

SELECT CASE
         WHEN spd.IS_MAIN_DEFECT='Y' 
           THEN spd.piece_Defect_num_id 
         ELSE min(spd.PIECE_DEFECT_NUM_ID)
       END AS defect
  FROM piece P,
       STY_PIECE_DEFECT spd,
       STY_DEFECT_CATALOGUE sdc,
       piece_history ph,
       piece_history_out pho,
       PLANT_CONFIG pc
  (...inner join and where clause)
  GROUP BY p.PIECE_ID,
           CASE
             WHEN spd.IS_MAIN_DEFECT='Y'
               THEN spd.piece_Defect_num_id
             ELSE min(spd.PIECE_DEFECT_NUM_ID)
           end 

似乎是错误

ORA-00934:这里不允许组函数

我想，组中有错误min()。

我该如何解决这个问题？

Answer 1

您必须像下面的analytical MIN()那样使用without group by函数

     SELECT distinct CASE WHEN spd.IS_MAIN_DEFECT='Y' 
      THEN spd.piece_Defect_num_id 
       ELSE min(spd.PIECE_DEFECT_NUM_ID) over () END AS defect
      FROM piece P , STY_PIECE_DEFECT spd ,STY_DEFECT_CATALOGUE sdc ,piece_history ph 
      , piece_history_out pho, PLANT_CONFIG pc
      (...inner join and where clause)

Answer 2

我理解您希望将PIECE_DEFECT_NUM_ID保留在与IS_MAIN_DEFECT = 'Y'一起的行中，并获得非主缺陷线的MIN值。

对于这种情况，最简单的解决方案是，在PIECE_ID上使用IS_MAIN_DEFECT = 'Y' - you组并计算min(PIECE_DEFECT_NUM_ID)，每个PIECE_ID, IS_MAIN_DEFECT只有一行。

行组的MIN等于原始值。

select PIECE_ID,IS_MAIN_DEFECT,
min(PIECE_DEFECT_NUM_ID) PIECE_DEFECT_NUM_ID
from tab
group by PIECE_ID, IS_MAIN_DEFECT
order by 1,2 desc;

如果您可能有更多的行(相同的PIECE_ID)与主标志，简单地选择它们取消分组，并添加分组的非主要部分使用UNION ALL。

select PIECE_ID,IS_MAIN_DEFECT,PIECE_DEFECT_NUM_ID
from tab
where IS_MAIN_DEFECT = 'Y'
UNION ALL
select PIECE_ID,IS_MAIN_DEFECT,
min(PIECE_DEFECT_NUM_ID) PIECE_DEFECT_NUM_ID
from tab
where nvl(IS_MAIN_DEFECT,'N') != 'Y'
group by PIECE_ID, IS_MAIN_DEFECT