一句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