• 一句SQL按照某个字段数值拆分出对应的数据条数,借助数据库常量表【master..spt_values】实现


    简介:master..spt_values,数据行拆分简单小技巧 

    SELECT  ProjGUID ,
            CostGUID ,
            SUM(FtAmount) AS FtAmount ,
            BeginMonth ,
            EndMonth ,
            ( EndMonth - BeginMonth ) + 1 AS RowCountNum
    FROM    cb_Loan2Cost
    WHERE   LoanGUID = '6D88EB2B-18FA-4A4A-9ADB-9873B0F14381'
    GROUP BY ProjGUID ,
            CostGUID ,
            EndMonth ,
            BeginMonth

    SQl结果集查出如下图:

    一句话拆分出对应的数据条数:
    
    SELECT  
    M.ProjGUID,
    M.CostGUID,
    CASE WHEN
    
     M.XnMonth=M.EndMonth
    
    THEN M.NewFtAmount+(M.FtAmount-M.NewFtAmount*M.FactMonth) 
    
    ELSE M.NewFtAmount END FactFtAmount,
    M.FactMonth 
    FROM (               
    SELECT  T.ProjGUID ,
            T.CostGUID ,
            T.FtAmount / T.RowCountNum AS NewFtAmount ,
            T.FtAmount,
            T.BeginMonth ,
            T.EndMonth ,
            CASE WHEN BeginMonth = EndMonth THEN EndMonth ELSE spt.number END FactMonth,
            spt.number AS XnMonth
    FROM    ( SELECT    ProjGUID ,
                        CostGUID ,
                        SUM(FtAmount) AS FtAmount ,
                        BeginMonth ,
                        EndMonth ,
                        ( EndMonth - BeginMonth ) + 1 AS RowCountNum
              FROM      cb_Loan2Cost
              WHERE     LoanGUID = '6D88EB2B-18FA-4A4A-9ADB-9873B0F14381'
              GROUP BY  ProjGUID ,
                        CostGUID ,
                        EndMonth ,
                        BeginMonth
            ) T ,
            master..spt_values spt
    WHERE   T.RowCountNum >= spt.number
            AND spt.type = 'P'
            AND spt.number > 0
     )M

    最终结果集如下图:

    拆分SQL核心如下

    借助数据库常量表:master..spt_values

    此表记录都是数据库中常用的常量值

    SELECT number FROM master..spt_values 

    简单的小例子

    create table T(A varchar(10), B varchar(10),  C int)
    
     
    
    insert into T
    select 'AA',            '',                 5 union all
    select 'AB',            '',                 3

    select t.A 编码, t.B as 名称,1 as 数量 
    from t,master..spt_values s
    where t.c>= s.number and s.type= 'P' and s.number >0

  • 相关阅读:
    jQuery .attr() vs .prop()
    新手學python之新體驗
    [pymongo] pymongo.errors.CursorNotFound Exception
    javascript Round Function
    .net Core 3 preview 3试用 WPF,winform桌面开发
    Rendering Transparent 3D Surfaces in WPF with C#(转载)
    jQuery 属性方法 总结
    jQuery 选择器总结
    js + css 实现标签内容切换功能
    初识javascript 之 函数:function
  • 原文地址:https://www.cnblogs.com/KingUp/p/5722583.html
Copyright © 2020-2023  润新知