o0LittleMiss0o 2023-08-12 11:38 采纳率: 100%
浏览 12
已结题

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

img


想做成这种样子

img

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

  • 写回答

2条回答 默认 最新

  • 喝茶品人生 2023-08-12 20:49
    关注

    试一试动态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
    
    本回答被专家选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

问题事件

  • 系统已结题 8月28日
  • 专家已采纳回答 8月20日
  • 创建了问题 8月12日

悬赏问题

  • ¥15 需要写计算过程,不要写代码,求解答,数据都在图上
  • ¥15 向数据表用newid方式插入GUID问题
  • ¥15 multisim电路设计
  • ¥20 用keil,写代码解决两个问题,用库函数
  • ¥50 ID中开关量采样信号通道、以及程序流程的设计
  • ¥15 U-Mamba/nnunetv2固定随机数种子
  • ¥15 C++行情软件的tick数据如何高效的合成K线
  • ¥15 vba使用jmail发送邮件正文里面怎么加图片
  • ¥15 vb6.0如何向数据库中添加自动生成的字段数据。
  • ¥20 在easyX库下编写C语言扑克游戏跑的快,能实现简单的人机对战