22,209
社区成员
发帖
与我相关
我的任务
分享相同列的两个数值对调
有一个表有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小时；
需要计算一个月的数据
例如：
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小时；
需要计算一个月的数据
...全文
请发表友善的回复…
发表回复
xubxuq 2012-11-01
- 打赏
- 举报
处处有高人啊
jimk5200 2011-05-30
- 打赏
- 举报
水平太有限,还看不明白
longai123 2011-05-24
- 打赏
- 举报
学习了datepart函数，努力进步
yubofighting 2011-05-06
- 打赏
- 举报
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
yubofighting 2011-05-06
- 打赏
- 举报
[Quote=引用 17 楼 htl258 的回复:]
引用 14 楼 swwei2002 的回复:
引用 6 楼 dawugui 的回复:
楼上的,你好象没考虑数据是否是同一周的.
如果一周是跨年度的,周一和周六不在同一年的话......
SQL code
if object_id('tb') is not null drop table tb
go
create table tb(tid int, tname varc……
[/Quote]
最小值不一定在周一
引用 14 楼 swwei2002 的回复:
引用 6 楼 dawugui 的回复:
楼上的,你好象没考虑数据是否是同一周的.
如果一周是跨年度的,周一和周六不在同一年的话......
SQL code
if object_id('tb') is not null drop table tb
go
create table tb(tid int, tname varc……
[/Quote]
最小值不一定在周一
Linares 2011-01-29
- 打赏
- 举报
话说这工资结算方式真黑心 巨寒
Linares 2011-01-29
- 打赏
- 举报
亮瞎我的狗眼
错的答案给100分
错的答案给100分
gsk09 2009-09-24
- 打赏
- 举报
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 行)
gsk09 2009-09-24
- 打赏
- 举报
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 行)
scorpions_z 2009-08-07
- 打赏
- 举报
到现在还有人给我写，谢谢了！
sue_1018 2009-08-07
- 打赏
- 举报
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 tsue_1018 2009-08-07
- 打赏
- 举报
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
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
weizhenya 2009-03-20
- 打赏
- 举报
顶一个
LichKing 2009-03-05
- 打赏
- 举报
study
jn0005 2009-03-05
- 打赏
- 举报
关注一下
dianzizhizhuo 2009-03-05
- 打赏
- 举报
再次感谢~！
dianzizhizhuo 2009-03-05
- 打赏
- 举报
太复杂了~！
sdwqs 2009-03-05
- 打赏
- 举报
学习，。。。
长风dad 2009-03-04
- 打赏
- 举报
顶！
冰岛男孩 2009-03-04
- 打赏
- 举报
路过，学习……
加载更多回复（29）
