已知表test1
数据如下:
Max_Bill_ID Max_List_ID
5 10
-----------------------------------------
test2的数据如下: List_ID为表test2的主键
Bill_ID List_ID
86 1606
86 1608
87 1618
-----------------------------------------
test3的数据如下
Bill_ID List_ID Color_ID Size_ID Prod_Number
86 1606 828 833 1.00000000
86 1606 828 834 2.00000000
86 1606 828 835 3.00000000
86 1608 828 833 1.00000000
86 1608 828 834 2.00000000
86 1608 828 835 3.00000000
87 1618 828 833 1.00000000
87 1618 828 834 2.00000000
87 1618 828 835 3.00000000
---------------------
现在以test1为准重新分配编号
更新test2的结果如下:
Bill_ID List_ID
5 10
5 11
5 12
更新test3的结果如下:
Bill_ID List_ID Color_ID Size_ID Prod_Number
5 10 828 833 1.00000000
5 10 828 834 2.00000000
5 10 828 835 3.00000000
5 11 828 833 1.00000000
5 11 828 834 2.00000000
5 11 828 835 3.00000000
5 12 828 833 1.00000000
5 12 828 834 2.00000000
5 12 828 835 3.00000000
最后更新test3结果如下:
Max_Bill_ID Max_List_ID
6 13
----------------------------------------
这样的重新分配编号的更新这三个表的查询怎么写呀?
List_ID的更新就是按照表test3的Max_List_ID为起始值重新进行流水号分配
------解决方案--------------------
- SQL code
------------------------------ Author :fredrickhu(小F，向高手学习)-- Date :2012-01-09 09:13:55-- Version:-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) -- Apr 22 2011 11:57:00 -- Copyright (c) Microsoft Corporation-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)--------------------------------> 测试数据：[test1]if object_id('[test1]') is not null drop table [test1]go create table [test1]([Max_Bill_ID] int,[Max_List_ID] int)insert [test1]select 5,10--> 测试数据：[test2]if object_id('[test2]') is not null drop table [test2]go create table [test2]([Bill_ID] int,[List_ID] int)insert [test2]select 86,1606 union allselect 86,1608 union allselect 87,1618--> 测试数据：[test3]if object_id('[test3]') is not null drop table [test3]go create table [test3]([Bill_ID] int,[List_ID] int,[Color_ID] int,[Size_ID] int,[Prod_Number] numeric(9,8))insert [test3]select 86,1606,828,833,1.00000000 union allselect 86,1606,828,834,2.00000000 union allselect 86,1606,828,835,3.00000000 union allselect 86,1608,828,833,1.00000000 union allselect 86,1608,828,834,2.00000000 union allselect 86,1608,828,835,3.00000000 union allselect 87,1618,828,833,1.00000000 union allselect 87,1618,828,834,2.00000000 union allselect 87,1618,828,835,3.00000000--------------开始查询--------------------------update cset Bill_ID=a.[Max_Bill_ID] from test1 a,test3 cupdate bset Bill_ID=a.[Max_Bill_ID],List_ID=a.Max_List_ID+b.pxfrom test1 a, (select row_number()over(order by getdate()) as px,* from test2)b select * from test3 select * from test2----------------结果----------------------------/* (1 行受影响)(3 行受影响)(9 行受影响)(9 行受影响)(3 行受影响)Bill_ID List_ID Color_ID Size_ID Prod_Number----------- ----------- ----------- ----------- ---------------------------------------5 1606 828 833 1.000000005 1606 828 834 2.000000005 1606 828 835 3.000000005 1608 828 833 1.000000005 1608 828 834 2.000000005 1608 828 835 3.000000005 1618 828 833 1.000000005 1618 828 834 2.000000005 1618 828 835 3.00000000(9 行受影响)Bill_ID List_ID----------- -----------5 115 125 13(3 行受影响)*/