参考一下
可以qq:28118085
CREATE proc dbo.PL_Jidu2
@S1 varchar(555)='',
@S0 varchar(555)='',
@D0 varchar(555)='',
@P0 varchar(555)='',
@T0 varchar(1)=''
as
set nocount on
declare @KB datetime
declare @KE datetime
declare @max int
declare @sqlstr nvarchar(2000)
declare @T nvarchar(1000)
declare @s int
declare @thisday varchar(15)
declare @p varchar(10)
declare @LB varchar(10)
select @max=max(T_ID) from Temp_JiDu
set @sqlstr=' create table ##rs(Chr_CarNum nvarchar(12),Chr_CarType nvarchar(4), '
set @s=1
while @s<=@max
begin
select @thisday=Chr_AllDate from Temp_JiDu where T_ID=@s
if @s=@max
set @sqlstr=@sqlstr + @thisday + ' varchar(15) DEFAULT ('+''''+'未交'+''''+') )'
else
set @sqlstr=@sqlstr + @thisday + ' varchar(15) DEFAULT ('+''''+'未交'+''''+'),'
set @s=@s+1
end
exec(@sqlstr)
Set @Sqlstr='insert into ##rs
(Chr_CarNum,Chr_CarType)
SELECT Chr_CarNum, Chr_CarType
FROM (SELECT DISTINCT Car_Base.Chr_CarNum, Car_Base.Chr_CarType
FROM Car_Base INNER JOIN
Car_Jiance ON Car_Jiance.Chr_CarNum = Car_Base.Chr_CarNum AND
Car_Jiance.Chr_CarType = Car_Base.Chr_CarType )
DERIVEDTBL'
exec(@sqlstr)
set @s=1
while @s<=@max
begin
select @thisday=Chr_AllDate,@kb=Date_One,@ke=Date_Two,@Lb=Chr_LeiBie from Temp_JiDu where T_ID=@s
set @p=
substring(@thisday,2,4)+'-'+substring(@thisday,6,2)+'-'+substring(@thisday,8,2)
set @sqlstr='update ##rs set '+@thisday+' =convert(Varchar(10),B.['+@Lb+'],121) from Car_Jiance B where (##rs.[Chr_CarNum]=B.[Chr_CarNum]) and (##rs.[Chr_CarType]=B.[Chr_CarType])
and (B.['+@Lb+'] Between '+''''+ convert(Varchar(10),@kb,121)+''''+' and '+ ''''+convert(Varchar(10),@ke,121)+''''+')'
exec(@sqlstr)
set @s=@s+1
end
set @sqlstr=' SELECT '+@S0
set @t=+' And ('
set @s=1
while @s<=@max
begin
select @thisday=Chr_AllDate from Temp_JiDu where T_ID=@s
if @s=@max
set @sqlstr=@sqlstr +' ##rs.'+@thisday+' '
else
set @sqlstr=@sqlstr +' ##rs.'+@thisday+' ,'
if @s=1
set @T=@T+'##rs.'+@thisday+'='+''''+'未交'+''''
else
set @T=@T+'or ##rs.'+@thisday+'='+''''+'未交'+''''
set @s=@s+1
End
if @t0='1'
set @t=''
else
set @t=@t+') '
Set @sqlstr=@sqlstr+@S1+' FROM ##rs INNER JOIN
Car_Base ON ##rs.Chr_CarNum = Car_Base.Chr_CarNum AND
##rs.Chr_CarType = Car_Base.Chr_CarType '
+@P0+@T+
' ORDER BY '+@D0
Print(@sqlstr)
exec(@sqlstr)
drop table ##rs
GO
select
from old as x,old as y, old as z.................九个。//old为原库
where x.项目编号=y,项目编号 and y.项目编号=z.项目编号 and ........九个。
。。。。。。。。。。。。。。。。
。。。。。。。。。。。。