效果 SELECT ROW_NUMBER() OVER (ORDER BY IncomeAmount) AS Idx, * FROM DailyIncome
;WITH CET1 AS ( SELECT ROW_NUMBER() OVER (ORDER BY IncomeAmount) AS Idx, * FROM DailyIncome ), CET2 AS ( SELECT ceiling(MAX(idx)/4.0) AS Cnt FROM CET1--如果有28条,这里的值就是7，且只有一行 ) SELECT a.VendorId as VendorId1,a.IncomeDay AS IncomeDay1,a.IncomeAmount AS IncomeAmount1,--1-7 b.VendorId as VendorId2,b.IncomeDay AS IncomeDay2,b.IncomeAmount AS IncomeAmount2,--8-14 c.VendorId as VendorId3,c.IncomeDay AS IncomeDay3,c.IncomeAmount AS IncomeAmount3,--15-21 d.VendorId as VendorId4,d.IncomeDay AS IncomeDay4,d.IncomeAmount AS IncomeAmount4--22-28 FROM ( SELECT * FROM CET1 a WHERE IDX<=(SELECT cnt FROM CET2 ) ) a LEFT JOIN ( SELECT * FROM CET1 a --WHERE IDX ) b ON a.idx=b.idx-(SELECT cnt FROM CET2 ) LEFT JOIN ( SELECT * FROM CET1 a --WHERE IDX ) c ON b.idx=c.idx-(SELECT cnt FROM CET2) LEFT JOIN ( SELECT * FROM CET1 a --WHERE IDX ) d ON c.idx=d.idx-(SELECT cnt FROM CET2)