One sentence of SQL splits the number of corresponding data bars by a field value, using the Database Constants table "master." Spt_values "Implementation

Introduction: Master. Spt_values, data line splitting simple tips

SELECTProjguid, Costguid,SUM(Ftamount) asFtamount, Beginmonth, Endmonth, (Endmonth-Beginmonth)+ 1  asRowcountnum fromCb_loan2costWHERELoanguid= '6d88eb2b-18fa-4a4a-9adb-9873b0f14381'GROUP  byProjguid, Costguid, Endmonth, Beginmonth

The SQL result set is isolated as:

a sentence splits the corresponding number of data bars:SELECTM.projguid,m.costguid, Case  whenM.xnmonth=M.endmonth ThenM.newftamount+(M.ftamount-M.newftamount*m.factmonth)ELSEM.newftamountENDFactftamount,m.factmonth from (               SELECTT.projguid, T.costguid, T.ftamount/T.rowcountnum asNewftamount, T.ftamount, T.beginmonth, T.endmonth, Case  whenBeginmonth=Endmonth ThenEndmonthELSESpt. Number ENDFactmonth, SPT. Number  asXnmonth from(SELECTProjguid, Costguid,SUM(Ftamount) asFtamount, Beginmonth, Endmonth, (Endmonth-Beginmonth)+ 1  asRowcountnum fromCb_loan2costWHERELoanguid= '6d88eb2b-18fa-4a4a-9adb-9873b0f14381'          GROUP  byProjguid, Costguid, Endmonth, Beginmonth) T, Master. Spt_values SPTWHERET.rowcountnum>=Spt. Number         andSpt.type= 'P'         andSpt. Number > 0) M

The final result set is as follows:

split the SQL core as follows :

With Database Constants table: Master. Spt_values

This table record is a constant value commonly used in the database

SELECT number from Master. Spt_values

A simple small example

Create TableT (Avarchar(Ten), Bvarchar(Ten), Cint) Insert  intoTSelect 'AA','Pen',5 Union  AllSelect 'AB','Paper',3

Select  as name,1 as from T,master. Spt_values swhere t.c>= S. number and S.type=' P '  and S.number>0

One sentence of SQL splits the number of corresponding data bars by a field value, using the Database Constants table "master." Spt_values "Implementation