相同列的两个数值对调

相同列的两个数值对调

有一个表有id,姓名,日期，和上班时间。上班时间由星期一到星期六，星期六算加班，星期一到星期五的上班时间去最小的值跟星期六上班时间对调！

例如：

tid tname tdate thour

1 allen 3.5

2 allen 5

3 allen 7

4 allen 8

5 allen 4

6 allen 6

7 bill 5

8 bill 6

9 bill 3

10 bill 8

11 bill 8

12 bill 6

根据上表，名字为allen的要把星期一（）和星期六（）对调，星期一的上班时间为6小时，星期六的上班时间为3.5小时；

名字为bill的要把星期三（）和星期六（）对调，星期一的上班时间为6小时，星期六的上班时间为3.5小时；

需要计算一个月的数据

SQL codecreate table tb(tid int, tname varchar(10), tdate datetime, thour decimal(18,1))

insert into tb values(1 , "allen" , "" , 3.5)

insert into tb values(2 , "allen" , "" , 5 )

insert into tb values(3 , "allen" , "" , 7 )

insert into tb values(4 , "allen" , "" , 8 )

insert into tb values(5 , "allen" , "" , 4 )

insert into tb values(6 , "allen" , "" , 6 )

insert into tb values(7 , "bill" , "" , 5 )

insert into tb values(8 , "bill" , "" , 6 )

insert into tb values(9 , "bill" , "" , 3 )

insert into tb values(10, "bill" , "" , 8 )

insert into tb values(11, "bill" , "" , 8 )

insert into tb values(12, "bill" , "" , 6 )

go

select tid ,

tname ,

tdate ,

thour = (case when thour = (select min(thour) from tb where tname = ame and datepart(week , tdate) = datepart(week,ate))

then (select top 1 thour from tb where tname = ame and datepart(week , tdate) = datepart(week , ate) order by tdate desc)

when datepart(weekday , tdate) = 7

then (select min(thour) from tb where tname = ame and datepart(week , tdate) = datepart(week , ate))

else thour

end)

from tb t

drop table tb

/*

tid tname tdate thour

----------- ---------- ------------------------------------------------------ --------------------

1 allen 00:00:00.000 6.0

2 allen 00:00:00.000 5.0

3 allen 00:00:00.000 7.0

4 allen 00:00:00.000 8.0

5 allen 00:00:00.000 4.0

6 allen 00:00:00.000 3.5

7 bill 00:00:00.000 5.0

8 bill 00:00:00.000 6.0

9 bill 00:00:00.000 6.0

10 bill 00:00:00.000 8.0

11 bill 00:00:00.000 8.0

12 bill 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" , "" , 3.5)

insert into tb values(2 , "allen" , "" , 5 )

insert into tb values(3 , "allen" , "" , 7 )

insert into tb values(4 , "allen" , "" , 8 )

insert into tb values(5 , "allen" , "" , 4 )

insert into tb values(6 , "allen" , "" , 6 )

insert into tb values(7 , "bill" , "" , 5 )

insert into tb values(8 , "bill" , "" , 6 )

insert into tb values(9 , "bill" , "" , 3 )

insert into tb values(10, "bill" , "" 要推广新, 8 )

insert into tb values(11, "bill" , "" , 8 )

insert into tb values(12, "bill" , "" , 6 )

go

select * ,

newthour = (case when thour = (select min(thour) from tb where tname = ame and datepart(week , tdate) = datepart(week,ate))

then (select top 1 thour from tb where tname = ame and datepart(week , tdate) = datepart(week , ate) order by tdate desc)

when datepart(weekday , tdate) = 7

then (select min(thour) from tb where tname = ame and datepart(week , tdate) = datepart(week , ate))

else thour

end)

from tb t

drop table tb

/*

tid tname tdate thour newthour

----------- ---------- ------------------------------------------------------ -------------------- --------------------

1 allen 00:00:00.000 3.5 6.0

2 allen 00:00:00.000 5.0 5.0

3 allen 00:00:00.000 7.0 7.0

4 allen 00:00:00.000 8.0 8.0

5 allen 00:00:00.000 4.0 4.0

6 allen 00:00:00.000 6.0 3.5

7 bill 00:00:00.000 5.0 5.0

8 bill 00:00:00.000 6.0 6.0

9 bill 00:00:00.000 3.0 6.0

10 bill 00:00:00.000 8.0 8.0

11 bill 00:00:00.000 8.0 8.0

12 bill 00:00:00.000 6.0 3.0

（所影响的行数为 12 行）

*/

varchar(10), tdate datetime, thour decimal(18,1))

insert into tb values(1 , "allen" , "" , 3.5)

insert into tb values(2 , "allen" , "" , 5 )

insert into tb values(3 , "allen" , "" , 7 )

insert into tb values(4 , "allen" , "" , 8 )

insert into tb values(5 , "allen" , "" , 4 )

insert into tb values(6 , "allen" , "" , 6 )

insert into tb values(7 , "bill" , "" , 5 )

insert into tb values(8 , "bill" , "" , 6 )

insert into tb values(9 , "bill" , "" , 3 )

insert into tb values(10, "bill" , "" , 8 )

insert into tb values(11, "bill" , "" , 8 )

insert into tb values(12, "bill" , "" , 6 )

go

select tid ,

tname ,

tdate ,

thour = (case when thour = (select min(thour) from tb where tname = ame and datepart(week , tdate) = datepart(week,ate))

then (select thour from tb where tname = ame and datepart(week , tdate) = datepart(week , ate) and datepart(weekday , tdate) = 7)

when datepart(weekday , tdate) = 7

then (select min(thour) from tb where tname = ame and datepart(week , tdate) = datepart(week , ate))

else thour

end)

from tb t

drop table tb

/*

tid tname tdate thour

----------- ---------- ------------------------------------------------------ --------------------

1 allen 00:00:00.000 6.0

2 allen 00:00:00.000 5.0

3 allen 00:00:00.000 7.0

4 allen 00:00:00.000 8.0

5 allen 00:00:00.000 4.0

6 allen 00:00:00.000 3.5

7 bill 00:00:00.000 5.0

8 bill 00:00:00.000 6.0

9 bill 00:00:00.000 6.0

10 bill 00:00:00.000 8.0

11 bill 00:00:00.000 8.0

12 bill 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" , "" , 3.5)

insert into tb values(2 , "allen" , "" , 3.5 )

insert into tb values(3 , "allen" , "" , 7 )

insert into tb values(4 , "allen" , "" , 8 )

insert into tb values(5 , "allen" , "" , 4 )

insert into tb values(6 , "allen" , "" , 6 )

insert into tb values(7 , "bill" , "" , 5 )

insert into tb values(8 , "bill" , "" , 6 )

insert into tb values(9 , "bill" , "" , 3 )

insert into tb values(10, "bill" , "" , 8 )

insert into tb values(11, "bill" , "" , 8 )

insert into tb values(12, "bill" , "" , 1 )

go

select tid ,

tname ,

tdate ,

thour ,

newthour = (case when tdate = (select top 1 tdate from tb where tname = ame and datepart(week , tdate) = datepart(week,ate) and thour = (select min(thour) from tb where tname = ame and datepart(week , tdate) = datepart(week,ate)))

then (select thour from tb where tname = ame and datepart(week , tdate) = datepart(week , ate) and datepart(weekday , tdate) = 7)

when datepart(weekday , tdate) = 7

then (select min(thour) from tb where tname = ame and datepart(week , tdate) = datepart(week , ate))

else thour

end)

from tb t

drop table tb

/*

tid tname tdate thour newthour

----------- ---------- ------------------------------------------------------ -------------------- --------------------

1 allen 00:00:00.000 3.5 6.0

2 allen 00:00:00.000 3.5 3.5

3 allen 00:00:00.000 7.0 7.0

4 allen 00:00:00.000 8.0 8.0

5 allen 00:00:00.000 4.0 4.0

6 allen 00:00:00.000 6.0 3.5

7 bill 00:00:00.000 5.0 5.0

8 bill 00:00:00.000 6.0 6.0

9 bill 00:00:00.000 3.0 3.0

10 bill 00:00:00.000 8.0 8.0

11 bill 00:00:00.000 8.0 8.0

12 bill 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" , "" , 3.5)

insert into tb values(2 , "allen" , "" , 5 )

insert into tb values(3 , "allen" , "" , 7 )

insert into tb values(4 , "allen" , "" , 8 )

insert into tb values(5 , "allen" , "" , 4 )

insert into tb values(6 , "allen" , "" , 6 )

insert into tb values(7 , "bill" , "" , 5 )

insert into tb values(8 , "bill" , "" , 6 )

insert into tb values(9 , "bill" , "" , 3 )

insert into tb values(10, "bill" , "" , 8 )

insert into tb values(11, "bill" , "" , 8 )

insert into tb values(12, "bill" , "" , 6 )

go

select tid ,

tname ,

tdate ,

thour = case when datename(w,tdate) ="星期六" then (select min(thour) from tb where tname=ame)

when thour = (select min(thour) from tb where tname=ame) then (select thour from tb where tname=ame and datename(w,tdate)="星期六")

else thour

end

from tb a

tid tname tdate thour

----------- ---------- ----------------------- ---------------------------------------

1 allen 00:00:00.000 6.0

2 allen 00:00:00.000 5.0

3 allen 00:00:00.000 7.0

4 allen 00:00:00注意防范。.000 8.0

5 allen 00:00:00.000 4.0

6 allen 00:00:00.000 3.5

7 bill 00:00:00.000 5.0

8 bill 00:00:00.000 6.0

9 bill 00:00:00.000 6.0

10 bill 00:00:00.000 8.0

11 bill 00:00:00.000 8.0

12 bill 00:00:00.000 3.0

(12 行受影响)

九江白癜风治疗费用
三亚什么医院治疗白癜风
冠心病怎么治疗最好