简介: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