首先按a,b,c3列分组（组号放到head列），一个head序号至多包含4条组数据，超过4条时head累加1。同一head组中amount累加不超过60，若超过60，再另外分组
第一个结果集是数据源。
第二个数据集是需要得到的结果集。
CREATE TABLE #t(a varchar(50),b varchar(50),c varchar(50),amount int)
--------------- #tmp_GridResults_1 ---------------
SELECT * INTO #tmp_GridResults_1
FROM (
SELECT N'a1' AS [a], N'b1' AS [b], N'c1' AS [c], N'10' AS [amount] UNION ALL
SELECT N'a1' AS [a], N'b1' AS [b], N'c1' AS [c], N'40' AS [amount] UNION ALL
SELECT N'a1' AS [a], N'b1' AS [b], N'c1' AS [c], N'40' AS [amount] UNION ALL
SELECT N'a1' AS [a], N'b1' AS [b], N'c1' AS [c], N'10' AS [amount] UNION ALL
SELECT N'a1' AS [a], N'b1' AS [b], N'c1' AS [c], N'50' AS [amount] UNION ALL
SELECT N'a1' AS [a], N'b1' AS [b], N'c2' AS [c], N'30' AS [amount] UNION ALL
SELECT N'a1' AS [a], N'b1' AS [b], N'c2' AS [c], N'40' AS [amount] UNION ALL
SELECT N'a1' AS [a], N'b1' AS [b], N'c2' AS [c], N'10' AS [amount] UNION ALL
SELECT N'a1' AS [a], N'b1' AS [b], N'c2' AS [c], N'10' AS [amount] UNION ALL
SELECT N'a1' AS [a], N'b1' AS [b], N'c2' AS [c], N'30' AS [amount] UNION ALL
SELECT N'a1' AS [a], N'b1' AS [b], N'c2' AS [c], N'30' AS [amount] UNION ALL
SELECT N'a1' AS [a], N'b1' AS [b], N'c2' AS [c], N'70' AS [amount] UNION ALL
SELECT N'a1' AS [a], N'b1' AS [b], N'c2' AS [c], N'30' AS [amount] ) t;
INSERT INTO #t([a], [b], [c], [amount])
SELECT [a], [b], [c], [amount]
FROM #tmp_GridResults_1
SELECT * FROM #t --数据源
--首先按a,b,c3列分组（组号放到head列），一个head序号至多包含4条组数据，超过4条时head累加1。同一head组中amount累加不超过60，若超过60，再另外分组
--结构数据集的行数和源数据行数相同
--如以上测试数据，需要得到的数据结果为
SELECT '1' AS head,'1' AS item, N'a1' AS [a], N'b1' AS [b], N'c1' AS [c], N'10' AS [amount] UNION ALL
SELECT '1' AS head,'2' AS item, N'a1' AS [a], N'b1' AS [b], N'c1' AS [c], N'40' AS [amount] UNION ALL
SELECT '1' AS head,'3' AS item, N'a1' AS [a], N'b1' AS [b], N'c1' AS [c], N'10' AS [amount] UNION ALL
SELECT '2' AS head,'1' AS item, N'a1' AS [a], N'b1' AS [b], N'c1' AS [c], N'40' AS [amount] UNION ALL
SELECT '3' AS head,'1' AS item, N'a1' AS [a], N'b1' AS [b], N'c1' AS [c], N'50' AS [amount] UNION ALL
SELECT '4' AS head,'1' AS item, N'a1' AS [a], N'b1' AS [b], N'c2' AS [c], N'40' AS [amount] UNION ALL
SELECT '4' AS head,'2' AS item, N'a1' AS [a], N'b1' AS [b], N'c2' AS [c], N'10' AS [amount] UNION ALL
SELECT '4' AS head,'3' AS item, N'a1' AS [a], N'b1' AS [b], N'c2' AS [c], N'10' AS [amount] UNION ALL
SELECT '5' AS head,'1' AS item, N'a1' AS [a], N'b1' AS [b], N'c2' AS [c], N'30' AS [amount] UNION ALL
SELECT '5' AS head,'2' AS item, N'a1' AS [a], N'b1' AS [b], N'c2' AS [c], N'30' AS [amount] UNION ALL
SELECT '6' AS head,'1' AS item, N'a1' AS [a], N'b1' AS [b], N'c2' AS [c], N'30' AS [amount] UNION ALL
SELECT '6' AS head,'2' AS item, N'a1' AS [a], N'b1' AS [b], N'c2' AS [c], N'30' AS [amount] UNION ALL
SELECT '7' AS head,'1' AS item, N'a1' AS [a], N'b1' AS [b], N'c2' AS [c], N'70' AS [amount]
DROP TABLE #tmp_GridResults_1
DROP TABLE #t
GO
------解决思路----------------------
有点难。难点在于不好后面的不好分组。这个行是无序的。
对于
amount
40
10
20
10
30
10
这个是无序。要是把他当做有序 很可能就分成3组。其实2组就可以了。
------解决思路----------------------
你看下，不知道这是不是你想要的效果
IF OBJECT_ID('TempDB..#t',N'U') IS NOT NULL
DROP TABLE #t
GO
CREATE TABLE #t(a varchar(50),b varchar(50),c varchar(50),amount int)
INSERT INTO #t