赋值，分组求max即可

按照生成订单、线体进行group by，赋值状态，处理中为2，已完成为1

然后进行max，在还原即可

具体写法看你数据库

mysql？oracle？SqlServer？

--------

with test as(

SELECT '1' [订单号],'S1' [线体],'处理中' [总状态]

union ALL

SELECT '2' [订单号],'S2' [线体],'已完成' [总状态]

union ALL

SELECT '3' [订单号],'S3' [线体],'处理中' [总状态]

union ALL

SELECT '3' [订单号],'S3' [线体],'已完成' [总状态]

union ALL

SELECT '4' [订单号],'S4' [线体],'已完成' [总状态]

union ALL

SELECT '4' [订单号],'S4' [线体],'已完成' [总状态]

),

t as (

SELECT *,ROW_NUMBER() OVER(PARTITION BY 订单号,线体 ORDER BY 总状态 ASC) [I] FROM TEST

)

SELECT * FROM T WHERE 1=1 AND NOT EXISTS(SELECT T1.订单号 FROM T T1 WHERE T1.订单号=T.订单号 AND T1.线体=T.线体 AND T1.I>1 )

UNION ALL

SELECT * FROM T WHERE 1=1 AND EXISTS(SELECT T1.订单号 FROM T T1 WHERE T1.订单号=T.订单号 AND T1.线体=T.线体 AND T1.I>1 ) and 总状态='处理中'

用case when 根据生产订单号分组，如果count(生产订单号)>1，则状态=处理中