SQL 分组获取序号

2014-09-24 01:54:04 396 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

STM32F103模拟SSI时序解析：实现高精度绝对式编码器位置读取本文详细解析了如何在STM32F103微控制器上通过软件模拟SSI协议时序，实现高精度绝对式编码器的位置读取。文章从项目驱动出发，深入剖析了SSI通信原理、硬件电平转换连接、核心软件模拟代码实现及关键时序参数调试方法，并提供了使用示波器进行波形对齐与优化的实战指南，为成本敏感型工业控制与机器人项目提供了高性价比的解决方案。阅读详情

首先按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

...全文

数据中心——Vxlan基本概念1 东西流量指的就是横向流量-------例如：服务器与服务器之间交换流量南北流量指的就是纵向流量-------例如：客户端和服务器之间的流量以下主要是讲解业务平面的网络架构（一般业务平面就需要用到专用的数据中心交换机）阅读详情

taotie1225

应用实例 27,579 成员 68,540 内容

+ 关注 + 加入社区

切换为时间正序

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组就可以了。

SQL 分组获取序号

相关推荐