CREATE TABLE [orders_bak] (
[ID] [int] IDENTITY (1, 1) NOT NULL,
[orderid] [varchar] (50) NULL)SET IDENTITY_INSERT [orders_bak] ONINSERT [orders_bak] ([ID],[orderid]) VALUES ( 1,N'D00000001')
INSERT [orders_bak] ([ID],[orderid]) VALUES ( 2,N'D00000002')
INSERT [orders_bak] ([ID],[orderid]) VALUES ( 3,N'D00000003')
INSERT [orders_bak] ([ID],[orderid]) VALUES ( 4,N'D00000004')
INSERT [orders_bak] ([ID],[orderid]) VALUES ( 5,N'D00000005')
INSERT [orders_bak] ([ID],[orderid]) VALUES ( 6,N'D00000006')SET IDENTITY_INSERT [orders_bak] OFF
CREATE TABLE [orderdetail_bak] (
[ID] [int] IDENTITY (1, 1) NOT NULL,
[orderid] [varchar] (50) NULL,
[ordernum] [int] NULL,
[beiginid] [int] NULL,
[endid] [int] NULL)SET IDENTITY_INSERT [orderdetail_bak] ONINSERT [orderdetail_bak] ([ID],[orderid],[ordernum],[beiginid],[endid]) VALUES ( 1,N'D00000001',1,1,1)
INSERT [orderdetail_bak] ([ID],[orderid],[ordernum],[beiginid],[endid]) VALUES ( 2,N'D00000001',1,2,2)
INSERT [orderdetail_bak] ([ID],[orderid],[ordernum],[beiginid],[endid]) VALUES ( 3,N'D00000002',2,3,4)
INSERT [orderdetail_bak] ([ID],[orderid],[ordernum],[beiginid],[endid]) VALUES ( 4,N'D00000003',1,5,5)
INSERT [orderdetail_bak] ([ID],[orderid],[ordernum],[beiginid],[endid]) VALUES ( 5,N'D00000004',1,6,6)
INSERT [orderdetail_bak] ([ID],[orderid],[ordernum],[beiginid],[endid]) VALUES ( 6,N'D00000004',1,6,6)
INSERT [orderdetail_bak] ([ID],[orderid],[ordernum],[beiginid],[endid]) VALUES ( 7,N'D00000005',2,7,8)
INSERT [orderdetail_bak] ([ID],[orderid],[ordernum],[beiginid],[endid]) VALUES ( 8,N'D00000006',1,9,9)SET IDENTITY_INSERT [orderdetail_bak] OFF
D00000001 1
D00000002 2
D00000002 2
D00000003 3
D00000003 3
D00000003 3就是说订单数有多少 那么这个订单记录就有多少条
WITH test as
(
SELECT ROW_NUMBER() OVER(ORDER BY ID) AS ID1 --保证连续ID
,*
FROM [orderdetail_bak]
)
,test1 AS
(
SELECT *,ordernum AS usercheck FROM test WHERE ID1=1
UNION ALL
SELECT A.*,CASE WHEN B.usercheck >1 THEN B.usercheck-1 ELSE A.OrderNum END as usercheck
FROM test AS A ,test1 AS B
WHERE A.ID1=CASE WHEN usercheck>1 THEN B.ID1
WHEN usercheck=1 THEN B.ID1+1 END
)
SELECT ID
,orderid
,ordernum
,beiginid
,endid
FROM test1
按你的数据D00000001是有2条啊
INSERT [orderdetail_bak] ([ID],[orderid],[ordernum],[beiginid],[endid]) VALUES ( 1,N'D00000001',1,1,1)
INSERT [orderdetail_bak] ([ID],[orderid],[ordernum],[beiginid],[endid]) VALUES ( 2,N'D00000001',1,2,2)
D00000004也是2条
按你的数据D00000001是有2条啊
INSERT [orderdetail_bak] ([ID],[orderid],[ordernum],[beiginid],[endid]) VALUES ( 1,N'D00000001',1,1,1)
INSERT [orderdetail_bak] ([ID],[orderid],[ordernum],[beiginid],[endid]) VALUES ( 2,N'D00000001',1,2,2)
D00000004也是2条
是的 ,你是对的,我看错了 但是 如果 ORDERNUM=3 或者 4 呢 就不准了
1 D00000001 1 1 1
2 D00000001 1 2 2
3 D00000002 2 3 4
3 D00000002 2 3 4
4 D00000003 1 5 5
5 D00000004 1 6 6
6 D00000004 1 7 7
7 D00000005 2 8 9
7 D00000005 2 8 9
8 D00000006 1 10 10
9 D00000007 3 11 13
9 D00000007 3 11 13
9 D00000007 3 11 13其实就是为每一个商品分配一个编号 要连续 endid可以不管
我要的就是ID orderid ordernum beiginid endid
1 D00000001 1 1 1
2 D00000001 1 2 2
3 D00000002 2 3 4
3 D00000002 2 4 4
4 D00000003 1 5 5
5 D00000004 1 6 6
6 D00000004 1 7 7
7 D00000005 2 8 9
7 D00000005 2 9 9
8 D00000006 1 10 10
9 D00000007 3 11 13
9 D00000007 3 12 13
9 D00000007 3 13 13
这就是我想要的了~
WITH test as
(
SELECT ROW_NUMBER() OVER(ORDER BY ID) AS ID1 --保证连续ID
,*
FROM [orderdetail_bak]
)
,test1 AS
(
SELECT *,ordernum AS usercheck FROM test WHERE ID1=1
UNION ALL
SELECT A.*,CASE WHEN B.usercheck >1 THEN B.usercheck-1 ELSE A.OrderNum END as usercheck
FROM test AS A ,test1 AS B
WHERE A.ID1=CASE WHEN usercheck>1 THEN B.ID1
WHEN usercheck=1 THEN B.ID1+1 END
)
SELECT ID
,orderid
,ordernum
,ROW_NUMBER() over(order by getdate()) AS beiginid
,endid
FROM test1
WITH test as
(
SELECT ROW_NUMBER() OVER(ORDER BY ID) AS ID1 --保证连续ID
,*
FROM [orderdetail_bak]
)
,test1 AS
(
SELECT *,1 AS BeginID,ordernum AS usercheck FROM test WHERE ID1=1
UNION ALL
SELECT A.*,B.BeginID+1 AS BeginID,CASE WHEN B.usercheck >1 THEN B.usercheck-1 ELSE A.OrderNum END as usercheck
FROM test AS A ,test1 AS B
WHERE A.ID1=CASE WHEN usercheck>1 THEN B.ID1
WHEN usercheck=1 THEN B.ID1+1 END
)
SELECT ID
,orderid
,ordernum
,BeginID AS beiginid
,endid
FROM test1