• 一句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验证插件简介
    第八章 jQuery与Ajax应用
    第七章 jQuery操作表格及其它应用
    [wpf笔记] 1.xaml
    [2014-10-11]wpf数据绑定
    [2014-9-15]异步委托线程高级
    [2014-9-13]委托多线程
    [2014-9-12]多线程
    [2014-9-11]异步编程继续
    [2014-9-10]异步编程
  • 原文地址:https://www.cnblogs.com/KingUp/p/5722583.html
Copyright © 2020-2023  润新知