编辑代码

select 
    Average,
    Homogen,

    difference,

    net_weight,

    jsonb_array_elements(testData::jsonb) ->> 'MaxPower' AS maxpower,

    jsonb_array_elements(testData::jsonb) ->> 'Thickness' AS thickness,
    jsonb_array_elements(testData::jsonb) ->> 'Refraction' AS refraction,

    FurnaceID,
    equipmentNo as FurnaceID,
    lineNo as FurnaceID,

    creationDate,
    case 
        when to_char(creationDate, 'HH24MI')::INTEGER >= 800 and to_char(creationDate, 'HH24MI')::INTEGER  < 2000 then to_char(creationDate 'MM/DD') || 'D'
        when to_char(creationDate, 'HH24MI')::INTEGER >= 2000 then to_char(creationDate 'MM/DD') || 'N'
        else to_char(creationDate - INTERVAL '1 day', 'MM/DD') || 'N'
    end AS timeStr
from 
    -- 硼扩方阻
    battery_spc_gather_auto
    -- 在线退火方阻
    spc_anneal_offline
    -- 离线退火方阻
    spc_anneal_offline
    -- 氧化方阻
    spc_oxidation_offline
    -- 制绒减重
    battert_spc_scales_texturing
    -- 碱抛减重
    battert_spc_scales_alkalicase
    -- 制绒反射率
    spc_texturing_offline
    -- 碱抛反射率
    spc_alkalicase_offline
    -- Pepoly
    spc_pepoly_offline
    -- 氧化铝-板式
    spc_alumina1_offline
    -- 氧化铝-管式
    spc_alumina2_offline
    -- 正膜
    spc_positive_membrane_offline
    -- 背膜
    spc_dorsal_membrane_offline
    -- 拉脱力-正电极
    spc_pullingforce1_offline
    -- 拉脱力-背电极
    spc_pullingforce2_offline
    -- 三点抗弯
    spc_threepointbendingresistance_offline
    -- 丝网数据
    battert_spc_scales_silk
where 1=1
    and bsga.workshop = 'C1'
    and ${reportCombox} = 1

    and Average != '0.0'

    and difference IS NOT NULL

    and net_weight IS NOT NULL

    and testData IS NOT NULL

    and creationDate >= to_timestamp('${startDate}', 'YYYY-MM-DD HH24:MI:SS')
    and creationDate < to_timestamp('${endDate}', 'YYYY-MM-DD HH24:MI:SS')

    ${if(len(deviceId)>0, " and FurnaceID in ('" +deviceId+ "') ","")}
    ${if(len(deviceId)>0, " and equipmentNo in ('" +deviceId+ "') ","")}
    ${if(len(deviceId)>0, " and lineNo in ('" +deviceId+ "') ","")}

order by timeStr