SQL 分组获取序号

taotie1225 2014-09-24 01:54:04
首先按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

...全文
283 15 打赏 收藏 转发到动态 举报
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
Tiger_Zhao 2014-09-25
  • 打赏
  • 举报
 回复
递归的性能很明显受数据量的影响。
嫌慢就得用其他方案，比如每条记录插入时就分组分配序号，把负担分摊开来。
taotie1225 2014-09-25
  • 打赏
  • 举报
 回复
回楼上几位。 在用递归后，出现超出最大递归数了。（提示The statement terminated. The maximum recursion 100 has been exhausted before statement completion.） 而后，我加了OPTION(MAXRECURSION 0)，查询速度大大大于预期。 如何解决这个问题呢？
xiaodongni 2014-09-24
  • 打赏
  • 举报
 回复
引用 11 楼 taotie1225 的回复:
[quote=引用 6 楼 Tiger_Zhao 的回复:] 3楼没按a,b,c分组。 条件太多，重复写3个CASE不如用3个递归。 
WITH tmp_GridResults_1 (a,b,c,amount) AS (
    SELECT N'a1',N'b1',N'c1',10 UNION ALL
    SELECT N'a1',N'b1',N'c1',40 UNION ALL
    SELECT N'a1',N'b1',N'c1',40 UNION ALL
    SELECT N'a1',N'b1',N'c1',10 UNION ALL
    SELECT N'a1',N'b1',N'c1',50 UNION ALL
    SELECT N'a1',N'b1',N'c2',30 UNION ALL
    SELECT N'a1',N'b1',N'c2',40 UNION ALL
    SELECT N'a1',N'b1',N'c2',10 UNION ALL
    SELECT N'a1',N'b1',N'c2',10 UNION ALL
    SELECT N'a1',N'b1',N'c2',30 UNION ALL
    SELECT N'a1',N'b1',N'c2',30 UNION ALL
    SELECT N'a1',N'b1',N'c2',70 UNION ALL
    SELECT N'a1',N'b1',N'c2',30
),
t1 AS (
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY a,b,c) id
      FROM tmp_GridResults_1
),
t2 AS (
    SELECT *,
           1 head,
           1 item,
           amount groupAmount
      FROM t1
     WHERE id = 1

    UNION ALL

    SELECT t1.*,
           t2.head,
           t2.item+1,
           t2.groupAmount + t1.amount
      FROM t1
      JOIN t2
        ON t1.id = t2.id+1
     WHERE t1.a = t2.a
       AND t1.b = t2.b
       AND t1.c = t2.c
       AND t2.item < 4
       AND (t2.groupAmount + t1.amount) <= 60
           
    UNION ALL

    SELECT t1.*,
           t2.head+1,
           1,
           t1.amount
      FROM t1
      JOIN t2
        ON t1.id = t2.id+1
     WHERE t1.a <> t2.a
        OR t1.b <> t2.b
        OR t1.c <> t2.c
        OR t2.item >= 4
        OR (t2.groupAmount + t1.amount) > 60
)
SELECT head, item, a, b, c, amount
  FROM t2
 ORDER BY head, item
       head        item a    b    c         amount
----------- ----------- ---- ---- ---- -----------
          1           1 a1   b1   c1            40
          2           1 a1   b1   c1            40
          2           2 a1   b1   c1            10
          3           1 a1   b1   c1            50
          3           2 a1   b1   c1            10
          4           1 a1   b1   c2            30
          5           1 a1   b1   c2            40
          5           2 a1   b1   c2            10
          5           3 a1   b1   c2            10
          6           1 a1   b1   c2            30
          6           2 a1   b1   c2            30
          7           1 a1   b1   c2            70
          8           1 a1   b1   c2            30
thx。 但是这样依然不能解决#1所说问题，主券最小化。是否可以解决最小化的问题？[/quote] 感觉应该让数据有序。只要结果唯一。不然结果不唯一。这样就容易出很多问题。 至于让HEAD最少。这要所谓的最优解。感觉实在太麻烦。因为你要优先进行各种配对。考虑各种因素。 因为你配对产生的第一组的时候。最好的时候就是ITEM=4，并且和为60，这样 这一组可以认为是饱和状态。 不然后面都可能因此多出来一组。各种情况 各种问题。
Tiger_Zhao 2014-09-24
  • 打赏
  • 举报
 回复
NP 问题啊！哪有简单的解法！
只能保证有解而已。
taotie1225 2014-09-24
  • 打赏
  • 举报
 回复
引用 6 楼 Tiger_Zhao 的回复:
3楼没按a,b,c分组。 条件太多，重复写3个CASE不如用3个递归。 
WITH tmp_GridResults_1 (a,b,c,amount) AS (
    SELECT N'a1',N'b1',N'c1',10 UNION ALL
    SELECT N'a1',N'b1',N'c1',40 UNION ALL
    SELECT N'a1',N'b1',N'c1',40 UNION ALL
    SELECT N'a1',N'b1',N'c1',10 UNION ALL
    SELECT N'a1',N'b1',N'c1',50 UNION ALL
    SELECT N'a1',N'b1',N'c2',30 UNION ALL
    SELECT N'a1',N'b1',N'c2',40 UNION ALL
    SELECT N'a1',N'b1',N'c2',10 UNION ALL
    SELECT N'a1',N'b1',N'c2',10 UNION ALL
    SELECT N'a1',N'b1',N'c2',30 UNION ALL
    SELECT N'a1',N'b1',N'c2',30 UNION ALL
    SELECT N'a1',N'b1',N'c2',70 UNION ALL
    SELECT N'a1',N'b1',N'c2',30
),
t1 AS (
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY a,b,c) id
      FROM tmp_GridResults_1
),
t2 AS (
    SELECT *,
           1 head,
           1 item,
           amount groupAmount
      FROM t1
     WHERE id = 1

    UNION ALL

    SELECT t1.*,
           t2.head,
           t2.item+1,
           t2.groupAmount + t1.amount
      FROM t1
      JOIN t2
        ON t1.id = t2.id+1
     WHERE t1.a = t2.a
       AND t1.b = t2.b
       AND t1.c = t2.c
       AND t2.item < 4
       AND (t2.groupAmount + t1.amount) <= 60
           
    UNION ALL

    SELECT t1.*,
           t2.head+1,
           1,
           t1.amount
      FROM t1
      JOIN t2
        ON t1.id = t2.id+1
     WHERE t1.a <> t2.a
        OR t1.b <> t2.b
        OR t1.c <> t2.c
        OR t2.item >= 4
        OR (t2.groupAmount + t1.amount) > 60
)
SELECT head, item, a, b, c, amount
  FROM t2
 ORDER BY head, item
       head        item a    b    c         amount
----------- ----------- ---- ---- ---- -----------
          1           1 a1   b1   c1            40
          2           1 a1   b1   c1            40
          2           2 a1   b1   c1            10
          3           1 a1   b1   c1            50
          3           2 a1   b1   c1            10
          4           1 a1   b1   c2            30
          5           1 a1   b1   c2            40
          5           2 a1   b1   c2            10
          5           3 a1   b1   c2            10
          6           1 a1   b1   c2            30
          6           2 a1   b1   c2            30
          7           1 a1   b1   c2            70
          8           1 a1   b1   c2            30
thx。 但是这样依然不能解决#1所说问题，主券最小化。是否可以解决最小化的问题？
Tiger_Zhao 2014-09-24
  • 打赏
  • 举报
 回复
到不是好坏的问题，而是5个条件写3遍太难看了。
还在加载中灬 2014-09-24
  • 打赏
  • 举报
 回复
引用 8 楼 taotie1225 的回复:
[quote=引用 5 楼 ky_min 的回复:] WITH CTE AS( SELECT ROW_NUMBER()OVER(ORDER BY GETDATE())RN ,* FROM #t ) ,CTE2 AS( SELECT a,b,c,amount,RN,1 head,1 item,amount sumamount FROM CTE WHERE RN=1 UNION ALL SELECT A.a,A.b,A.c,A.amount,A.RN ,CASE WHEN A.amount+B.sumamount<=60 THEN B.head ELSE B.head+1 END ,CASE WHEN A.amount+B.sumamount<=60 AND B.item<4 THEN B.item+1 ELSE 1 END ,CASE WHEN A.amount+B.sumamount<=60 THEN A.amount+B.sumamount ELSE A.amount END FROM CTE A INNER JOIN CTE2 B ON A.RN=B.RN+1 ) SELECT head,item,a,b,c,amount FROM CTE2 哦，加上红色的地方试下
加上后，第5行记录的head仍为1.应该是2了[/quote] 嗯，不好意思，结合 #6 提出来的错误 整合如下 
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
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'1' AS [amount] UNION ALL
SELECT N'a1' AS [a], N'b1' AS [b], N'c1' AS [c], N'1' AS [amount] UNION ALL
SELECT N'a1' AS [a], N'b1' AS [b], N'c1' AS [c], N'1' AS [amount] UNION ALL
SELECT N'a1' AS [a], N'b1' AS [b], N'c1' AS [c], N'1' 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]
go
WITH CTE AS(
	SELECT
		ROW_NUMBER()OVER(ORDER BY a,b,c)RN
		,*
	FROM
		#t
)
,CTE2 AS(
	SELECT a,b,c,amount,RN,1 head,1 item,amount sumamount FROM CTE WHERE RN=1
	UNION ALL
	SELECT
		A.a,A.b,A.c,A.amount,A.RN
		,CASE WHEN A.amount+B.sumamount<=60 AND B.item<4 THEN B.head ELSE B.head+1 END
		,CASE WHEN A.amount+B.sumamount<=60 AND B.item<4 THEN B.item+1 ELSE 1 END
		,CASE WHEN A.amount+B.sumamount<=60 AND B.item<4 THEN A.amount+B.sumamount ELSE A.amount END
	FROM
		CTE A
		INNER JOIN CTE2 B ON A.RN=B.RN+1
)
SELECT head,item,a,b,c,amount FROM CTE2
taotie1225 2014-09-24
  • 打赏
  • 举报
 回复
引用 5 楼 ky_min 的回复:
WITH CTE AS( SELECT ROW_NUMBER()OVER(ORDER BY GETDATE())RN ,* FROM #t ) ,CTE2 AS( SELECT a,b,c,amount,RN,1 head,1 item,amount sumamount FROM CTE WHERE RN=1 UNION ALL SELECT A.a,A.b,A.c,A.amount,A.RN ,CASE WHEN A.amount+B.sumamount<=60 THEN B.head ELSE B.head+1 END ,CASE WHEN A.amount+B.sumamount<=60 AND B.item<4 THEN B.item+1 ELSE 1 END ,CASE WHEN A.amount+B.sumamount<=60 THEN A.amount+B.sumamount ELSE A.amount END FROM CTE A INNER JOIN CTE2 B ON A.RN=B.RN+1 ) SELECT head,item,a,b,c,amount FROM CTE2 哦，加上红色的地方试下
加上后，第5行记录的head仍为1.应该是2了
还在加载中灬 2014-09-24
  • 打赏
  • 举报
 回复
引用 6 楼 Tiger_Zhao 的回复:
3楼没按a,b,c分组。 条件太多，重复写3个CASE不如用3个递归。
嗯，确实忘了分组了，多一个递归和多一个条件判断，我现在还感觉不出好坏 分组我觉得还应该加上点什么
Tiger_Zhao 2014-09-24
  • 打赏
  • 举报
 回复
3楼没按a,b,c分组。
条件太多，重复写3个CASE不如用3个递归。
WITH tmp_GridResults_1 (a,b,c,amount) AS (

    SELECT N'a1',N'b1',N'c1',10 UNION ALL

    SELECT N'a1',N'b1',N'c1',40 UNION ALL

    SELECT N'a1',N'b1',N'c1',40 UNION ALL

    SELECT N'a1',N'b1',N'c1',10 UNION ALL

    SELECT N'a1',N'b1',N'c1',50 UNION ALL

    SELECT N'a1',N'b1',N'c2',30 UNION ALL

    SELECT N'a1',N'b1',N'c2',40 UNION ALL

    SELECT N'a1',N'b1',N'c2',10 UNION ALL

    SELECT N'a1',N'b1',N'c2',10 UNION ALL

    SELECT N'a1',N'b1',N'c2',30 UNION ALL

    SELECT N'a1',N'b1',N'c2',30 UNION ALL

    SELECT N'a1',N'b1',N'c2',70 UNION ALL

    SELECT N'a1',N'b1',N'c2',30

),

t1 AS (

    SELECT *,

           ROW_NUMBER() OVER (ORDER BY a,b,c) id

      FROM tmp_GridResults_1

),

t2 AS (

    SELECT *,

           1 head,

           1 item,

           amount groupAmount

      FROM t1

     WHERE id = 1



    UNION ALL



    SELECT t1.*,

           t2.head,

           t2.item+1,

           t2.groupAmount + t1.amount

      FROM t1

      JOIN t2

        ON t1.id = t2.id+1

     WHERE t1.a = t2.a

       AND t1.b = t2.b

       AND t1.c = t2.c

       AND t2.item < 4

       AND (t2.groupAmount + t1.amount) <= 60

           

    UNION ALL



    SELECT t1.*,

           t2.head+1,

           1,

           t1.amount

      FROM t1

      JOIN t2

        ON t1.id = t2.id+1

     WHERE t1.a <> t2.a

        OR t1.b <> t2.b

        OR t1.c <> t2.c

        OR t2.item >= 4

        OR (t2.groupAmount + t1.amount) > 60

)

SELECT head, item, a, b, c, amount

  FROM t2

 ORDER BY head, item

       head        item a    b    c         amount

----------- ----------- ---- ---- ---- -----------

          1           1 a1   b1   c1            40

          2           1 a1   b1   c1            40

          2           2 a1   b1   c1            10

          3           1 a1   b1   c1            50

          3           2 a1   b1   c1            10

          4           1 a1   b1   c2            30

          5           1 a1   b1   c2            40

          5           2 a1   b1   c2            10

          5           3 a1   b1   c2            10

          6           1 a1   b1   c2            30

          6           2 a1   b1   c2            30

          7           1 a1   b1   c2            70

          8           1 a1   b1   c2            30
还在加载中灬 2014-09-24
  • 打赏
  • 举报
 回复
WITH CTE AS( SELECT ROW_NUMBER()OVER(ORDER BY GETDATE())RN ,* FROM #t ) ,CTE2 AS( SELECT a,b,c,amount,RN,1 head,1 item,amount sumamount FROM CTE WHERE RN=1 UNION ALL SELECT A.a,A.b,A.c,A.amount,A.RN ,CASE WHEN A.amount+B.sumamount<=60 THEN B.head ELSE B.head+1 END ,CASE WHEN A.amount+B.sumamount<=60 AND B.item<4 THEN B.item+1 ELSE 1 END ,CASE WHEN A.amount+B.sumamount<=60 THEN A.amount+B.sumamount ELSE A.amount END FROM CTE A INNER JOIN CTE2 B ON A.RN=B.RN+1 ) SELECT head,item,a,b,c,amount FROM CTE2 哦，加上红色的地方试下
taotie1225 2014-09-24
  • 打赏
  • 举报
 回复
ky_min，你的SQL还有个BUG，一个head最多只可以有4个item。 如果再加几条测试数据 SELECT N'a1' AS [a], N'b1' AS [b], N'c1' AS [c], N'5' AS [amount] UNION ALL SELECT N'a1' AS [a], N'b1' AS [b], N'c1' AS [c], N'5' AS [amount] UNION ALL SELECT N'a1' AS [a], N'b1' AS [b], N'c1' AS [c], N'5' AS [amount] UNION ALL SELECT N'a1' AS [a], N'b1' AS [b], N'c1' AS [c], N'5' AS [amount] 出来的结果中，item有5的情况了
还在加载中灬 2014-09-24
  • 打赏
  • 举报
 回复
你看下，不知道这是不是你想要的效果 
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
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]
go
WITH CTE AS(
	SELECT
		ROW_NUMBER()OVER(ORDER BY GETDATE())RN
		,*
	FROM
		#t
)
,CTE2 AS(
	SELECT a,b,c,amount,RN,1 head,1 item,amount sumamount FROM CTE WHERE RN=1
	UNION ALL
	SELECT
		A.a,A.b,A.c,A.amount,A.RN
		,CASE WHEN A.amount+B.sumamount<=60 THEN B.head ELSE B.head+1 END
		,CASE WHEN A.amount+B.sumamount<=60 THEN B.item+1 ELSE 1 END
		,CASE WHEN A.amount+B.sumamount<=60 THEN A.amount+B.sumamount ELSE A.amount END
	FROM
		CTE A
		INNER JOIN CTE2 B ON A.RN=B.RN+1
)
SELECT head,item,a,b,c,amount FROM CTE2
taotie1225 2014-09-24
  • 打赏
  • 举报
 回复
引用 1 楼 alimake 的回复:
有点难。难点在于不好后面的不好分组。这个行是无序的。 对于 amount 40 10 20 10 30 10 这个是无序。要是把他当做有序 很可能就分成3组。其实2组就可以了。
对于这点，在没有完美的解决方案下，可以先不考虑
xiaodongni 2014-09-24
  • 打赏
  • 举报
 回复
有点难。难点在于不好后面的不好分组。这个行是无序的。 对于 amount 40 10 20 10 30 10 这个是无序。要是把他当做有序 很可能就分成3组。其实2组就可以了。
VBA从入门到精通（控件+类模块+ADO+SQL数据库）
VBA中比较高阶的课程，主要讲解关于各种事件的应用，各种控件的应用，如果使用类模块，如果在VBA用调用SQL语句去处理数据库中的数据等操作。学习完成本课后，你可以在Excel中开发各种小型应用系统。以下是课程主要...
sql分组后获取其中一个字段最大值或最小值的该行的整条记录
sql分组后获取其中一个字段最大值或最小值的该行的整条记录
sql分组查询最大值或最小值
分组只保留一条数据select min(KHID) from UCC_KHXX group by TEL去除重复数据，只保留第一次录入的数据DELETE FROM KHXX WHERE TEL IN (select TEL from KHXX group by TEL having count(TEL) > 1) and KHID not in...
SQL分组后取出每组的第N条（或第几大/小的）记录（全网最简单的方法）
在实际工作中，有时我们需要用SQL语句对数据分组后取出每一组内的某一条（如第1条，第2条……第n条，或者第1大/小，第2大/小……第n大/小）记录，下面将介绍实现这一需求的最简单的SQL查询方式。 需求：查询每门...
SQL分组获取随机记录
SQL分组获取随机记录 2017-12-30 08:59:32一木剑阅读数 1038更多 分类专栏：SQL 版权声明：本文为博主原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。 本文链接：...
应用实例

27,579

社区成员

68,558

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章