单列权重计算、动态列Flag、动态页Flag

--单列权重计算、动态列Flag、动态页Flag
CREATE TABLE City(Id INT, Name NVARCHAR(500), Weight DECIMAL(9,2));
INSERT INTO City VALUES('1','Birmingham','7.6');
INSERT INTO City VALUES('2','Montgomery','8.6');
INSERT INTO City VALUES('3','Mobile','7.6');
INSERT INTO City VALUES('4','Anniston','6.6');
INSERT INTO City VALUES('5','Gadsden','6.6');
INSERT INTO City VALUES('6','Phoenix','5.6');
INSERT INTO City VALUES('7','Scottsdale','6.6');
INSERT INTO City VALUES('8','Tempe','7.7');
INSERT INTO City VALUES('9','Buckeye','8.7');
INSERT INTO City VALUES('10','Chandler','7.6');
INSERT INTO City VALUES('11','ElDorado','6.6');
INSERT INTO City VALUES('12','Jonesboro','8.6');
INSERT INTO City VALUES('13','PaineBluff','7.6');
INSERT INTO City VALUES('14','LittleRock','7.6');
INSERT INTO City VALUES('15','Fayetteville','7.6');
INSERT INTO City VALUES('16','FortSmith','6.6');
INSERT INTO City VALUES('17','MileHouse','8.6');
INSERT INTO City VALUES('18','Kelowna','7.7');
INSERT INTO City VALUES('19','PrinceGeorge','5.6');
INSERT INTO City VALUES('20','Modesto','7.7');
INSERT INTO City VALUES('21','LosAngeles','6.6');
INSERT INTO City VALUES('22','Monterey','7.6');
INSERT INTO City VALUES('23','SanJose','8.6');
INSERT INTO City VALUES('24','SanFrancisco','8.7');
INSERT INTO City VALUES('25','Oakland','8.7');
INSERT INTO City VALUES('26','Berkeley','7.6');
INSERT INTO City VALUES('27','WalnutCreek','8.6');
INSERT INTO City VALUES('28','Alturas','6.6');
INSERT INTO City VALUES('29','Chico','8.6');
INSERT INTO City VALUES('30','Reading','9.6');
INSERT INTO City VALUES('31','Fresno','7.6');
INSERT INTO City VALUES('32','Norwalk','8.6');
INSERT INTO City VALUES('33','Downey','6.6');
INSERT INTO City VALUES('34','LongBeach','8.6');

DECLARE @MAXWEIGHT INT='50';
WITH RecursionList AS (
SELECT cl.Id
     , cl.Name
     , cl.Weight
     , CONVERT(DECIMAL(9,2), cl.Weight) TotalWeight
	 , 1 ColumnIndex
  FROM City cl
 WHERE cl.Id=1
UNION ALL
SELECT cl.Id
     , cl.Name
     , cl.Weight
     , CONVERT(DECIMAL(9,2), CASE WHEN r.TotalWeight+cl.Weight > @MAXWEIGHT THEN cl.Weight ELSE cl.Weight+r.TotalWeight END) TotalWeight
	 , CASE WHEN r.TotalWeight+cl.Weight >@MAXWEIGHT THEN r.ColumnIndex+1 ELSE r.ColumnIndex END ColumnIndex
  FROM RecursionList r
       INNER JOIN City cl ON r.Id=cl.Id-1
)
SELECT x.Id
     , x.Name
     , x.Weight
     , x.TotalWeight
     , x.ColumnIndex
     , CEILING(x.ColumnIndex*1.0/2)  PageIndex
     , CASE x.ColumnIndex%2 WHEN 1 THEN 'A' ELSE 'B' END ColumnSign
  FROM RecursionList x