if object_id('table1') is not null
drop table table1
gocreate table table1(Jobid varchar(10),sHour varchar(10),nSun int,nMon int,nTue int,nWed int,nThu int,nFri int,nStat int)
goinsert into table1
select '001', '8:00', 80, 20, 10, 30, 50, 20, 60 union all
select '001', '9:00', 30, 40, 10, 50, 20, 30, 100 union all
select '001', '10:00', 20, 60, 40, 90, 10, 30, 50 union all
select '002', '8:00', 60, 20, 30, 30, 50, 20, 60 union all
select '002', '9:00', 30, 40, 10, 50, 20, 30, 90 union all
select '002', '10:00', 40, 60, 40, 90, 10, 30, 50
go--select * from table1
declare @BeginDate datetime
declare @EndDate datetime
select @BeginDate = '20090501'
select @EndDate = '20090531'
select jobid ,sHour,nums,dateadd(day,number,@BeginDate) as sDate from
(
select jobid ,sHour,nSUN AS nums,wk = 0 from table1
union all
select jobid ,sHour,nMon AS nums,wk = 1 from table1
union all
select jobid ,sHour,nTue AS nums,wk = 2 from table1
union all
select jobid ,sHour,nWed AS nums,wk = 3 from table1
union all
select jobid ,sHour,nThu AS nums,wk = 4 from table1
union all
select jobid ,sHour,nFri AS nums,wk = 5 from table1
union all
select jobid ,sHour,nStat AS nums,wk = 6 from table1
)a,master..spt_values b where b.type = 'P' and dateadd(day,number,@BeginDate)<=@EndDate and datepart(dw,dateadd(day,number,@BeginDate))=a.wk
order by sdate
解决方案 »
- SQL数据库引擎 删除用户登录名的痕迹
- 急!请各位大虾帮帮我!
- SQL 安装的问题 求高手回答
- 简单问题::::怎么用select语句把'1'变成'0001','20'变成'0020',等等这样的操作???
- 求教怎么将SQL中查询出来的数据以扇形统计图显示
- 一段SQL在Access执行上正常,在SQL Server上不对了?!
- 帮我看看触发器吧~~~~
- 一个数据表的3个字段跟另外一个数据表关联,应该如何写select?
- 如何从数据表的两个数字字段中,取其中数字较小的字段?谢谢!
- 一个sql server 表,定义了主键,在输入时为了避免重复的值,怎么判断,不能用查找,那样每次都要查找,会降底速度的
- SQL 2000 数据库还原
- 请高人指点
/*起日期和结束日期变量*/
declare @BeginDate datetime
declare @EndDate datetime
select @BeginDate = '20090501'
select @EndDate = '20090531'
/*表table1数据:
Jobid sHour nSun nMon nTue nWed nThu nFri nStat
001 8:00 80 20 10 30 50 20 60
001 9:00 30 40 10 50 20 30 100
001 10:00 20 60 40 90 10 30 50
002 8:00 60 20 30 30 50 20 60
002 9:00 30 40 10 50 20 30 90
002 10:00 40 60 40 90 10 30 50
*/if object_id('table1') is not null
drop table table1
gocreate table table1(Jobid varchar(10),sHour varchar(10),nSun int,nMon int,nTue int,nWed int,nThu int,nFri int,nStat int)
goinsert into table1
select '001', '8:00', 80, 20, 10, 30, 50, 20, 60 union all
select '001', '9:00', 30, 40, 10, 50, 20, 30, 100 union all
select '001', '10:00', 20, 60, 40, 90, 10, 30, 50 union all
select '002', '8:00', 60, 20, 30, 30, 50, 20, 60 union all
select '002', '9:00', 30, 40, 10, 50, 20, 30, 90 union all
select '002', '10:00', 40, 60, 40, 90, 10, 30, 50
go--select * from table1
declare @BeginDate datetime
declare @EndDate datetime
select @BeginDate = '20090501'
select @EndDate = '20090531'
select jobid ,sHour,nums,dateadd(day,number,@BeginDate) as sDate from
(
select jobid ,sHour,nSUN AS nums,wk = 1 from table1
union all
select jobid ,sHour,nMon AS nums,wk = 2 from table1
union all
select jobid ,sHour,nTue AS nums,wk = 3 from table1
union all
select jobid ,sHour,nWed AS nums,wk = 4 from table1
union all
select jobid ,sHour,nThu AS nums,wk = 5 from table1
union all
select jobid ,sHour,nFri AS nums,wk = 6 from table1
union all
select jobid ,sHour,nStat AS nums,wk = 7 from table1
)a,master..spt_values b where b.type = 'P' and dateadd(day,number,@BeginDate)<=@EndDate and datepart(dw,dateadd(day,number,@BeginDate))=a.wk
order by sdate
不好意思,数据样例贴出来其实可以看出来。意思是:参照table1表数据转化为5月份每一天,某个时间,table1对应的星期几的数据
问题考虑到各数据库版本兼容问题不想用系统自带的master..spt_values。谢谢您!期待高手给出最好的解决办法!
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Jobid] varchar(3),[sHour] varchar(10),[nSun] int,[nMon] int,[nTue] int,[nWed] int,[nThu] int,[nFri] int,[nStat] int)
insert [tb]
select '001','8:00',80,20,10,30,50,20,60 union all
select '001','9:00',30,40,10,50,20,30,100 union all
select '001','10:00',20,60,40,90,10,30,50 union all
select '002','8:00',60,20,30,30,50,20,60 union all
select '002','9:00',30,40,10,50,20,30,90 union all
select '002','10:00',40,60,40,90,10,30,50
---查询---
SELECT TOP 360 ID=IDENTITY(INT,0,1) INTO #number FROM syscolumns,sysobjects
select b.jobid,a.sdate,b.sHour,b.nums from
(
SELECT DATEADD(day,ID,'2009-5-1') as sdate
FROM #number
WHERE DATEADD(day,ID,'2009-5-1')<='2009-5-31'
) a
left join
(
select Jobid,sHour,1 as wk,[nSun] as nums from [tb]
union all
select Jobid,sHour,2 as wk,[nMon] from [tb]
union all
select Jobid,sHour,3 as wk,[nTue] from [tb]
union all
select Jobid,sHour,4 as wk,[nWed] from [tb]
union all
select Jobid,sHour,5 as wk,[nThu] from [tb]
union all
select Jobid,sHour,6 as wk,[nFri] from [tb]
union all
select Jobid,sHour,7 as wk,[nStat] from [tb]
) b
on
b.wk=datepart(w,a.sdate)
order by
jobid,sdate
---结果---
jobid sdate sHour nums
----- ------------------------------------------------------ ---------- -----------
001 2009-05-01 00:00:00.000 8:00 20
001 2009-05-01 00:00:00.000 9:00 30
001 2009-05-01 00:00:00.000 10:00 30
001 2009-05-02 00:00:00.000 8:00 60
001 2009-05-02 00:00:00.000 9:00 100
001 2009-05-02 00:00:00.000 10:00 50
001 2009-05-03 00:00:00.000 8:00 80
001 2009-05-03 00:00:00.000 9:00 30
001 2009-05-03 00:00:00.000 10:00 20
………………
………………
002 2009-05-30 00:00:00.000 8:00 60
002 2009-05-30 00:00:00.000 9:00 90
002 2009-05-30 00:00:00.000 10:00 50
002 2009-05-31 00:00:00.000 8:00 60
002 2009-05-31 00:00:00.000 9:00 30
002 2009-05-31 00:00:00.000 10:00 40(所影响的行数为 186 行)[/code]