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)c
where T1.status=0 and T1.type=a.type and T1.beginid=b.beginid and T1.endid=c.endid
UPDATE 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 t
where 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
