相同列的两个数值对调

相同列的两个数值对调

有一个表有id,姓名,日期，和上班时间。上班时间由星期一到星期六，星期六算加班，星期一到星期五的上班时间去最小的值跟星期六上班时间对调！
例如：
tid  tname  tdate      thour
1    allen  2009-3-2  3.5
2    allen  2009-3-3  5
3    allen  2009-3-4  7
4    allen  2009-3-5  8
5    allen  2009-3-6  4
6    allen  2009-3-7  6

7    bill  2009-3-2  5
8    bill  2009-3-3  6
9    bill  2009-3-4  3
10  bill  2009-3-5  8
11  bill  2009-3-6  8
12  bill  2009-3-7  6
根据上表，名字为allen的要把星期一（2009-3-2）和星期六（2009-3-7）对调，星期一的上班时间为6小时，星期六的上班时间为3.5小时；
名字为bill的要把星期三（2009-3-4）和星期六（2009-3-7）对调，星期一的上班时间为6小时，星期六的上班时间为3.5小时；
需要计算一个月的数据

SQL codecreate table tb(tid int, tname varchar(10), tdate datetime, thour decimal(18,1))
insert into tb values(1 ,   'allen' , '2009-3-2' , 3.5)
insert into tb values(2 ,   'allen' , '2009-3-3' , 5 )
insert into tb values(3 ,   'allen' , '2009-3-4' , 7 )
insert into tb values(4 ,   'allen' , '2009-3-5' , 8 )
insert into tb values(5 ,   'allen' , '2009-3-6' , 4 )
insert into tb values(6 ,   'allen' , '2009-3-7' , 6 )
insert into tb values(7 ,   'bill'  , '2009-3-2' , 5 )
insert into tb values(8 ,   'bill'  , '2009-3-3' , 6 )
insert into tb values(9 ,   'bill'  , '2009-3-4' , 3 )
insert into tb values(10,   'bill'  , '2009-3-5' , 8 )
insert into tb values(11,   'bill'  , '2009-3-6' , 8 )
insert into tb values(12,   'bill'  , '2009-3-7' , 6 )
go

select tid ,
       tname ,
       tdate ,
       thour = (case when thour = (select min(thour) from tb where tname = t.tname and datepart(week , tdate) = datepart(week,t.tdate))
                        then (select top 1 thour from tb where tname = t.tname and datepart(week , tdate) = datepart(week , t.tdate) order by tdate desc)
                        when datepart(weekday , tdate) = 7
                        then (select min(thour) from tb where tname = t.tname and datepart(week , tdate) = datepart(week , t.tdate))
                   else thour
                   end)
from tb t

drop table tb

/*
tid         tname      tdate                                                  thour               
----------- ---------- ------------------------------------------------------ --------------------
1           allen      2009-03-02 00:00:00.000                                6.0
2           allen      2009-03-03 00:00:00.000                                5.0
3           allen      2009-03-04 00:00:00.000                                7.0
4           allen      2009-03-05 00:00:00.000                                8.0
5           allen      2009-03-06 00:00:00.000                                4.0
6           allen      2009-03-07 00:00:00.000                                3.5
7           bill       2009-03-02 00:00:00.000                                5.0
8           bill       2009-03-03 00:00:00.000                                6.0
9           bill       2009-03-04 00:00:00.000                                6.0
10          bill       2009-03-05 00:00:00.000                                8.0
11          bill       2009-03-06 00:00:00.000                                8.0
12          bill       2009-03-07 00:00:00.000                                3.0

（所影响的行数为 12 行）
*/

SQL codecreate table tb(tid int, tname varchar(10), tdate datetime, thour decimal(18,1))
insert into tb values(1 ,   'allen' , '2009-3-2' , 3.5)
insert into tb values(2 ,   'allen' , '2009-3-3' , 5 )
insert into tb values(3 ,   'allen' , '2009-3-4' , 7 )
insert into tb values(4 ,   'allen' , '2009-3-5' , 8 )
insert into tb values(5 ,   'allen' , '2009-3-6' , 4 )
insert into tb values(6 ,   'allen' , '2009-3-7' , 6 )
insert into tb values(7 ,   'bill'  , '2009-3-2' , 5 )
insert into tb values(8 ,   'bill'  , '2009-3-3' , 6 )
insert into tb values(9 ,   'bill'  , '2009-3-4' , 3 )
insert into tb values(10,   'bill'  , '2009-3-5' , 8 )
insert into tb values(11,   'bill'  , '2009-3-6' , 8 )
insert into tb values(12,   'bill'  , '2009-3-7' , 6 )
go

select * ,
       newthour = (case when thour = (select min(thour) from tb where tname = t.tname and datepart(week , tdate) = datepart(week,t.tdate))
                        then (select top 1 thour from tb where tname = t.tname and datepart(week , tdate) = datepart(week , t.tdate) order by tdate desc)
                        when datepart(weekday , tdate) = 7
                        then (select min(thour) from tb where tname = t.tname and datepart(week , tdate) = datepart(week , t.tdate))
                   else thour
                   end)
from tb t

drop table tb

/*
tid         tname      tdate                                                  thour                newthour            
----------- ---------- ------------------------------------------------------ -------------------- --------------------
1           allen      2009-03-02 00:00:00.000                                3.5                  6.0
2           allen      2009-03-03 00:00:00.000                                5.0                  5.0
3           allen      2009-03-04 00:00:00.000                                7.0                  7.0
4           allen      2009-03-05 00:00:00.000                                8.0                  8.0
5           allen      2009-03-06 00:00:00.000                                4.0                  4.0
6           allen      2009-03-07 00:00:00.000                                6.0                  3.5
7           bill       2009-03-02 00:00:00.000                                5.0                  5.0
8           bill       2009-03-03 00:00:00.000                                6.0                  6.0
9           bill       2009-03-04 00:00:00.000                                3.0                  6.0
10          bill       2009-03-05 00:00:00.000                                8.0                  8.0
11          bill       2009-03-06 00:00:00.000                                8.0                  8.0
12          bill       2009-03-07 00:00:00.000                                6.0                  3.0

（所影响的行数为 12 行）
*/
 
 varchar(10), tdate datetime, thour decimal(18,1))
insert into tb values(1 ,   'allen' , '2009-3-2' , 3.5)
insert into tb values(2 ,   'allen' , '2009-3-3' , 5 )
insert into tb values(3 ,   'allen' , '2009-3-4' , 7 )
insert into tb values(4 ,   'allen' , '2009-3-5' , 8 )
insert into tb values(5 ,   'allen' , '2009-3-6' , 4 )
insert into tb values(6 ,   'allen' , '2009-3-7' , 6 )
insert into tb values(7 ,   'bill'  , '2009-3-2' , 5 )
insert into tb values(8 ,   'bill'  , '2009-3-3' , 6 )
insert into tb values(9 ,   'bill'  , '2009-3-4' , 3 )
insert into tb values(10,   'bill'  , '2009-3-5' , 8 )
insert into tb values(11,   'bill'  , '2009-3-6' , 8 )
insert into tb values(12,   'bill'  , '2009-3-7' , 6 )
go

select tid ,
       tname ,
       tdate ,
       thour = (case when thour = (select min(thour) from tb where tname = t.tname and datepart(week , tdate) = datepart(week,t.tdate))
                        then (select thour from tb where tname = t.tname and datepart(week , tdate) = datepart(week , t.tdate) and datepart(weekday , tdate) = 7)
                        when datepart(weekday , tdate) = 7
                        then (select min(thour) from tb where tname = t.tname and datepart(week , tdate) = datepart(week , t.tdate))
                   else thour
                   end)
from tb t

drop table tb

/*
tid         tname      tdate                                                  thour               
----------- ---------- ------------------------------------------------------ --------------------
1           allen      2009-03-02 00:00:00.000                                6.0
2           allen      2009-03-03 00:00:00.000                                5.0
3           allen      2009-03-04 00:00:00.000                                7.0
4           allen      2009-03-05 00:00:00.000                                8.0
5           allen      2009-03-06 00:00:00.000                                4.0
6           allen      2009-03-07 00:00:00.000                                3.5
7           bill       2009-03-02 00:00:00.000                                5.0
8           bill       2009-03-03 00:00:00.000                                6.0
9           bill       2009-03-04 00:00:00.000                                6.0
10          bill       2009-03-05 00:00:00.000                                8.0
11          bill       2009-03-06 00:00:00.000                                8.0
12          bill       2009-03-07 00:00:00.000                                3.0

（所影响的行数为 12 行）
*/
 
 

--以下是考虑有相同最小时,只替换第一个,况且周六的值比周一到周五的值还小时,不替换.

SQL codecreate table tb(tid int, tname varchar(10), tdate datetime, thour decimal(18,1))
insert into tb values(1 ,   'allen' , '2009-3-2' , 3.5)
insert into tb values(2 ,   'allen' , '2009-3-3' , 3.5 )
insert into tb values(3 ,   'allen' , '2009-3-4' , 7 )
insert into tb values(4 ,   'allen' , '2009-3-5' , 8 )
insert into tb values(5 ,   'allen' , '2009-3-6' , 4 )
insert into tb values(6 ,   'allen' , '2009-3-7' , 6 )
insert into tb values(7 ,   'bill'  , '2009-3-2' , 5 )
insert into tb values(8 ,   'bill'  , '2009-3-3' , 6 )
insert into tb values(9 ,   'bill'  , '2009-3-4' , 3 )
insert into tb values(10,   'bill'  , '2009-3-5' , 8 )
insert into tb values(11,   'bill'  , '2009-3-6' , 8 )
insert into tb values(12,   'bill'  , '2009-3-7' , 1 )
go

select tid ,
       tname ,
       tdate ,
       thour ,
       newthour = (case when tdate = (select top 1 tdate from tb where tname = t.tname and datepart(week , tdate) = datepart(week,t.tdate) and thour = (select min(thour) from tb where tname = t.tname and datepart(week , tdate) = datepart(week,t.tdate)))
                     then (select thour from tb where tname = t.tname and datepart(week , tdate) = datepart(week , t.tdate) and datepart(weekday , tdate) = 7)
                     when datepart(weekday , tdate) = 7
                     then (select min(thour) from tb where tname = t.tname and datepart(week , tdate) = datepart(week , t.tdate))
                else thour
                end)
from tb t

drop table tb

/*
tid         tname      tdate                                                  thour                newthour            
----------- ---------- ------------------------------------------------------ -------------------- --------------------
1           allen      2009-03-02 00:00:00.000                                3.5                  6.0
2           allen      2009-03-03 00:00:00.000                                3.5                  3.5
3           allen      2009-03-04 00:00:00.000                                7.0                  7.0
4           allen      2009-03-05 00:00:00.000                                8.0                  8.0
5           allen      2009-03-06 00:00:00.000                                4.0                  4.0
6           allen      2009-03-07 00:00:00.000                                6.0                  3.5
7           bill       2009-03-02 00:00:00.000                                5.0                  5.0
8           bill       2009-03-03 00:00:00.000                                6.0                  6.0
9           bill       2009-03-04 00:00:00.000                                3.0                  3.0
10          bill       2009-03-05 00:00:00.000                                8.0                  8.0
11          bill       2009-03-06 00:00:00.000                                8.0                  8.0
12          bill       2009-03-07 00:00:00.000                                1.0                  1.0

（所影响的行数为 12 行）

*/

 
if object_id('tb') is not null drop table tb
go
create table tb(tid int, tname varchar(10), tdate datetime, thour decimal(18,1))
insert into tb values(1 ,   'allen' , '2009-3-2' , 3.5)
insert into tb values(2 ,   'allen' , '2009-3-3' , 5 )
insert into tb values(3 ,   'allen' , '2009-3-4' , 7 )
insert into tb values(4 ,   'allen' , '2009-3-5' , 8 )
insert into tb values(5 ,   'allen' , '2009-3-6' , 4 )
insert into tb values(6 ,   'allen' , '2009-3-7' , 6 )
insert into tb values(7 ,   'bill'  , '2009-3-2' , 5 )
insert into tb values(8 ,   'bill'  , '2009-3-3' , 6 )
insert into tb values(9 ,   'bill'  , '2009-3-4' , 3 )
insert into tb values(10,   'bill'  , '2009-3-5' , 8 )
insert into tb values(11,   'bill'  , '2009-3-6' , 8 )
insert into tb values(12,   'bill'  , '2009-3-7' , 6 )
go

select tid ,
       tname ,
       tdate ,
       thour = case when datename(w,tdate) ='星期六' then (select min(thour) from tb where tname=a.tname)
                    when thour = (select min(thour) from tb where tname=a.tname) then (select thour from tb where tname=a.tname and datename(w,tdate)='星期六')
                    else thour
                    end
from tb a

tid         tname      tdate                   thour
----------- ---------- ----------------------- ---------------------------------------
1           allen      2009-03-02 00:00:00.000 6.0
2           allen      2009-03-03 00:00:00.000 5.0
3           allen      2009-03-04 00:00:00.000 7.0
4           allen      2009-03-05 00:00:00.000 8.0
5           allen      2009-03-06 00:00:00.000 4.0
6           allen      2009-03-07 00:00:00.000 3.5
7           bill       2009-03-02 00:00:00.000 5.0
8           bill       2009-03-03 00:00:00.000 6.0
9           bill       2009-03-04 00:00:00.000 6.0
10          bill       2009-03-05 00:00:00.000 8.0
11          bill       2009-03-06 00:00:00.000 8.0
12          bill       2009-03-07 00:00:00.000 3.0

(12 行受影响)