Oracle SQL - 在组内重复相同的列值

Question

我需要一些帮助来调整以下查询

select 
    data.smalldate, 
    mip.mip_step_description, 
    error_code.error_code_en, 
    count(case when (error_code is null and quality_plan is null) then data.part_serial_number end) as "Input", 
    count(case when error_code is not null then data.part_serial_number end) as "Defects"
from Data

left join MIP
On data.equipment = mip.equipment

left join error_code
on data.error_code = error_code.error_code_sn 

group by data.smalldate, mip.mip_step_description, error_code.error_code_en

order by data.smalldate, mip.mip_step_description, count(data.part_serial_number) desc

正如您在 select 语句中所看到的，我在计数函数中使用了 case 语句。这很好用。数据输出看起来像这样，

Date    MIP_Desc    Error_Code    Input    Defects
1/1/2011    MIP Z    (null)       100      0
1/1/2011    MIP Z    A            0        10
1/1/2011    MIP Z    B            0        15

我想在具有相同日期和 MIP_Desc 的所有行的输入列中填写相同的输入值。

输出应该是这样的

Date    MIP_Desc    Error_Code    Input    Defects
1/1/2011    MIP Z    (null)       100      0
1/1/2011    MIP Z    A            100      10
1/1/2011    MIP Z    B            100      15

Answer 1

这有帮助吗？ （未经测试）：

SELECT smalldate, mip_step_description
     , error_code_en
     , MAX("Input") OVER (PARTITION BY smalldate, mip_step_description) "Input"
     , "Defects"
  FROM (select data.smalldate, mip.mip_step_description, error_code.error_code_en
             , count(case when (error_code is null and quality_plan is null)
                          then data.part_serial_number end) as "Input"
             , count(case when error_code is not null 
                          then data.part_serial_number end) as "Defects"
             , count(data.part_serial_number) sn_ct
          from DATA left join MIP On data.equipment = mip.equipment
                    left join ERROR_CODE on data.error_code = error_code.error_code_sn 
         group by data.smalldate, mip.mip_step_description, error_code.error_code_en)
order by smalldate, mip_step_description, sn_ct desc;