Create Table T1(
type int,
beginid int,
endid int,
moeny int,
status int
)
其中 type,beginid,endid 都是主键，建的复合索引，
已知数据
type beginid endid money status
1 1 1 0 0
1 1 2 0 0
1 2 1 0 0
1 2 2 0 0
2 1 1 0 0
2 1 2 0 0
该表只进行更新操作， 每次更新 type,beginid,endid 最小 并且status=0的记录。
------解决方案--------------------
- SQL code
Create Table T1(type int,beginid int,endid int,moeny int,status int)insert into T1 values (1,1,1,0,0),(1,1,1,0,0), (1,1,2,0,0),(1,2,1,0,0),(1,2,2,0,0),(2,1,1,0,0), (2,1,2,0,0)declare @talbe table (type int,beginid int,endid int,status int); --（可能会查询到多条结果，所以先用表变量保存结果，方便后面更新）insert into @talbe select T1.type,T1.beginid,T1.endid,T1.status from T1, (select min(type) as type from T1 where status=0) a, (select min(beginid) as beginid from T1 where status=0)b, (select min(endid) as endid from T1 where status=0)cwhere T1.status=0 and T1.type=a.type and T1.beginid=b.beginid and T1.endid=c.endidUPDATE T1 SET moeny ='20' --要更新的money值WHERE T1.type IN (SELECT type FROM @talbe) AND T1.beginid IN (SELECT beginid FROM @talbe) AND T1.endid IN (SELECT endid FROM @talbe) AND T1.status IN (SELECT status FROM @talbe)SELECT * FROM T1--SQLSERVER 2008下测试同过
------解决方案--------------------
- SQL code
select *from tb twhere status = 0 and not exists (select 1 from tb where status = t.status and ([type] < t.[type] or ([type]=t.[type] and beginid < t.beginid) or ([type]=t.[type] and beginid = t.beginid and endid < t.endid)))
------解决方案--------------------
- SQL code
update table as a set money='{0}' ,status='{1}' from (select min(type) as type,min(beginid) as beginid ,min(endid) as endid from table)t where a.type=t.type and a.beginid=t.beginid and a.endid=t.endid
------解决方案--------------------
- SQL code
update table as a set money='{0}' ,status='{1}' from (select min(type) as type,min(beginid) as beginid ,min(endid) as endid from table)t where a.type=t.type and a.beginid=t.beginid and a.endid=t.endid
------解决方案--------------------
update table as a set money='{0}' ,status='{1}'
from (select min(type) as type,min(beginid) as beginid ,min(endid) as endid
from table)t where a.type=t.type or a.beginid=t.beginid or a.endid=t.endid