SQL查询代码简化优化

这里还有“布疋松布“，”平幅洗水“，”胚定1“，”胚定2“，等16个项，有没有什么动态的办法，或者其他更简便的方法？

select 
 (select SUM(isnull(b.Ds_SendQty,c.Ds_SendQty)) as qty
      from (
           SELECT *,ROW_NUMBER() OVER(PARTITION BY Ms_TankNo ORDER BY Ms_FlowID DESC) Num FROM Dye_Scheme
           ) t
           left join Bas_FlowInfo on Ms_FlowNo = Bf_FlowNo
           left join Dye_Schedule b on b.Ds_TankNo = Ms_TankNo
           left join Dye_Scheduled c on c.Ds_TankNo = Ms_TankNo
            WHERE t.Num=1 
            and Ms_FactualDate is null and Ms_StartDate is null 
            and Ms_InceptDate < GETDATE()
            and Bf_FlowName = '染部收单'
            group by Bf_FlowName
  ) as '染部收单',
 (select SUM(isnull(b.Ds_SendQty,c.Ds_SendQty)) as qty
      from (
           SELECT *,ROW_NUMBER() OVER(PARTITION BY Ms_TankNo ORDER BY Ms_FlowID DESC) Num FROM Dye_Scheme
           ) t
           left join Bas_FlowInfo on Ms_FlowNo = Bf_FlowNo
           left join Dye_Schedule b on b.Ds_TankNo = Ms_TankNo
           left join Dye_Scheduled c on c.Ds_TankNo = Ms_TankNo
            WHERE t.Num=1 
            and Ms_FactualDate is null and Ms_StartDate is null 
            and Ms_InceptDate < GETDATE()
            and Bf_FlowName = '成品定型1'
            group by Bf_FlowName
  ) as '成品定型1'

本意是想把这部分查询结果显示成一行，但是Bf_FlowName列统计可能会没有数据，就会缺少！

SELECT Bf_FlowName,SUM(isnull(b.Ds_SendQty,c.Ds_SendQty)) as qty
FROM(
SELECT *,ROW_NUMBER() OVER(PARTITION BY Ms_TankNo ORDER BY Ms_FlowID DESC) Num FROM Dye_Scheme
) t
left join Bas_FlowInfo on Ms_FlowNo = Bf_FlowNo
left join Dye_Schedule b on b.Ds_TankNo = Ms_TankNo
left join Dye_Scheduled c on c.Ds_TankNo = Ms_TankNo
WHERE t.Num=1 
and Ms_FactualDate is null and Ms_StartDate is null 
and Ms_InceptDate < GETDATE()
and Ms_FlowNo in(2,3,4,6,7,8,9,11,12,13,14,15,26,28,32,35)     --这里是相关的Bf_FlowName代码号
group by Ms_FlowNo,Bf_FlowName
order by Ms_FlowNo

请各位大师指导交流！谢谢！

试一试动态sql吧，我没有你原表的数据，只能在ssms软件中给你改写

DECLARE @DynamicSQL NVARCHAR(MAX) 
DECLARE @Columns NVARCHAR(MAX)

SELECT @Columns = COALESCE(@Columns + ',', '') + QUOTENAME(Bf_FlowName) 
FROM Bas_FlowInfo 
WHERE Ms_FlowNo IN (2,3,4,6,7,8,9,11,12,13,14,15,26,28,32,35)

SET @DynamicSQL = ' SELECT ' + @Columns + ', SUM(isnull(b.Ds_SendQty,c.Ds_SendQty)) as qty 
FROM( 
    SELECT *,ROW_NUMBER() OVER(PARTITION BY Ms_TankNo ORDER BY Ms_FlowID DESC) Num 
    FROM Dye_Scheme ) t 
left join Bas_FlowInfo 
on Ms_FlowNo = Bf_FlowNo left join Dye_Schedule b on b.Ds_TankNo = Ms_TankNo 
left join Dye_Scheduled c on c.Ds_TankNo = Ms_TankNo 
WHERE t.Num=1 and Ms_FactualDate is null and Ms_StartDate is null and Ms_InceptDate < GETDATE() 
and Ms_FlowNo in(2,3,4,6,7,8,9,11,12,13,14,15,26,28,32,35) 
 group by Ms_FlowNo,Bf_FlowName order by Ms_FlowNo'

EXEC sp_executesql @DynamicSQL

【以下回答由 GPT 生成】

SELECT 
  SUM(qty) as '染部收单', 
  SUM(qty) as '成品定型1'
FROM (
  SELECT 
    isnull(b.Ds_SendQty,c.Ds_SendQty) as qty,
    Bf_FlowName
  FROM (
    SELECT 
      *,ROW_NUMBER() OVER(PARTITION BY Ms_TankNo ORDER BY Ms_FlowID DESC) Num 
    FROM Dye_Scheme
  ) t
  LEFT JOIN Bas_FlowInfo ON Ms_FlowNo = Bf_FlowNo
  LEFT JOIN Dye_Schedule b ON b.Ds_TankNo = Ms_TankNo
  LEFT JOIN Dye_Scheduled c ON c.Ds_TankNo = Ms_TankNo
  WHERE 
    t.Num=1 
    AND Ms_FactualDate IS NULL 
    AND Ms_StartDate IS NULL 
    AND Ms_InceptDate < GETDATE()
    AND (Bf_FlowName = '染部收单' OR Bf_FlowName = '成品定型1')
) subquery
GROUP BY Bf_FlowName;

解决方案说明：

1. 将两个子查询合并为一个主查询，避免了重复的代码。
2. 使用了子查询和GROUP BY子句来计算每个Bf_FlowName对应的qty总和。
3. 在子查询中，只选择Bf_FlowName为'染部收单'和'成品定型1'的记录，避免了无关的数据。
4. 将SUM函数移动到主查询中，将两个子查询的结果合并为一个结果集，并使用Bf_FlowName作为标签。
5. 优化了查询逻辑，避免了对同一数据表的重复查询，减少了数据库负载。
6. 去除了子查询的冗余代码，使查询结果更加清晰明了。

这种优化方案可以更有效地处理查询需求，并减少冗余代码。

【相关推荐】

• 你可以看下这个问题的回答https://ask.csdn.net/questions/7703945
• 这篇博客你也可以参考下：【SQL开发实战技巧】系列（十四）：计算消费后的余额&计算银行流水累计和&计算各部门工资排名前三位的员工
• 除此之外, 这篇博客: SQL华水复试总结（保证复试笔试85分以上）中的 存取控制是指什么？主要包括哪两部分？有哪两类方法？ 部分也许能够解决你的问题。