更新状态(条件：相同料号，相同批次，不同sn分次更新)

yelang771 2011-12-12 03:54:33

index stockcode sn batchid qty status
1 A111 1001 2011001 10 0
2 A111 1001 2011001 20 0
3 A112 1001 2011002 20 0
4 A113 1001 2011003 10 0
5 A111 1002 2011001 20 0
6 A999 1002 2011111 100 0

相同料号，相同批次，不同sn分次更新
如上：1,2&5sn不同，料号，批次相同，更新状态的时候只选择sn较大或较小的更新。
更新1,2,3,4,6或者3,4,5,6
更新status 0->1

谢谢

...全文

96 5 打赏收藏转发到动态举报

写回复

用AI写文章

5 条回复

切换为时间正序

请发表友善的回复…

发表回复

-晴天 2011-12-12

打赏
举报

create table tb([index] int,kstockcode varchar(10),sn varchar(10),batchid varchar(10),qty varchar(10),status int)

insert into tb select 1,'A111','1001','2011001','10',0

insert into tb select 2,'A111','1001','2011001','20',0

insert into tb select 3,'A112','1001','2011002','20',0

insert into tb select 4,'A113','1001','2011003','10',0

insert into tb select 5,'A111','1002','2011001','20',0

insert into tb select 6,'A999','1002','2011111','100',0

go

update a set status=1 from tb a 

where not exists(select 1 from tb where kstockcode=a.kstockcode and batchid=a.batchid and sn>a.sn) --如选较小更新的话,把>改成<

select * from tb

/*

index       kstockcode sn         batchid    qty        status

----------- ---------- ---------- ---------- ---------- -----------

1           A111       1001       2011001    10         0

2           A111       1001       2011001    20         0

3           A112       1001       2011002    20         1

4           A113       1001       2011003    10         1

5           A111       1002       2011001    20         1

6           A999       1002       2011111    100        1



(6 行受影响)



*/

go

drop table tb

--小F-- 2011-12-12

打赏
举报

----------------------------------------------------------------

-- Author  :fredrickhu(小F，向高手学习)

-- Date    :2011-12-12 16:09:13

-- 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)

--

----------------------------------------------------------------

--> 测试数据：[tb]

if object_id('[tb]') is not null drop table [tb]

go 

create table [tb]([index] int,[stockcode] varchar(4),[sn] int,[batchid] int,[qty] int,[status] int)

insert [tb]

select 1,'A111',1001,2011001,10,0 union all

select 2,'A111',1001,2011001,20,0 union all

select 3,'A112',1001,2011002,20,0 union all

select 4,'A113',1001,2011003,10,0 union all

select 5,'A111',1002,2011001,20,0 union all

select 6,'A999',1002,2011111,100,0

--------------开始查询--------------------------

update 

   t

 set

   status=1

 from

   tb t

 where 

   sn=(select Min(sn) from tb where stockcode=t.stockcode and batchid=t.batchid)

 

 

 select * from tb 

----------------结果----------------------------

/* index       stockcode sn          batchid     qty         status

----------- --------- ----------- ----------- ----------- -----------

1           A111      1001        2011001     10          1

2           A111      1001        2011001     20          1

3           A112      1001        2011002     20          1

4           A113      1001        2011003     10          1

5           A111      1002        2011001     20          0

6           A999      1002        2011111     100         1



(6 行受影响)



*/

--小F-- 2011-12-12

打赏
举报

----------------------------------------------------------------

-- Author  :fredrickhu(小F，向高手学习)

-- Date    :2011-12-12 16:09:13

-- 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)

--

----------------------------------------------------------------

--> 测试数据：[tb]

if object_id('[tb]') is not null drop table [tb]

go 

create table [tb]([index] int,[stockcode] varchar(4),[sn] int,[batchid] int,[qty] int,[status] int)

insert [tb]

select 1,'A111',1001,2011001,10,0 union all

select 2,'A111',1001,2011001,20,0 union all

select 3,'A112',1001,2011002,20,0 union all

select 4,'A113',1001,2011003,10,0 union all

select 5,'A111',1002,2011001,20,0 union all

select 6,'A999',1002,2011111,100,0

--------------开始查询--------------------------

update 

   t

 set

   status=1

 from

   tb t

 where 

   sn=(select MAX(sn) from tb where stockcode=t.stockcode and batchid=t.batchid)

 

 

 select * from tb 

----------------结果----------------------------

/* index       stockcode sn          batchid     qty         status

----------- --------- ----------- ----------- ----------- -----------

1           A111      1001        2011001     10          0

2           A111      1001        2011001     20          0

3           A112      1001        2011002     20          1

4           A113      1001        2011003     10          1

5           A111      1002        2011001     20          1

6           A999      1002        2011111     100         1



(6 行受影响)



*/

yelang771 2011-12-12

打赏
举报

结果

1 A111 1001 2011001 10 1
2 A111 1001 2011001 20 1
3 A112 1001 2011002 20 1
4 A113 1001 2011003 10 1
5 A111 1002 2011001 20 0
6 A999 1002 2011111 100 1
或者
1 A111 1001 2011001 10 0
2 A111 1001 2011001 20 0
3 A112 1001 2011002 20 1
4 A113 1001 2011003 10 1
5 A111 1002 2011001 20 1
6 A999 1002 2011111 100 1

--小F-- 2011-12-12