sql 一对多数据如何做到一对一

sql
主要问题
表:
(t1 运输任务单主表 t2 运输任务单子表 t3小代码子表(用于过滤运输任务单数据类型) )调度池 (t4 物料表 t5 主月台 t6 主月台装车表 t7组合月台 t8 组合月台装车表) 匹配池

调度池主要实现于
按照IBC桶、MDI-100、有托盘这三种优先调度原则对车牌进行排序 后按照接收时间排序
存在托盘则调度到支持托盘的月台
匹配池:
运输任务子表物料匹配月台物料然后感觉匹配出的物料找出对应的月台

这样会存在一个问题就是 当我有2张运输任务单且物料都满足1号月台时 张两单会同时调度到1号月台
就是当数据不为一对一数据时会产生一对多的情况

select t1.supos_tm_Truck_Name as Truck_Name,
    t1.supos_tm_Business_Type as Business_Type,
case
        when t2.supos_tm_Material like '%IBC%' then '1'
        when t2.supos_tm_Brand_No = 'MDI-100' then '1'
        when t2.supos_tm_pallet_Material_desc != '无'
        and t2.supos_tm_pallet_Material_desc is not null then '1'
        else '0'
    end as level,
    t5.admin_jcsj_Rplatform_No as Rplatform_No,
    t5.admin_jcsj_next_platform as next_platform,
    t6.supos_bm_Truck_Name,
    t7.admin_jcsj_Rplatform_No as Rplatform_No_combind
from supos_tm_tm_transportation_main as t1
    left join supos_tm_tm_transportation_detail as t2 on t2.supos_tm_Transporttask_NO = t1.supos_tm_Transporttask_NO
    right join admin_jcsj_BC_SmallCode_detail as t3 on t3.admin_jcsj_code = 'T32'
    and t3.admin_jcsj_value1 = t1.supos_tm_belong
    left join admin_jcsj_material_r_material_detail as t4 on t2.supos_tm_Material_Code = t4.admin_jcsj_material
    left join admin_jcsj_material_r_Platform as t5 on t5.admin_jcsj_Rplatform_No = t4.admin_jcsj_Rplatform_No
    and t5.admin_jcsj_Isdeleted = '1'
    and t5.admin_jcsj_ISAutomatic_calling = '1'
    and t5.admin_jcsj_Status = '1'
    left join supos_bm_bm_pli as t6 on t6.supos_bm_Rplatform_No = t5.admin_jcsj_Rplatform_No
    left join admin_jcsj_material_r_Platform as t7 on t7.admin_jcsj_Rplatform_No = t5.admin_jcsj_Rplatform_No_combind
    and (
        t7.admin_jcsj_Isdeleted = '1'
        and t7.admin_jcsj_ISAutomatic_calling = '1'
        and t7.admin_jcsj_Status = '1'
    )
    left join supos_bm_bm_pli as t8 on t8.supos_bm_Rplatform_No = t7.admin_jcsj_Rplatform_No
where t1.supos_tm_status = '10'
    and t5.admin_jcsj_rplatform_no is not null
    and t6.supos_bm_Truck_Name is null
    and case
        when t2.supos_tm_pallet_Material_desc is not null
        and t2.supos_tm_pallet_Material_desc != '无' then t5.admin_jcsj_is_use_pallet = 1
        else t5.admin_jcsj_is_use_pallet = 0
        or t5.admin_jcsj_is_use_pallet = 1
    end
    and t8.supos_bm_Truck_Name is null
group by t1.supos_tm_Transporttask_NO
order by t1.supos_tm_RecvTime,
    max(
        case
            when t2.supos_tm_Material like '%IBC%' then '1'
            when t2.supos_tm_Brand_No = 'MDI-100' then '1'
            when t2.supos_tm_pallet_Material_desc != '无'
            and t2.supos_tm_pallet_Material_desc is not null then '1'
            else '0'
        end
    )

执行结果

不知道你这个问题是否已经解决, 如果还没有解决的话:

• 这有个类似的问题, 你可以参考下: https://ask.csdn.net/questions/7619467

如果你已经解决了该问题, 非常希望你能够分享一下解决方案, 写成博客, 将相关链接放在评论区, 以帮助更多的人 ^-^