雷同列的两个数值对调

2012-12-16

相同列的两个数值对调有一个表有id,姓名,日期，和上班时间。上班时间由星期一到星期六，星期六算加班，星期一

相同列的两个数值对调
有一个表有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小时；
需要计算一个月的数据
[最优解释]

引用:

引用:
楼上的,你好象没考虑数据是否是同一周的.

如果一周是跨年度的,周一和周六不在同一年的话......

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 ,
       newthour = case when datepart(weekday,tdate+@@datefirst-1) = 6
                           then (select min(thour) from tb where tname=a.tname and datepart(wk,tdate+@@datefirst-1) = datepart(wk,a.tdate+@@datefirst-1)  and year(tdate)=year(a.tdate))
                       when  thour = (select min(thour) from tb where tname=a.tname  and datepart(wk,tdate+@@datefirst-1) = datepart(wk,a.tdate+@@datefirst-1) and year(tdate)=year(a.tdate)) and tdate= (select min(tdate) from tb where tname=a.tname and datepart(wk,tdate+@@datefirst-1) = datepart(wk,a.tdate+@@datefirst-1) and year(tdate)=year(a.tdate) )
                           then (select thour from tb where tname=a.tname and datepart(weekday,tdate+@@datefirst-1) = 6  and datepart(wk,tdate+@@datefirst-1) = datepart(wk,a.tdate+@@datefirst-1) and year(tdate)=year(a.tdate) )
                       else thour
                       end
from tb a

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
13          allen      2009-03-09 00:00:00.000                                3.5                  6.0
14          allen      2009-03-10 00:00:00.000                                3.5                  3.5
15          allen      2009-03-11 00:00:00.000                                7.0                  7.0
16          allen      2009-03-12 00:00:00.000                                8.0                  8.0
17          allen      2009-03-13 00:00:00.000                                4.0                  4.0
18          allen      2009-03-14 00:00:00.000                                6.0                  3.5

19          allen      2008-12-01 00:00:00.000                                3.5                  6.0
20          allen      2008-12-02 00:00:00.000                                3.5                  3.5
21          allen      2008-12-03 00:00:00.000                                7.0                  7.0
22          allen      2008-12-04 00:00:00.000                                8.0                  8.0
23          allen      2008-12-05 00:00:00.000                                4.0                  4.0
24          allen      2008-12-06 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 6.0 3.0

（所影响的行数为 24 行）

[其他解释]
关注一下
[其他解释]

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 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 行）
*/

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 * ,
       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 行）
*/

[其他解释]

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 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 行）
*/

[其他解释]
--不过楼主是不是漏考虑了一个问题,如果有两天,其最小值一样,是都替换为周六,还是只替换一个,还如果,周六的值比周一到周五的最小值还小,是否还替换?

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

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' , 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 行）

*/

[其他解释]

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 行受影响)

[其他解释]
楼上的,你好象没考虑数据是否是同一周的.
[其他解释]

-->楼上的方式受系统环境的影响,这种方式将不受系统环境的影响
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 datepart(weekday,tdate+@@datefirst-1) = 6 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 datepart(weekday,tdate+@@datefirst-1) = 6)
                    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 行受影响)

[其他解释]

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(13 ,   'allen' , '2009-3-9' , 3.5)
insert into tb values(14 ,   'allen' , '2009-3-10', 5 )
insert into tb values(15 ,   'allen' , '2009-3-11' , 7 )
insert into tb values(16 ,   'allen' , '2009-3-12' , 8 )
insert into tb values(17,   'allen' , '2009-3-13' , 4 )
insert into tb values(18,   'allen' , '2009-3-14' , 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 datepart(weekday,tdate+@@datefirst-1) = 6 then (select min(thour) from tb where tname=a.tname and datepart(wk,tdate+@@datefirst-1) = datepart(wk,a.tdate+@@datefirst-1) )
                    when thour = (select min(thour) from tb where tname=a.tname  and datepart(wk,tdate+@@datefirst-1) = datepart(wk,a.tdate+@@datefirst-1) ) then (select thour from tb where tname=a.tname and datepart(weekday,tdate+@@datefirst-1) = 6  and datepart(wk,tdate+@@datefirst-1) = datepart(wk,a.tdate+@@datefirst-1) )
                    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
13          allen      2009-03-09 00:00:00.000 6.0
14          allen      2009-03-10 00:00:00.000 5.0
15          allen      2009-03-11 00:00:00.000 7.0
16          allen      2009-03-12 00:00:00.000 8.0
17          allen      2009-03-13 00:00:00.000 4.0
18          allen      2009-03-14 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

(18 行受影响)

-->受dawugui指点,在此修正,向dawugui表示感谢!

[其他解释]

-->在睡觉的时候想着这个问题,觉得有点不对劲,特爬上来继续做了修正,这下应该没问题了:

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(13 ,   'allen' , '2009-3-9' , 3.5)
insert into tb values(14 ,   'allen' , '2009-3-10', 3.5 )
insert into tb values(15 ,   'allen' , '2009-3-11' , 7 )
insert into tb values(16 ,   'allen' , '2009-3-12' , 8 )
insert into tb values(17,   'allen' , '2009-3-13' , 4 )
insert into tb values(18,   'allen' , '2009-3-14' , 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 ,
       newthour = case when datepart(weekday,tdate+@@datefirst-1) = 6 then (select min(thour) from tb where tname=a.tname and datepart(wk,tdate+@@datefirst-1) = datepart(wk,a.tdate+@@datefirst-1) )
                    when  thour = (select min(thour) from tb where tname=a.tname  and datepart(wk,tdate+@@datefirst-1) = datepart(wk,a.tdate+@@datefirst-1) ) and tdate= (select min(tdate) from tb where tname=a.tname and datepart(wk,tdate+@@datefirst-1) = datepart(wk,a.tdate+@@datefirst-1) ) then (select thour from tb where tname=a.tname and datepart(weekday,tdate+@@datefirst-1) = 6  and datepart(wk,tdate+@@datefirst-1) = datepart(wk,a.tdate+@@datefirst-1) )
                    else thour
                    end
from tb a

----------------------
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
13          allen      2009-03-09 00:00:00.000 3.5                                     6.0
14          allen      2009-03-10 00:00:00.000 3.5                                     3.5
15          allen      2009-03-11 00:00:00.000 7.0                                     7.0
16          allen      2009-03-12 00:00:00.000 8.0                                     8.0
17          allen      2009-03-13 00:00:00.000 4.0                                     4.0
18          allen      2009-03-14 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 6.0                                     3.0

(18 行受影响)

[其他解释]
楼上考虑的很全了....
[其他解释]
汗，都这么强悍。关注
[其他解释]
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(13 ,   'allen' , '2009-3-9' , 3.5)
insert into tb values(14 ,   'allen' , '2009-3-10', 3.5 )
insert into tb values(15 ,   'allen' , '2009-3-11' , 7 )
insert into tb values(16 ,   'allen' , '2009-3-12' , 8 )
insert into tb values(17,   'allen' , '2009-3-13' , 4 )

insert into tb values(18,   'allen' , '2009-3-14' , 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 ,
       newthour = case when datepart(weekday,tdate+@@datefirst-1) = 6 then (select min(thour) from tb where tname=a.tname and datepart(wk,tdate+@@datefirst-1) = datepart(wk,a.tdate+@@datefirst-1) )
                    when  thour = (select min(thour) from tb where tname=a.tname  and datepart(wk,tdate+@@datefirst-1) = datepart(wk,a.tdate+@@datefirst-1) ) and tdate= (select min(tdate) from tb where tname=a.tname and datepart(wk,tdate+@@datefirst-1) = datepart(wk,a.tdate+@@datefirst-1) ) then (select thour from tb where tname=a.tname and datepart(weekday,tdate+@@datefirst-1) = 6  and datepart(wk,tdate+@@datefirst-1) = datepart(wk,a.tdate+@@datefirst-1) )
                    else thour
                    end
from tb a

----------------------
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
13          allen      2009-03-09 00:00:00.000 3.5                                     6.0
14          allen      2009-03-10 00:00:00.000 3.5                                     3.5
15          allen      2009-03-11 00:00:00.000 7.0                                     7.0
16          allen      2009-03-12 00:00:00.000 8.0                                     8.0
17          allen      2009-03-13 00:00:00.000 4.0                                     4.0
18          allen      2009-03-14 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 6.0                                     3.0

(18 行受影响)
[其他解释]
学习!
[其他解释]

引用:

楼上的,你好象没考虑数据是否是同一周的.

如果一周是跨年度的,周一和周六不在同一年的话......
[其他解释]

引用:

引用:
楼上的,你好象没考虑数据是否是同一周的.

如果一周是跨年度的,周一和周六不在同一年的话......

他只算月的,要不然就更麻烦了.
[其他解释]
学习..
[其他解释]
帮顶。
[其他解释]
.
[其他解释]
该回复于2010-01-05 16:41:40被版主删除
[其他解释]
学习
[其他解释]
小孩游戏
[其他解释]
学习一下，顶了！
[其他解释]
厉害学习了顶
[其他解释]
mark
[其他解释]
顶
[其他解释]
感谢各位的帮忙！！
[其他解释]
大家水平都太高了
我自叹不如啊！
[其他解释]
路过，学习……
[其他解释]
大家水平都太高了

[其他解释]
在浏览网页的时候总是觉得，广告很扰乱我的视线，很烦人！希望网站建设的要好些
[其他解释]
顶！
------其他解决方案--------------------

关注一下
[其他解释]
学习，。。。
[其他解释]
该回复于2009-03-05 09:23:30被版主删除
[其他解释]
study
[其他解释]
顶一个
[其他解释]
SELECT tname,tdate,
thour=CASE
WHEN datepart(w,tdate+@@DATEFIRST-1)=6
THEN (SELECT thour FROM tb WHERE t.tname=tname AND datepart(w,tdate+@@DATEFIRST-1)=1)
WHEN datepart(w,tdate+@@DATEFIRST-1)=1
THEN (SELECT thour FROM tb WHERE t.tname=tname AND datepart(w,tdate+@@DATEFIRST-1)=6)
ELSE thour
END
FROM tb t

[其他解释]
太复杂了~！
[其他解释]
再次感谢~！
[其他解释]
SQLSERVER2005


declare @t table ([tid] int,[tname] varchar(5),[tdate] datetime,[thour] numeric(2,1))
insert into @t
select 1,'allen','2009-3-2',1.5 union all
select 2,'allen','2009-3-3',2 union all
select 3,'allen','2009-3-4',7 union all
select 4,'allen','2009-3-5',8 union all
select 5,'allen','2009-3-6',4 union all
select 6,'allen','2009-3-7',6 union all
select 7,'bill','2009-3-2',5 union all
select 8,'bill','2009-3-3',3 union all
select 9,'bill','2009-3-4',3 union all
select 10,'bill','2009-3-5',8 union all
select 11,'bill','2009-3-6',8 union all
select 12,'bill','2009-3-7',9

declare @sunday datetime
set @sunday = '2009-3-1'
select tid,tname,tdate
,(case 
when (datepart(weekday,tdate)=7)
then min(thour)over(partition by tname,(datediff(day,@sunday,tdate)/8))
when ((thour=min(thour)over(partition by tname,(datediff(day,@sunday,tdate)/78)))
and (row_number()over(partition by tname,(datediff(day,@sunday,tdate)/8),thour order by tdate))=1)
then (sum(case when datepart(weekday,tdate)=7 then thour else 0 end)
over(partition by tname,(datediff(day,@sunday,tdate)/8)))
else thour
end ) as thour
from @t
order by tid

tid         tname tdate                   thour
----------- ----- ----------------------- ---------------------------------------
1           allen 2009-03-02 00:00:00.000 6.0
2           allen 2009-03-03 00:00:00.000 2.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 1.5
7           bill  2009-03-02 00:00:00.000 5.0
8           bill  2009-03-03 00:00:00.000 9.0
9           bill  2009-03-04 00:00:00.000 3.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 行)

[其他解释]
SQLSERVER2005


declare @t table ([tid] int,[tname] varchar(10),[tdate] datetime,[thour] numeric(2,1))
insert into @t
select 1,'allen','2009-3-2',3.5 union all
select 2,'allen','2009-3-3',2 union all
select 3,'allen','2009-3-4',7 union all
select 4,'allen','2009-3-5',8 union all
select 5,'allen','2009-3-6',4 union all
select 6,'allen','2009-3-7',6 union all
select 7,'bill','2009-3-2',5 union all
select 8,'bill','2009-3-3',3 union all
select 9,'bill','2009-3-4',3 union all
select 10,'bill','2009-3-5',0.5 union all
select 11,'bill','2009-3-6',1 union all
select 12,'bill','2009-3-7',9

declare @sunday datetime
,@datefirst smallint
,@weekDays smallint
set @sunday = '1995-1-1'
set @weekDays = 7
set @datefirst = @@datefirst -1

select tid,tname,tdate
,(case 
when (datepart(weekday,tdate+@datefirst)=6)
then min(thour)over(partition by tname,(datediff(day,@sunday,tdate)/@weekDays))
when ((thour=min(thour)over(partition by tname,(datediff(day,@sunday,tdate)/@weekDays)))
and (row_number()over(partition by tname,(datediff(day,@sunday,tdate)/@weekDays),thour order by tdate))=1)
then (sum(case when datepart(weekday,tdate+@datefirst)=6 then thour else 0 end)
over(partition by tname,(datediff(day,@sunday,tdate)/@weekDays)))
else thour
end ) as thour
from @t
order by tid

tid         tname      tdate                   thour
----------- ---------- ----------------------- ---------------------------------------
1           allen      2009-03-02 00:00:00.000 3.5
2           allen      2009-03-03 00:00:00.000 6.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 2.0
7           bill       2009-03-02 00:00:00.000 5.0
8           bill       2009-03-03 00:00:00.000 3.0
9           bill       2009-03-04 00:00:00.000 3.0
10          bill       2009-03-05 00:00:00.000 9.0
11          bill       2009-03-06 00:00:00.000 1.0
12          bill       2009-03-07 00:00:00.000 0.5

(12 行)

[其他解释]

SELECT tname,tdate,
thour=CASE
WHEN datepart(w,tdate+@@DATEFIRST-1)=6 
THEN (SELECT thour FROM tb WHERE t.tname=tname AND datepart(w,tdate+@@DATEFIRST-1)=1)
WHEN datepart(w,tdate+@@DATEFIRST-1)=1 
THEN (SELECT thour FROM tb WHERE t.tname=tname AND datepart(w,tdate+@@DATEFIRST-1)=6)
ELSE thour
  END
FROM tb t

[其他解释]
到现在还有人给我写，谢谢了！
[其他解释]

引用:

引用:
引用:
楼上的,你好象没考虑数据是否是同一周的.

如果一周是跨年度的,周一和周六不在同一年的话......

SQL code
if object_id('tb') is not null drop table tb
go
create table tb(tid int, tname varc……

最小值不一定在周一
[其他解释]
100分的答案应该该成这样：


select tid , 
       tname , 
       tdate ,
       thour ,
       newthour = case when datepart(weekday,tdate+@@datefirst-1) = 6 
                           then (select min(thour) from tb where tname=a.tname and datepart(wk,tdate+@@datefirst-1) = datepart(wk,a.tdate+@@datefirst-1)  and year(tdate)=year(a.tdate))    
                       when  thour = (select min(thour) from tb where tname=a.tname  and datepart(wk,tdate+@@datefirst-1) = datepart(wk,a.tdate+@@datefirst-1) and year(tdate)=year(a.tdate)) and tdate= (select min(tdate) from tb where tname=a.tname and datepart(wk,tdate+@@datefirst-1) = datepart(wk,a.tdate+@@datefirst-1) and year(tdate)=year(a.tdate) and thour=((select min(thour) from tb where tname=a.tname  and datepart(wk,tdate+@@datefirst-1) = datepart(wk,a.tdate+@@datefirst-1) and year(tdate)=year(a.tdate)) ) ) 
 
                           then (select thour from tb where tname=a.tname and datepart(weekday,tdate+@@datefirst-1) = 6  and datepart(wk,tdate+@@datefirst-1) = datepart(wk,a.tdate+@@datefirst-1) and year(tdate)=year(a.tdate) )
                       else thour
                       end
from tb a

[其他解释]
亮瞎我的狗眼
错的答案给100分
[其他解释]
话说这工资结算方式真黑心巨寒
[其他解释]
处处有高人啊
[其他解释]
水平太有限,还看不明白
[其他解释]
学习了datepart函数，努力进步

热点排行

SQL Server

雷同列的两个数值对调