34,576
社区成员
发帖
与我相关
我的任务
分享求一句SQL
有这样一张流水表格，userid记录的是操作者代码，Docnum记录的是当前操作的工单号，Linenum记录的是当前工单操作的次数序号，
nextdocnum 记录下一个的操作工单号 ，nextdocline 记录的是下一个工单操作的次数序号，
现在想按照userID, 在某段时间内关联出某个用户的所有操作顺序步骤，（时间段我在这里没有贴出字段来，后面有个记录当前开始操作的时间字段）
比如第一行 userid 1 的操作者 操作了工单887 的第一次，他操作的下个工单是1065 的第六次，那么SQL语句要实现的就是第一行后应该跟 这一行： 1 1065 6 1066 5
我想安装这个顺序把他们排序出来
userid docnum linenum nextdocnum nextdocline
1 887 1 1065 6
1 887 2 927 1
1 887 3 887 4
1 887 4 1071 2
1 887 5 887 6
1 887 6 887 14
2 887 7 887 12
4 887 8 887 9
4 887 9 887 10
4 887 10 887 11
4 887 11 1074 1
2 887 12 887 13
2 887 13 1046 1
1 887 14 1067 4
1 887 15 1070 6
4 887 16 NULL NULL
1 887 17 NULL NULL
1 927 1 1038 2
1 976 1 1063 1
1 1008 1 1008 2
1 1008 2 1008 3
1 1008 3 1072 1
1 1037 1 1037 2
1 1037 2 1037 3
1 1037 3 1037 4
1 1037 4 1068 1
1 1038 1 1065 5
1 1038 2 1038 3
1 1038 3 1038 4
1 1038 4 1067 1
1 1045 1 1045 2
1 1045 2 1045 3
1 1045 3 1070 1
2 1046 1 1046 2
2 1046 2 1067 3
1 1047 1 1038 1
1 1047 2 1047 3
1 1047 3 1047 4
1 1047 4 1071 1
1 1063 1 1064 2
4 1064 1 1066 3
1 1064 2 1064 3
1 1064 3 1065 1
1 1065 1 1065 2
1 1065 2 1065 3
1 1065 3 1066 1
1 1065 4 1047 1
1 1065 5 887 1
1 1065 6 1066 5
1 1066 1 1066 2
1 1066 2 1065 4
4 1066 3 887 8
2 1066 4 887 7
1 1066 5 1066 6
1 1066 6 1066 7
1 1066 7 1066 8
1 1066 8 1066 9
1 1066 9 1066 10
1 1066 10 887 2
1 1067 1 1067 2
1 1067 2 1037 1
2 1067 3 1068 2
1 1067 4 1067 6
2 1067 5 1068 5
1 1067 6 1067 7
1 1067 7 1068 6
1 1067 8 1067 9
1 1067 9 1067 10
1 1067 10 1067 11
1 1067 11 1067 12
1 1067 12 1070 3
1 1067 13 1073 1
1 1067 14 887 17
1 1068 1 1045 1
2 1068 2 1068 3
2 1068 3 1068 4
2 1068 4 1067 5
2 1068 5 1070 2
1 1068 6 1067 8
1 1070 1 1047 2
2 1070 2 NULL NULL
1 1070 3 1067 13
1 1070 4 1070 5
1 1070 5 1076 1
1 1070 6 1067 14
1 1071 1 1008 1
1 1071 2 887 5
1 1072 1 887 3
1 1073 1 1070 4
1 1073 2 1078 1
4 1074 1 1074 2
4 1074 2 1074 3
4 1074 3 1074 4
4 1074 4 1075 1
4 1075 1 1075 2
4 1075 2 1075 3
4 1075 3 1075 4
4 1075 4 887 16
1 1076 1 1076 2
1 1076 2 1076 3
1 1076 3 1077 1
1 1077 1 1077 2
1 1077 2 1077 3
1 1077 3 1077 4
1 1077 4 1073 2
1 1078 1 1078 2
1 1078 2 1078 3
1 1078 3 1078 4
1 1078 4 1078 5
1 1078 5 1079 1
1 1079 1 1079 2
1 1079 2 1079 3
1 1079 3 887 15
nextdocnum 记录下一个的操作工单号 ，nextdocline 记录的是下一个工单操作的次数序号，
现在想按照userID, 在某段时间内关联出某个用户的所有操作顺序步骤，（时间段我在这里没有贴出字段来，后面有个记录当前开始操作的时间字段）
比如第一行 userid 1 的操作者 操作了工单887 的第一次，他操作的下个工单是1065 的第六次，那么SQL语句要实现的就是第一行后应该跟 这一行： 1 1065 6 1066 5
我想安装这个顺序把他们排序出来
userid docnum linenum nextdocnum nextdocline
1 887 1 1065 6
1 887 2 927 1
1 887 3 887 4
1 887 4 1071 2
1 887 5 887 6
1 887 6 887 14
2 887 7 887 12
4 887 8 887 9
4 887 9 887 10
4 887 10 887 11
4 887 11 1074 1
2 887 12 887 13
2 887 13 1046 1
1 887 14 1067 4
1 887 15 1070 6
4 887 16 NULL NULL
1 887 17 NULL NULL
1 927 1 1038 2
1 976 1 1063 1
1 1008 1 1008 2
1 1008 2 1008 3
1 1008 3 1072 1
1 1037 1 1037 2
1 1037 2 1037 3
1 1037 3 1037 4
1 1037 4 1068 1
1 1038 1 1065 5
1 1038 2 1038 3
1 1038 3 1038 4
1 1038 4 1067 1
1 1045 1 1045 2
1 1045 2 1045 3
1 1045 3 1070 1
2 1046 1 1046 2
2 1046 2 1067 3
1 1047 1 1038 1
1 1047 2 1047 3
1 1047 3 1047 4
1 1047 4 1071 1
1 1063 1 1064 2
4 1064 1 1066 3
1 1064 2 1064 3
1 1064 3 1065 1
1 1065 1 1065 2
1 1065 2 1065 3
1 1065 3 1066 1
1 1065 4 1047 1
1 1065 5 887 1
1 1065 6 1066 5
1 1066 1 1066 2
1 1066 2 1065 4
4 1066 3 887 8
2 1066 4 887 7
1 1066 5 1066 6
1 1066 6 1066 7
1 1066 7 1066 8
1 1066 8 1066 9
1 1066 9 1066 10
1 1066 10 887 2
1 1067 1 1067 2
1 1067 2 1037 1
2 1067 3 1068 2
1 1067 4 1067 6
2 1067 5 1068 5
1 1067 6 1067 7
1 1067 7 1068 6
1 1067 8 1067 9
1 1067 9 1067 10
1 1067 10 1067 11
1 1067 11 1067 12
1 1067 12 1070 3
1 1067 13 1073 1
1 1067 14 887 17
1 1068 1 1045 1
2 1068 2 1068 3
2 1068 3 1068 4
2 1068 4 1067 5
2 1068 5 1070 2
1 1068 6 1067 8
1 1070 1 1047 2
2 1070 2 NULL NULL
1 1070 3 1067 13
1 1070 4 1070 5
1 1070 5 1076 1
1 1070 6 1067 14
1 1071 1 1008 1
1 1071 2 887 5
1 1072 1 887 3
1 1073 1 1070 4
1 1073 2 1078 1
4 1074 1 1074 2
4 1074 2 1074 3
4 1074 3 1074 4
4 1074 4 1075 1
4 1075 1 1075 2
4 1075 2 1075 3
4 1075 3 1075 4
4 1075 4 887 16
1 1076 1 1076 2
1 1076 2 1076 3
1 1076 3 1077 1
1 1077 1 1077 2
1 1077 2 1077 3
1 1077 3 1077 4
1 1077 4 1073 2
1 1078 1 1078 2
1 1078 2 1078 3
1 1078 3 1078 4
1 1078 4 1078 5
1 1078 5 1079 1
1 1079 1 1079 2
1 1079 2 1079 3
1 1079 3 887 15
...全文
请发表友善的回复…
发表回复
唐诗三百首 2019-07-17
- 打赏
- 举报
create table #t(userid int,docnum int,linenum int,nextdocnum int,nextdocline int)
insert into #t
select 1,887,1,1065,6 union all
select 1,887,2,927,1 union all
select 1,887,3,887,4 union all
select 1,1066,5,1067,2 union all
select 1,887,4,1071,2 union all
select 1,887,5,887,6 union all
select 2,887,7,887,12 union all
select 1,887,6,887,14 union all
select 1,1065,6,1066,5
with u as(
select a.*,rn2=(row_number() over(order by getdate()))*1000000
from #t a
where not exists(select 1 from #t b where b.userid=a.userid and b.nextdocnum=a.docnum and b.nextdocline=a.linenum)
union all
select d.*,rn2=c.rn2+1
from u c
inner join #t d on c.userid=d.userid and c.nextdocnum=d.docnum and c.nextdocline=d.linenum
)
select userid,docnum,linenum,nextdocnum,nextdocline
from u
order by rn2
/*
userid docnum linenum nextdocnum nextdocline
----------- ----------- ----------- ----------- -----------
1 887 1 1065 6
1 1065 6 1066 5
1 1066 5 1067 2
1 887 2 927 1
1 887 3 887 4
1 887 4 1071 2
1 887 5 887 6
1 887 6 887 14
2 887 7 887 12
(9 行受影响)
*/
听雨停了 2019-07-17
- 打赏
- 举报
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
if not object_id(N'Tempdb..#tab') is null
drop table #tab
Go
Create table #tab([userid] int,[docnum] int,[linenum] int,[nextdocnum] int,[nextdocline] int)
Insert #tab
select 1,887,1,1065,6 union all
select 1,887,2,927,1 union all
select 1,887,3,887,4 union all
select 1,887,4,1071,2 union all
select 1,887,5,887,6 union all
select 1,887,6,887,14 union all
select 2,887,7,887,12 union all
select 4,887,8,887,9 union all
select 4,887,9,887,10 union all
select 4,887,10,887,11 union all
select 4,887,11,1074,1 union all
select 2,887,12,887,13 union all
select 2,887,13,1046,1 union all
select 1,887,14,1067,4 union all
select 1,887,15,1070,6 union all
select 4,887,16,null,null union all
select 1,887,17,null,null union all
select 1,927,1,1038,2 union all
select 1,976,1,1063,1 union all
select 1,1008,1,1008,2 union all
select 1,1008,2,1008,3 union all
select 1,1008,3,1072,1 union all
select 1,1037,1,1037,2 union all
select 1,1037,2,1037,3 union all
select 1,1037,3,1037,4 union all
select 1,1037,4,1068,1 union all
select 1,1038,1,1065,5 union all
select 1,1038,2,1038,3 union all
select 1,1038,3,1038,4 union all
select 1,1038,4,1067,1 union all
select 1,1045,1,1045,2 union all
select 1,1045,2,1045,3 union all
select 1,1045,3,1070,1 union all
select 2,1046,1,1046,2 union all
select 2,1046,2,1067,3 union all
select 1,1047,1,1038,1 union all
select 1,1047,2,1047,3 union all
select 1,1047,3,1047,4 union all
select 1,1047,4,1071,1 union all
select 1,1063,1,1064,2 union all
select 4,1064,1,1066,3 union all
select 1,1064,2,1064,3 union all
select 1,1064,3,1065,1 union all
select 1,1065,1,1065,2 union all
select 1,1065,2,1065,3 union all
select 1,1065,3,1066,1 union all
select 1,1065,4,1047,1 union all
select 1,1065,5,887,1 union all
select 1,1065,6,1066,5 union all
select 1,1066,1,1066,2 union all
select 1,1066,2,1065,4 union all
select 4,1066,3,887,8 union all
select 2,1066,4,887,7 union all
select 1,1066,5,1066,6 union all
select 1,1066,6,1066,7 union all
select 1,1066,7,1066,8 union all
select 1,1066,8,1066,9 union all
select 1,1066,9,1066,10 union all
select 1,1066,10,887,2 union all
select 1,1067,1,1067,2 union all
select 1,1067,2,1037,1 union all
select 2,1067,3,1068,2 union all
select 1,1067,4,1067,6 union all
select 2,1067,5,1068,5 union all
select 1,1067,6,1067,7 union all
select 1,1067,7,1068,6 union all
select 1,1067,8,1067,9 union all
select 1,1067,9,1067,10 union all
select 1,1067,10,1067,11 union all
select 1,1067,11,1067,12 union all
select 1,1067,12,1070,3 union all
select 1,1067,13,1073,1 union all
select 1,1067,14,887,17 union all
select 1,1068,1,1045,1 union all
select 2,1068,2,1068,3 union all
select 2,1068,3,1068,4 union all
select 2,1068,4,1067,5 union all
select 2,1068,5,1070,2 union all
select 1,1068,6,1067,8 union all
select 1,1070,1,1047,2 union all
select 2,1070,2,null,null union all
select 1,1070,3,1067,13 union all
select 1,1070,4,1070,5 union all
select 1,1070,5,1076,1 union all
select 1,1070,6,1067,14 union all
select 1,1071,1,1008,1 union all
select 1,1071,2,887,5 union all
select 1,1072,1,887,3 union all
select 1,1073,1,1070,4 union all
select 1,1073,2,1078,1 union all
select 4,1074,1,1074,2 union all
select 4,1074,2,1074,3 union all
select 4,1074,3,1074,4 union all
select 4,1074,4,1075,1 union all
select 4,1075,1,1075,2 union all
select 4,1075,2,1075,3 union all
select 4,1075,3,1075,4 union all
select 4,1075,4,887,16 union all
select 1,1076,1,1076,2 union all
select 1,1076,2,1076,3 union all
select 1,1076,3,1077,1 union all
select 1,1077,1,1077,2 union all
select 1,1077,2,1077,3 union all
select 1,1077,3,1077,4 union all
select 1,1077,4,1073,2 union all
select 1,1078,1,1078,2 union all
select 1,1078,2,1078,3 union all
select 1,1078,3,1078,4 union all
select 1,1078,4,1078,5 union all
select 1,1078,5,1079,1 union all
select 1,1079,1,1079,2 union all
select 1,1079,2,1079,3 union all
select 1,1079,3,887,15
GO
--测试数据结束
;with cte(userid, docnum, linenum, nextdocnum, nextdocline,rn) as
(
select userid, docnum, linenum, nextdocnum, nextdocline,1 AS rn from #tab
WHERE nextdocnum IS NULL
union all
SELECT a.*,b.rn+1 AS rn FROM #tab a
inner join cte b ON b.docnum=a.nextdocnum AND b.linenum=a.nextdocline AND a.userid=b.userid
)
SELECT userid, docnum, linenum, nextdocnum, nextdocline FROM cte
ORDER BY userid,rn DESC
userid docnum linenum nextdocnum nextdocline
----------- ----------- ----------- ----------- -----------
1 976 1 1063 1
1 1063 1 1064 2
1 1064 2 1064 3
1 1064 3 1065 1
1 1065 1 1065 2
1 1065 2 1065 3
1 1065 3 1066 1
1 1066 1 1066 2
1 1066 2 1065 4
1 1065 4 1047 1
1 1047 1 1038 1
1 1038 1 1065 5
1 1065 5 887 1
1 887 1 1065 6
1 1065 6 1066 5
1 1066 5 1066 6
1 1066 6 1066 7
1 1066 7 1066 8
1 1066 8 1066 9
1 1066 9 1066 10
1 1066 10 887 2
1 887 2 927 1
1 927 1 1038 2
1 1038 2 1038 3
1 1038 3 1038 4
1 1038 4 1067 1
1 1067 1 1067 2
1 1067 2 1037 1
1 1037 1 1037 2
1 1037 2 1037 3
1 1037 3 1037 4
1 1037 4 1068 1
1 1068 1 1045 1
1 1045 1 1045 2
1 1045 2 1045 3
1 1045 3 1070 1
1 1070 1 1047 2
1 1047 2 1047 3
1 1047 3 1047 4
1 1047 4 1071 1
1 1071 1 1008 1
1 1008 1 1008 2
1 1008 2 1008 3
1 1008 3 1072 1
1 1072 1 887 3
1 887 3 887 4
1 887 4 1071 2
1 1071 2 887 5
1 887 5 887 6
1 887 6 887 14
1 887 14 1067 4
1 1067 4 1067 6
1 1067 6 1067 7
1 1067 7 1068 6
1 1068 6 1067 8
1 1067 8 1067 9
1 1067 9 1067 10
1 1067 10 1067 11
1 1067 11 1067 12
1 1067 12 1070 3
1 1070 3 1067 13
1 1067 13 1073 1
1 1073 1 1070 4
1 1070 4 1070 5
1 1070 5 1076 1
1 1076 1 1076 2
1 1076 2 1076 3
1 1076 3 1077 1
1 1077 1 1077 2
1 1077 2 1077 3
1 1077 3 1077 4
1 1077 4 1073 2
1 1073 2 1078 1
1 1078 1 1078 2
1 1078 2 1078 3
1 1078 3 1078 4
1 1078 4 1078 5
1 1078 5 1079 1
1 1079 1 1079 2
1 1079 2 1079 3
1 1079 3 887 15
1 887 15 1070 6
1 1070 6 1067 14
1 1067 14 887 17
1 887 17 NULL NULL
2 1066 4 887 7
2 887 7 887 12
2 887 12 887 13
2 887 13 1046 1
2 1046 1 1046 2
2 1046 2 1067 3
2 1067 3 1068 2
2 1068 2 1068 3
2 1068 3 1068 4
2 1068 4 1067 5
2 1067 5 1068 5
2 1068 5 1070 2
2 1070 2 NULL NULL
4 1064 1 1066 3
4 1066 3 887 8
4 887 8 887 9
4 887 9 887 10
4 887 10 887 11
4 887 11 1074 1
4 1074 1 1074 2
4 1074 2 1074 3
4 1074 3 1074 4
4 1074 4 1075 1
4 1075 1 1075 2
4 1075 2 1075
