我真的没分了,最后100分了,再次开贴求SQL语句!
表:t1
services 港口 到港 离港 SI截关 CY截关 TT(所需天数)
c1 香港 周日 周日 周五 周六
c1 他科马 周五 周六 12
c1 奥克兰 周一 周二 15
c1 檀香山 周六 周日 20
c1 关岛 周二 周三 30
表:t2
Services 年份 第几周
c1 2007 34
c1 2008 34
要得到这样的结果:
香港 到港时间
services 年份 第几周 到港 离港 SI截关 CY截关 他科马 奥克兰 檀香山 关岛
c1 2007 34 8-19 8-19 8-24 8-25 8-31 9-3 9-8 9-13
c1 2008 34 ? ?..............................................
..
..
也就是说:在t1中已经有了周几和TT(例如:从香港到他科马需要12天),
在t2中有对应的services,然后通过年份和给出的第几周,来得到那样的结果.
例如:上面的,从香港到他科马需要12天,那么我要得到:2007年,第34周,
到他科马是几月几日?
[解决办法]
c1 香港 周日 周日 周五 周六
这条纪录似乎跟你下面的纪录的意义不一样啊?
而且 SI截关 CY截关 这两个字段如何而来...
[解决办法]
--创建表
create table t1(
services varchar(20) not null,
港口 varchar(20),
到港 varchar(20),
离港 varchar(20),
SI截关 varchar(20),
CY截关 varchar(20),
TT int
)
go
create table t2(
Services varchar(20) not null,
年份 int,
第几周 int
)
go
--插入数据
insert into t1 select 'c1 ', '香港 ', '周日 ', '周日 ', '周五 ', '周六 ',0
union select 'c1 ', '他科马 ', '周五 ', '周六 ', ' ', ' ',12
union select 'c1 ', '奥克兰 ', '周一 ', '周二 ', ' ', ' ',15
union select 'c1 ', '檀香山 ', '周六 ', '周日 ', ' ', ' ',20
union select 'c1 ', '关岛 ', '周二 ', '周三 ', ' ', ' ',30
go
insert into t2 select 'c1 ',2007,34
union select 'c1 ',2008,34
go
--查询
create procedure ge
@Services varchar(20),
@y int
as
begin
----设置星期一作为每周的第一天
set datefirst 1--
declare @sql nvarchar(1000)--查询语句
declare @s nvarchar(1000)--
declare @w int --第几周
declare @d datetime
select @w=第几周 from t2 where Services=@Services and 年份=@y
set @s=Convert(varchar(4),@y)+ '-01-01 '
set @d=DATEADD(DAY, (@w-1) * 7, Convert(Datetime,@s))
set @s=substring(convert(char(10),@d),7,4)+ '- ' +substring(convert(char(10),@d),1,2) + '- '+replace(substring(convert(char(10),@d),4,2), ' ', '0 ')
set @sql=N 'select services, '
set @sql=@sql+ Convert(varchar(4),@y)+ ' 年份, '+Convert(varchar(4),@w)+ ' 第几周 '
select @sql=@sql + ',max(case 港口 when ' ' '+港口 + ' ' 'then DATEADD(DAY, TT,Convert(datetime, ' ' '+@s+ ' ' ')) end )[ ' +港口 + '] '
from (select TOP 100 Percent 港口 from t1 Group By 港口,TT order by TT asc) as a
select @sql=@sql + ' from t1 group by services '
exec (@sql)
end
go
---运行
exec ge 'c1 ',2007
--删除
drop table t1
drop table t2
drop procedure ge
[解决办法]
1.首先要根据年份和第几周来获取具体的日期:
SELECT DATEADD(ww,34,DATEADD(yy,DATEDIFF(yy,0,getdate()),0))
当然你也可以把DATEADD(yy,DATEDIFF(yy,0,getdate()),0)通过其他的方法替换成 '2007-01-01 '或 '2008-01-01 '
2.利用生成交叉表的方法生成楼主所需的结果
问题:
对于楼主的意思不是很明白
表t2与t1关联的列是哪些
[解决办法]
--创建表
create table t1(
services varchar(20) not null,
港口 varchar(20),
到港 varchar(20),
离港 varchar(20),
SI截关 varchar(20),
CY截关 varchar(20),
TT int
)
go
create table t2(
Services varchar(20) not null,
年份 int,
第几周 int
)
go
--插入数据
insert into t1 select 'c1 ', '香港 ', '周日 ', '周日 ', '周五 ', '周六 ',0
union select 'c1 ', '他科马 ', '周五 ', '周六 ', ' ', ' ',12
union select 'c1 ', '奥克兰 ', '周一 ', '周二 ', ' ', ' ',15
union select 'c1 ', '檀香山 ', '周六 ', '周日 ', ' ', ' ',20
union select 'c1 ', '关岛 ', '周二 ', '周三 ', ' ', ' ',30
go
insert into t2 select 'c1 ',2007,34
union select 'c1 ',2008,34
go
--查询
create function getD(@d datetime)
returns varchar(20)
as
begin
return substring(convert(char(10),@d),7,4)+ '- ' +substring(convert(char(10),@d),1,2) + '- '+replace(substring(convert(char(10),@d),4,2), ' ', '0 ')
end
go
create function ggg(@s varchar(20))
returns int
as
begin
if @s= '周一 '
return 0
if @s= '周二 '
return 1
if @s= '周三 '
return 2
if @s= '周四 '
return 3
if @s= '周五 '
return 4
if @s= '周六 '
return 5
if @s= '周日 '
return 6
return -1
end
go
create procedure ge
@Services varchar(20),
@y int
as
begin
----设置星期一作为每周的第一天
set datefirst 1--
declare @sql nvarchar(1000)--查询语句
declare @s nvarchar(1000)--
declare @w int --第几周
declare @i int --周几
declare @d datetime
declare @dd datetime--出发当天
select @w=第几周 from t2 where Services=@Services and 年份=@y
set @s=Convert(varchar(4),@y)+ '-01-01 '
set @d=DATEADD(DAY, (@w-1) * 7, Convert(Datetime,@s))
declare @si varchar(20)
declare @sl varchar(20)
declare @cy varchar(20)
select @s=到港,@sl=离港,@si=SI截关,@cy=CY截关 from t1 where Services=@Services and TT=0
set @i=dbo.ggg(@s)
set @dd=DateAdd(DAY,@i,@d)
set @s=dbo.getD(@dd)
set @sql=N 'select services, '
set @sql=@sql+ Convert(varchar(4),@y)+ ' 年份, '+Convert(varchar(4),@w)+ ' 第几周, '
set @sl=dbo.getD(DateAdd(DAY,dbo.ggg(@sl),@d))
set @sql=@sql+ ' ' ' '+ @s+ ' ' ' 到港, '
set @sql=@sql+ ' ' ' '+ @Sl+ ' ' ' 离港, '
if dbo.ggg(@SI) <dbo.ggg(@CY)
begin
set @i=7+dbo.ggg(@SI)
set @sql=@sql+ ' ' ' '+ dbo.getD(DateAdd(DAY,@i,@dd))+ ' ' ' SI截关, '
set @i=7+dbo.ggg(@CY)
set @sql=@sql+ ' ' ' '+ dbo.getD(DateAdd(DAY,@i,@dd))+ ' ' ' CY截关 '
end
else
begin
set @i=dbo.ggg(@SI)
set @sql=@sql+ ' ' ' '+ dbo.getD(DateAdd(DAY,@i,@dd))+ ' ' ' SI截关, '
set @i=dbo.ggg(@CY)
set @sql=@sql+ ' ' ' '+ dbo.getD(DateAdd(DAY,@i,@dd))+ ' ' ' CY截关 '
end
select TOP 100 Percent 港口 into #t from t1 Group By 港口,TT HAVING TT> 0 order by TT asc
select @sql=@sql + ',max(case 港口 when ' ' '+港口 + ' ' 'then dbo.getD(DATEADD(DAY, TT,Convert(datetime, ' ' '+@s+ ' ' '))) end )[ ' +港口 + '] '
from #t
select @sql=@sql + ' from t1 group by services '
exec (@sql)
drop table #t
end
go
---运行
exec ge 'c1 ',2008
--删除
drop table t1
drop table t2
drop procedure ge
drop function ggg
drop function getD
