# 求一SQL语句的写法，简单点，最好能用一条SQL语句解决。

www.myexceptions.net  网友分享于：2013-03-31  浏览：2次

Create Table T1(
type int,
beginid int,
endid int,
moeny int,
status int
)

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

------解决方案--------------------
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```