给SQL查询结果加上索引号

zhfyfd_1 2014-10-13 12:16:58
现有表A的部分查询结果如下:
[BOMID] [工艺代码] [排序字段] [ID]
99055 1 7 529764
99055 2 13 529405
99055 4 34 539216
99055 4 43 535986
99055 4 67 502340
99055 8 70 536332
99055 7 82 539217
99055 9 94 537687
99055 4 97 537688
104998 1 1 605705
104998 2 2 605706
104998 4 3 605707
104998 4 4 605708
104998 10 5 605709
104998 4 6 605710
104998 4 7 605711
104998 10 8 605712
104998 9 9 605713

表A主键列为ID，这里是两组以BOMID字段为分组依据的两组数据，排序字段中的数值用于决定同一组BOMID中的数据先后顺序。求问各位大神，能否增加一个分组字段[GroupID](用于记录在同一组BOMID中，按照排序字段进行排序的前提下，如果前后相邻的两个工艺代码相同，则为一组)，即达到如下效果呢:
[BOMID] [工艺代码] [排序字段] [ID] [GroupID]
99055 1 7 529764 1
99055 2 13 529405 2
99055 4 34 539216 3
99055 4 43 535986 3
99055 4 67 502340 3
99055 8 70 536332 4
99055 7 82 539217 5
99055 9 94 537687 6
99055 4 97 537688 7
104998 1 1 605705 1
104998 2 2 605706 2
104998 4 3 605707 3
104998 4 4 605708 3
104998 10 5 605709 4
104998 4 6 605710 5
104998 4 7 605711 5
104998 10 8 605712 6
104998 9 9 605713 7
...全文
456 16 打赏 收藏 转发到动态 举报
写回复
用AI写文章
16 条回复
切换为时间正序
请发表友善的回复…
发表回复
还在加载中灬 2014-10-13
  • 打赏
  • 举报
 回复
不用递归也可以吧~~ 
WITH CTE AS(
	SELECT
		ROW_NUMBER()OVER(ORDER BY BOMID,[排序字段])RN
		,ROW_NUMBER()OVER(PARTITION BY [工艺代码] ORDER BY BOMID,[排序字段])RN2
		,BOMID,[工艺代码],[排序字段],ID
	FROM 表A
)
,CTE2 AS(
	SELECT ROW_NUMBER()OVER(PARTITION BY BOMID,[工艺代码],RN-RN2 ORDER BY BOMID,[排序字段])RN,BOMID,[工艺代码],[排序字段],ID,RN-RN2 RN12 FROM CTE
)
,CTE3 AS(
	SELECT ROW_NUMBER()OVER(ORDER BY BOMID,[排序字段])RN,BOMID,[工艺代码],[排序字段],RN12 FROM CTE2 WHERE RN=1
)
SELECT
	A.BOMID,A.[工艺代码],A.[排序字段],A.ID,B.RN GroupID
FROM
	CTE2 A
	LEFT JOIN CTE3 B ON A.BOMID=B.BOMID AND A.[工艺代码]=B.[工艺代码] AND A.RN12=B.RN12
ORDER BY A.BOMID,B.[排序字段]
AcHerat 2014-10-13
  • 打赏
  • 举报
 回复
引用 14 楼 alimake 的回复:
[quote=引用 13 楼 AcHerat 的回复:] [quote=引用 10 楼 alimake 的回复:] [quote=引用 8 楼 AcHerat 的回复:] 不是大版主。。。 找代码找错了，不好意思！
还是 不对。我也觉得这种情况能够用借用孤岛一样的思路用ROW_number -number 这种分组来确定。搞了半天。没搞定。 你这个还是不对。还差一点[/quote] 这个结果是对的，排序需要看实际情况！[/quote] 大版主 你看看这个 99055 1 7 1 1 1 99055 2 13 2 1 2 99055 4 34 3 1 3 99055 4 43 4 2 3 99055 4 67 5 3 3 99055 8 70 6 1 4 99055 7 82 7 1 5 99055 9 94 8 1 6 99055 4 97 9 4 4 你最后一行分组分错了啊 [/quote] 这在最后的 order by lid - rid 给这个值乘工艺代码就能分开，不过分不到楼主的效果，看看还有什么方法。
xiaodongni 2014-10-13
  • 打赏
  • 举报
 回复
引用 13 楼 AcHerat 的回复:
[quote=引用 10 楼 alimake 的回复:] [quote=引用 8 楼 AcHerat 的回复:] 不是大版主。。。 找代码找错了，不好意思！
还是 不对。我也觉得这种情况能够用借用孤岛一样的思路用ROW_number -number 这种分组来确定。搞了半天。没搞定。 你这个还是不对。还差一点[/quote] 这个结果是对的，排序需要看实际情况！[/quote] 大版主 你看看这个 99055 1 7 1 1 1 99055 2 13 2 1 2 99055 4 34 3 1 3 99055 4 43 4 2 3 99055 4 67 5 3 3 99055 8 70 6 1 4 99055 7 82 7 1 5 99055 9 94 8 1 6 99055 4 97 9 4 4 你最后一行分组分错了啊
AcHerat 2014-10-13
  • 打赏
  • 举报
 回复
引用 10 楼 alimake 的回复:
[quote=引用 8 楼 AcHerat 的回复:] 不是大版主。。。 找代码找错了，不好意思！
还是 不对。我也觉得这种情况能够用借用孤岛一样的思路用ROW_number -number 这种分组来确定。搞了半天。没搞定。 你这个还是不对。还差一点[/quote] 这个结果是对的，排序需要看实际情况！
xiaodongni 2014-10-13
  • 打赏
  • 举报
 回复
引用 9 楼 yupeigu 的回复:
[quote=引用 1 楼 alimake 的回复:] 



with cte as
(select 99055 as id,1 gno, 7 ono   union  all 
 select 99055 as id,2 gno, 13 ono   union  all 
 select 99055 as id,4 gno, 34 ono   union  all 
 select 99055 as id,4 gno, 43 ono   union  all 
 select 99055 as id,4 gno, 67 ono   union  all 
 select 99055 as id,8 gno, 70 ono   union  all 
 select 99055 as id,7 gno, 82 ono   union  all 
 select 99055 as id,9 gno, 94 ono   union  all 
 select 99055 as id,4 gno, 97 ono   union  all 
 select 104998 as id,1 gno, 1 ono   union  all
 select 104998 as id,2 gno, 2 ono   union  all
 select 104998 as id,4 gno, 3 ono   union  all
 select 104998 as id,4 gno, 4 ono   union  all
 select 104998 as id,10 gno, 5 ono   union  all
 select 104998 as id,4 gno, 6 ono ),
 cte1 as 
 (select *,ROW_NUMBER()over(partition by id order by ono) as n from cte),
 cte2 as 
 (select * ,1 as groupid from cte1 where n=1
 union all
  select a.*,case when a.gno=b.gno then b.groupid
  else b.groupid+1 end as groupid  from cte1 as a join cte2 as b
    on a.n=b.n+1 and a.id=b.id )
select * from cte2
 order by id,ono

--结果

id          gno         ono         n                    groupid
----------- ----------- ----------- -------------------- -----------
99055       1           7           1                    1
99055       2           13          2                    2
99055       4           34          3                    3
99055       4           43          4                    3
99055       4           67          5                    3
99055       8           70          6                    4
99055       7           82          7                    5
99055       9           94          8                    6
99055       4           97          9                    7
104998      1           1           1                    1
104998      2           2           2                    2
104998      4           3           3                    3
104998      4           4           4                    3
104998      10          5           5                    4
104998      4           6           6                    5

(15 行受影响)
我发现你在解决问题是，经常用递归的方式来解决哦。 但是觉得递归速度上可能会有问题[/quote] 我只能想到这个。我也想到了孤岛问题。想想能不能用ROW_NUMBER -number 这种思路来解决。但是没想到。
xiaodongni 2014-10-13
  • 打赏
  • 举报
 回复 

--结果
id          gno         ono         rid                  lid                  groupid
----------- ----------- ----------- -------------------- -------------------- --------------------
99055       1           7           1                    1                    1
99055       2           13          2                    1                    2
99055       4           34          3                    1                    3
99055       4           43          4                    2                    3
99055       4           67          5                    3                    3
99055       8           70          6                    1                    4
99055       7           82          7                    1                    5
99055       9           94          8                    1                    6
99055       4           97          9                    4                    4
104998      1           1           1                    1                    1
104998      2           2           2                    1                    2
104998      4           3           3                    1                    3
104998      4           4           4                    2                    3
104998      10          5           5                    1                    5
104998      4           6           6                    3                    4

(15 行受影响)
xiaodongni 2014-10-13
  • 打赏
  • 举报
 回复
引用 8 楼 AcHerat 的回复:
不是大版主。。。 找代码找错了，不好意思！
还是 不对。我也觉得这种情况能够用借用孤岛一样的思路用ROW_number -number 这种分组来确定。搞了半天。没搞定。 你这个还是不对。还差一点
LongRui888 2014-10-13
  • 打赏
  • 举报
 回复
引用 1 楼 alimake 的回复:



with cte as
(select 99055 as id,1 gno, 7 ono   union  all 
 select 99055 as id,2 gno, 13 ono   union  all 
 select 99055 as id,4 gno, 34 ono   union  all 
 select 99055 as id,4 gno, 43 ono   union  all 
 select 99055 as id,4 gno, 67 ono   union  all 
 select 99055 as id,8 gno, 70 ono   union  all 
 select 99055 as id,7 gno, 82 ono   union  all 
 select 99055 as id,9 gno, 94 ono   union  all 
 select 99055 as id,4 gno, 97 ono   union  all 
 select 104998 as id,1 gno, 1 ono   union  all
 select 104998 as id,2 gno, 2 ono   union  all
 select 104998 as id,4 gno, 3 ono   union  all
 select 104998 as id,4 gno, 4 ono   union  all
 select 104998 as id,10 gno, 5 ono   union  all
 select 104998 as id,4 gno, 6 ono ),
 cte1 as 
 (select *,ROW_NUMBER()over(partition by id order by ono) as n from cte),
 cte2 as 
 (select * ,1 as groupid from cte1 where n=1
 union all
  select a.*,case when a.gno=b.gno then b.groupid
  else b.groupid+1 end as groupid  from cte1 as a join cte2 as b
    on a.n=b.n+1 and a.id=b.id )
select * from cte2
 order by id,ono

--结果

id          gno         ono         n                    groupid
----------- ----------- ----------- -------------------- -----------
99055       1           7           1                    1
99055       2           13          2                    2
99055       4           34          3                    3
99055       4           43          4                    3
99055       4           67          5                    3
99055       8           70          6                    4
99055       7           82          7                    5
99055       9           94          8                    6
99055       4           97          9                    7
104998      1           1           1                    1
104998      2           2           2                    2
104998      4           3           3                    3
104998      4           4           4                    3
104998      10          5           5                    4
104998      4           6           6                    5

(15 行受影响)
我发现你在解决问题是，经常用递归的方式来解决哦。 但是觉得递归速度上可能会有问题
AcHerat 2014-10-13
  • 打赏
  • 举报
 回复
不是大版主。。。 找代码找错了，不好意思！
AcHerat 2014-10-13
  • 打赏
  • 举报
 回复 

;with acherat as
(
    select *,rid=row_number() over (partition by bomid order by 排序字段),
             lid=row_number() over (partition by bomid,工艺代码 order by 排序字段)
    from cte1
)
 
select *,groupid = dense_rank() over (partition by bomid order by lid - rid)
from acherat
xiaodongni 2014-10-13
  • 打赏
  • 举报
 回复
引用 5 楼 AcHerat 的回复:

;with acherat as
(
    select *,rid=row_number() over (partition by bomid order by 排序字段)
    from a
)

select *,groupid = dense_rank() over (partition by bomid order by 排序字段 - rid)
from acherat
大版主 你这个用法没见过。 我测试了 结果是这个 [code=sql] id gno ono rid groupid ----------- ----------- ----------- -------------------- -------------------- 99055 1 7 1 1 99055 2 13 2 2 99055 4 34 3 3 99055 4 43 4 4 99055 4 67 5 5 99055 8 70 6 6 99055 7 82 7 7 99055 9 94 8 8 99055 4 97 9 9 104998 1 1 1 1 104998 2 2 2 1 104998 4 3 3 1 104998 4 4 4 1 104998 10 5 5 1 104998 4 6 6 1 (15 行受影响)
AcHerat 2014-10-13
  • 打赏
  • 举报
 回复 

;with acherat as
(
    select *,rid=row_number() over (partition by bomid order by 排序字段)
    from a
)

select *,groupid = dense_rank() over (partition by bomid order by 排序字段 - rid)
from acherat
AcHerat 2014-10-13
  • 打赏
  • 举报
 回复
嗯，看错数据，最后的几行看来不是这么分的，采用with cte如下： 

;with acherat as
()
xiaodongni 2014-10-13
  • 打赏
  • 举报
 回复
引用 2 楼 AcHerat 的回复:
dense_rank() over (partition by bomid order by 工艺代码) as groupid
大版主 这个不行吧。DENSE_RANK 会把4 都分在一组吧？
AcHerat 2014-10-13
  • 打赏
  • 举报
 回复
dense_rank() over (partition by bomid order by 工艺代码) as groupid
xiaodongni 2014-10-13
  • 打赏
  • 举报
 回复 



with cte as
(select 99055 as id,1 gno, 7 ono   union  all 
 select 99055 as id,2 gno, 13 ono   union  all 
 select 99055 as id,4 gno, 34 ono   union  all 
 select 99055 as id,4 gno, 43 ono   union  all 
 select 99055 as id,4 gno, 67 ono   union  all 
 select 99055 as id,8 gno, 70 ono   union  all 
 select 99055 as id,7 gno, 82 ono   union  all 
 select 99055 as id,9 gno, 94 ono   union  all 
 select 99055 as id,4 gno, 97 ono   union  all 
 select 104998 as id,1 gno, 1 ono   union  all
 select 104998 as id,2 gno, 2 ono   union  all
 select 104998 as id,4 gno, 3 ono   union  all
 select 104998 as id,4 gno, 4 ono   union  all
 select 104998 as id,10 gno, 5 ono   union  all
 select 104998 as id,4 gno, 6 ono ),
 cte1 as 
 (select *,ROW_NUMBER()over(partition by id order by ono) as n from cte),
 cte2 as 
 (select * ,1 as groupid from cte1 where n=1
 union all
  select a.*,case when a.gno=b.gno then b.groupid
  else b.groupid+1 end as groupid  from cte1 as a join cte2 as b
    on a.n=b.n+1 and a.id=b.id )
select * from cte2
 order by id,ono

--结果

id          gno         ono         n                    groupid
----------- ----------- ----------- -------------------- -----------
99055       1           7           1                    1
99055       2           13          2                    2
99055       4           34          3                    3
99055       4           43          4                    3
99055       4           67          5                    3
99055       8           70          6                    4
99055       7           82          7                    5
99055       9           94          8                    6
99055       4           97          9                    7
104998      1           1           1                    1
104998      2           2           2                    2
104998      4           3           3                    3
104998      4           4           4                    3
104998      10          5           5                    4
104998      4           6           6                    5

(15 行受影响)
mysql 查询数据库索引语句_利用SQL语句查询数据库中所有索引
本章我们就要讲解一下如何利用sql语句来查询出数据库中所有索引明细。当然了，我们可以在microsoftsqlservermanagementstudio中选择"表"->"索引"来查看单个表的索引明细。但这样，查询效率就很低了。要利用sql查询出数据中所有索引，首先要了解目录视图sys.indexes。sys.indexes的定义如下：每个表格对象(例如，表、视图或表值函数)的索引...
SQL查询优化-添加索引
对于新手小白，想要优化SQL，提升查询速度的，简单粗暴的方式。
【SQL Server】查询表字段以及创建索引
6、给某个表某个字段创建索引。8、查询出结果，进行粘贴，执行。4、使用系统存储过程查询索引。7、查询出要创建索引语句。3、查询指定表索引。
MySQL索引失效原因，SQL查询语句不走索引原因
MySQL索引失效原因，SQL查询语句不走索引原因，explain关键字
SQL之SQL索引
索引(index）是帮助MysQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

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

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

+ 用AI写文章