原贴内容
http://community.csdn.net/Expert/topic/5415/5415119.xml?temp=.7444574
但在实际解决中因为跟我的实际环境有些不同,而且我又不会SQL,所以还是有很多问题,希望哪位热心人帮我解决一下,我可以把具体问题传给你们,解决后500分相赠(就剩下这些了),决不食言,谢谢大家啊!
实在是太着急,没有办法了……
http://community.csdn.net/Expert/topic/5415/5415119.xml?temp=.7444574
但在实际解决中因为跟我的实际环境有些不同,而且我又不会SQL,所以还是有很多问题,希望哪位热心人帮我解决一下,我可以把具体问题传给你们,解决后500分相赠(就剩下这些了),决不食言,谢谢大家啊!
实在是太着急,没有办法了……
把問題email給我吧,[email protected]
你用的SQL2000的吧?
右鍵你的表名-->所有工作-->產生SQL腳本
然後把附件email給我
sp_help 你的表名
--按Ctrl+T,再按F5
--把結果Copy到文本文件裡傳給我
你的qyrainyb08表結構要與rain_table中的一樣
iiiii char(10)
DateDay char(10)
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
alter TABLE [dbo].[qyrainyb08]
alter column [iiiii] [char](10) NOT NULL
go
alter TABLE [dbo].[qyrainyb08]
alter column [DateDay] [char](10) NOT NULL
GO
SET ANSI_PADDING OFF
服务器: 消息 4902,级别 16,状态 1,行 1
无法更改表 'dbo.qyrainyb08',因为数据库 'master' 中不存在此表。
服务器: 消息 4902,级别 16,状态 1,行 1
无法更改表 'dbo.qyrainyb08',因为数据库 'master' 中不存在此表。
use 你所在的數據庫
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
alter TABLE [dbo].[qyrainyb08]
alter column [iiiii] [char](10) NOT NULL
go
alter TABLE [dbo].[qyrainyb08]
alter column [DateDay] [char](10) NOT NULL
GO
SET ANSI_PADDING OFF
GOSET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
alter TABLE [dbo].[qyrainjg08]
alter column [iiiii] [char](10) NOT NULL
GO
alter TABLE [dbo].[qyrainjg08]
alter column
[DateDay] [char](10) NOT NULL
GO
alter TABLE [dbo].[qyrainjg08]
alter column [ybspan] [varchar](20) NULL
GO
alter TABLE [dbo].[qyrainjg08]
alter column [yblj] [decimal](18, 1) NULL
GO
alter TABLE [dbo].[qyrainjg08]
alter column [sklj] [decimal](18, 1) NULL
GO
SET ANSI_PADDING OFF
服务器: 消息 5074,级别 16,状态 8,行 1
对象 'PK_qyrainyb' 依赖于 列 'iiiii'。
服务器: 消息 4922,级别 16,状态 1,行 1
ALTER TABLE ALTER COLUMN iiiii 失败,因为有一个或多个对象访问此列。
服务器: 消息 5074,级别 16,状态 8,行 1
对象 'PK_qyrainyb' 依赖于 列 'DateDay'。
服务器: 消息 5074,级别 16,状态 1,行 1
对象 'PK_qyrainyb' 依赖于 列 'DateDay'。
服务器: 消息 4922,级别 16,状态 1,行 1
ALTER TABLE ALTER COLUMN DateDay 失败,因为有一个或多个对象访问此列。但是我在hbqxsql的用户表里没有看到有一个'PK_qyrainyb' 的表:(
有没有什么办法能够在不修改标的情况下完成这个查询啊江涛兄?
等一下,馬上搞定另外,你把預測表最近幾周的數據導到excel給我做測試
不會導的話,在查詢分析中查出來拷貝到excel中也行
select top 50 * from qyrainyb08 where iiiii='53889' and dateday>='20070201' and dateday<='20070228'
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
alter TABLE [dbo].[qyrainyb08]
drop CONSTRAINT [FK_qyrainyb08_rain_table]
go
alter TABLE [dbo].[qyrainyb08]
alter column[iiiii] [char](10) NOT NULL
go
alter TABLE [dbo].[qyrainyb08]
alter column[DateDay] [char](10) NOT NULL
GOSET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[qyrainyb08] WITH CHECK ADD CONSTRAINT [FK_qyrainyb08_rain_table] FOREIGN KEY([iiiii], [DateDay])
REFERENCES [dbo].[rain_table] ([iiiii], [DateDay])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[qyrainyb08] CHECK CONSTRAINT [FK_qyrainyb08_rain_table]
USE 你的數據庫名
GOSET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
alter TABLE [dbo].[qyrainjg08]
alter column [iiiii] [char](10) NOT NULL
GO
alter TABLE [dbo].[qyrainjg08]
alter column
[DateDay] [char](10) NOT NULL
GO
alter TABLE [dbo].[qyrainjg08]
alter column [ybspan] [varchar](20) NULL
GO
alter TABLE [dbo].[qyrainjg08]
alter column [yblj] [decimal](18, 1) NULL
GO
alter TABLE [dbo].[qyrainjg08]
alter column [sklj] [decimal](18, 1) NULL
GO
SET ANSI_PADDING OFF
服务器: 消息 3728,级别 16,状态 1,行 1
'FK_qyrainyb08_rain_table' 不是约束。
服务器: 消息 3727,级别 16,状态 1,行 1
未能除去约束。请参阅前面的错误信息。
服务器: 消息 5074,级别 16,状态 8,行 1
对象 'PK_qyrainyb' 依赖于 列 'iiiii'。
服务器: 消息 4922,级别 16,状态 1,行 1
ALTER TABLE ALTER COLUMN iiiii 失败,因为有一个或多个对象访问此列。
服务器: 消息 5074,级别 16,状态 8,行 1
对象 'PK_qyrainyb' 依赖于 列 'DateDay'。
服务器: 消息 5074,级别 16,状态 1,行 1
对象 'PK_qyrainyb' 依赖于 列 'DateDay'。
服务器: 消息 4922,级别 16,状态 1,行 1
ALTER TABLE ALTER COLUMN DateDay 失败,因为有一个或多个对象访问此列。
服务器: 消息 1753,级别 16,状态 1,行 1
列 'dbo.rain_table.iiiii' 与外键 'FK_qyrainyb08_rain_table' 中引用列 'qyrainyb08.iiiii' 的长度不同。
服务器: 消息 1750,级别 16,状态 1,行 1
未能创建约束。请参阅前面的错误信息。
服务器: 消息 4917,级别 16,状态 1,行 1
约束 'FK_qyrainyb08_rain_table' 不存在。
服务器: 消息 4916,级别 16,状态 1,行 1
未能启用或禁用约束。请参阅前面的错误信息。希望您老不要着急啊,我对这个真的是一窍不通……
CREATE TABLE [qyrainjg08] (
[iiiii] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[dateday] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[ybspan] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[yblj] [decimal](18, 1) NULL ,
[sklj] [decimal](18, 1) NULL ,
CONSTRAINT [PK_qyrainjg08] PRIMARY KEY CLUSTERED
(
[iiiii],
[dateday],
[ybspan]
) ON [PRIMARY]
) ON [PRIMARY]
GO
您老看看还可以吗?
你自己查一下預報表,看其外鍵是不叫PK_qyrainyb
sp_help qyrainyb08如果是的話,執行SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
alter TABLE [dbo].[qyrainyb08]
drop CONSTRAINT PK_qyrainyb --先去掉外鍵后才能改表結構
go
alter TABLE [dbo].[qyrainyb08]
alter column[iiiii] [char](10) NOT NULL
go
alter TABLE [dbo].[qyrainyb08]
alter column[DateDay] [char](10) NOT NULL
GOSET ANSI_PADDING OFF
GO
--再加上外鍵
ALTER TABLE [dbo].[qyrainyb08] WITH CHECK ADD CONSTRAINT [FK_qyrainyb08_rain_table] FOREIGN KEY([iiiii], [DateDay])
REFERENCES [dbo].[rain_table] ([iiiii], [DateDay])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[qyrainyb08] CHECK CONSTRAINT [FK_qyrainyb08_rain_table]
[qyrainjg08]:[iiiii] [char] (10) ,[dateday] [char] (10) ,[ybspan] [varchar] (20),[yblj] [decimal](18, 1),[sklj] [decimal](18, 1)。
[qyrainyb08]:[iiiii] [char] (10),[dateday] [char] (10),[024] [numeric](4, 1),[048] [numeric](4, 1) 一直到[168] [numeric](4, 1)。
[rain_table]:[iiiii] [char] (10),[dateday] [char] (10) ,[r0808] [numeric](18, 2),[r2020] [numeric](18, 2)。
这样子我们可以进行下一步了吗?
PK是主鍵,primary key
FK是外鍵,foreign key可以了
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'024' as ybspan,a.[024] as yblj,b.r0808 as sklj
from [qyrainyb08] a
left join [rain_table] b on a.iiiii=b.iiiii and a.dateday=replace(convert(varchar(10),dateadd(dd,1,b.dateday),120),'-','')
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'048' as ybspan,a.[048] as yblj,b.r0808 as sklj
from [qyrainyb08] a
left join [rain_table] b on a.iiiii=b.iiiii and a.dateday=replace(convert(varchar(10),dateadd(dd,2,b.dateday),120),'-','')
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'072' as ybspan,a.[072] as yblj,b.r0808 as sklj
from [qyrainyb08] a
left join [rain_table] b on a.iiiii=b.iiiii and a.dateday=replace(convert(varchar(10),dateadd(dd,3,b.dateday),120),'-','')
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'096' as ybspan,a.[096] as yblj,b.r0808 as sklj
from [qyrainyb08] a
left join [rain_table] b on a.iiiii=b.iiiii and a.dateday=replace(convert(varchar(10),dateadd(dd,4,b.dateday),120),'-','')
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'120' as ybspan,a.[120] as yblj,b.r0808 as sklj
from [qyrainyb08] a
left join [rain_table] b on a.iiiii=b.iiiii and a.dateday=replace(convert(varchar(10),dateadd(dd,5,b.dateday),120),'-','')
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'144' as ybspan,a.[144] as yblj,b.r0808 as sklj
from [qyrainyb08] a
left join [rain_table] b on a.iiiii=b.iiiii and a.dateday=replace(convert(varchar(10),dateadd(dd,6,b.dateday),120),'-','')
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'168' as ybspan,a.[168] as yblj,b.r0808 as sklj
from [qyrainyb08] a
left join [rain_table] b on a.iiiii=b.iiiii and a.dateday=replace(convert(varchar(10),dateadd(dd,7,b.dateday),120),'-','')
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Rocky
-- Create date: 2007.03.24
-- Description: 將指定日期之后的預測和實況結果插入到結果表[qyrainjg08]
-- =============================================
CREATE PROCEDURE dbo.SP_qyRainJG08
@date smalldatetime=null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF @date IS NULL
SET @date = GETDATE()
DECLARE @StrDate varchar(10) SET @StrDate=replace(convert(varchar(10),@date,120),'-','') -- Insert statements for procedure here
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'024' as ybspan,a.[024] as yblj,b.r0808 as sklj into #temp
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and a.dateday=replace(convert(varchar(10),dateadd(dd,1,b.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'048' as ybspan,a.[048] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and a.dateday=replace(convert(varchar(10),dateadd(dd,2,b.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'072' as ybspan,a.[072] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and a.dateday=replace(convert(varchar(10),dateadd(dd,3,b.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'096' as ybspan,a.[096] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and a.dateday=replace(convert(varchar(10),dateadd(dd,4,b.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'120' as ybspan,a.[120] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and a.dateday=replace(convert(varchar(10),dateadd(dd,5,b.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'144' as ybspan,a.[144] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and a.dateday=replace(convert(varchar(10),dateadd(dd,6,b.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'168' as ybspan,a.[168] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and a.dateday=replace(convert(varchar(10),dateadd(dd,7,b.dateday),120),'-','')
WHERE a.dateday>=@StrDate insert [qyrainjg08]([iiiii],[DateDay],[ybspan],[yblj],[sklj])
SELECT * FROM #temp A (nolock) WHERE NOT EXISTS (SELECT 1 FROM [qyrainjg08] (nolock) where [iiiii]=A.[iiiii] and dateday=A.dateday AND [ybspan]=A.[ybspan])
END
GO
__________________________________________________________________可以創建一個作業(job)每天執行這個存儲過程即可,每個月就不用再定時運行了
創建作業的代碼如下:
USE [msdb]
GO
/****** 物件: Job [Job_qyRainJG08] 指令碼日期: 03/24/2007 14:14:13 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** 物件: JobCategory [[Uncategorized (Local)]]] 指令碼日期: 03/24/2007 14:14:13 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackENDDECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Job_qyRainJG08',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'EXEC DBO.SP_qyRainJG08',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** 物件: Step [08] 指令碼日期: 03/24/2007 14:14:13 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'08',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC DBO.SP_qyRainJG08',
@database_name=N'hbqxsql',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'everyday',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20070324,
@active_end_date=99991231,
@active_start_time=233000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
还有一个问题就是我想在jg08的表中把实况的雨量值转换成实况的量级,即0毫米为0.0级,0.01-9.99毫米为1.0级,10-24.99毫米为2.0级,25-49.99为3.0级,50-99.99为4.0级,100以上为5.0级,rain_table表里面的数值全都是雨量值,这个怎么在查询中转换呢?
drop table #temp--即在查詢分析器中執行下面代碼:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Rocky
-- Create date: 2007.03.24
-- Description: 將指定日期之后的預測和實況結果插入到結果表[qyrainjg08]
-- =============================================
alter PROCEDURE dbo.SP_qyRainJG08
@date smalldatetime=null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF @date IS NULL
SET @date = GETDATE()
DECLARE @StrDate varchar(10) SET @StrDate=replace(convert(varchar(10),@date,120),'-','') -- Insert statements for procedure here
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'024' as ybspan,a.[024] as yblj,b.r0808 as sklj into #temp
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and a.dateday=replace(convert(varchar(10),dateadd(dd,1,b.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'048' as ybspan,a.[048] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and a.dateday=replace(convert(varchar(10),dateadd(dd,2,b.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'072' as ybspan,a.[072] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and a.dateday=replace(convert(varchar(10),dateadd(dd,3,b.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'096' as ybspan,a.[096] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and a.dateday=replace(convert(varchar(10),dateadd(dd,4,b.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'120' as ybspan,a.[120] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and a.dateday=replace(convert(varchar(10),dateadd(dd,5,b.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'144' as ybspan,a.[144] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and a.dateday=replace(convert(varchar(10),dateadd(dd,6,b.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'168' as ybspan,a.[168] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and a.dateday=replace(convert(varchar(10),dateadd(dd,7,b.dateday),120),'-','')
WHERE a.dateday>=@StrDate insert [qyrainjg08]([iiiii],[DateDay],[ybspan],[yblj],[sklj])
SELECT * FROM #temp A (nolock) WHERE NOT EXISTS (SELECT 1 FROM [qyrainjg08] (nolock) where [iiiii]=A.[iiiii] and dateday=A.dateday AND [ybspan]=A.[ybspan])
truncate table #temp
drop table #temp
END
GO
truncate table #temp
drop table #temp
后出现了下面的一个错误消息:
服务器: 消息 208,级别 16,状态 6,过程 SP_qyRainJG08,行 68
对象名 'dbo.SP_qyRainJG08' 无效。
我们没有创建这样子一个表好像……
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Rocky
-- Create date: 2007.03.24
-- Description: 將指定日期之后的預測和實況結果插入到結果表[qyrainjg08]
-- =============================================
alter PROCEDURE dbo.SP_qyRainJG08
@date smalldatetime=null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF @date IS NULL
SET @date = GETDATE()
DECLARE @StrDate varchar(10) SET @StrDate=replace(convert(varchar(10),@date,120),'-','') -- Insert statements for procedure here
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'024' as ybspan,a.[024] as yblj,b.r0808 as sklj into #temp
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,1,a.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'048' as ybspan,a.[048] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,2,a.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'072' as ybspan,a.[072] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,3,a.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'096' as ybspan,a.[096] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,4,a.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'120' as ybspan,a.[120] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,5,a.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'144' as ybspan,a.[144] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,6,a.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'168' as ybspan,a.[168] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,7,a.dateday),120),'-','')
WHERE a.dateday>=@StrDate insert [qyrainjg08]([iiiii],[DateDay],[ybspan],[yblj],[sklj])
SELECT * FROM #temp A (nolock) WHERE NOT EXISTS (SELECT 1 FROM [qyrainjg08] (nolock) where [iiiii]=A.[iiiii] and dateday=A.dateday AND [ybspan]=A.[ybspan])
truncate table #temp
drop table #temp
END
GO
馬上貼出來
,实况的量级=case
when [sklj] is null then '實況雨量未輸入'
when [sklj]=0 then '0毫米'
when [sklj]=1.0 then '0.01-9.99毫米'
when [sklj]=2.0 then '10-24.99毫米'
when [sklj]=3.0 then '25-49.99毫米'
when [sklj]=4.0 then '50-99.99毫米'
when [sklj]=5.0 then '100毫米以上'
else '其他量級'
end
FROM [dbo].[qyrainjg08]
where iiiii='53889' and dateday>='20070201' and dateday<='20070228'
SELECT [iiiii],[DateDay],[ybspan],[yblj],[sklj]
,实况的量级=case
when [sklj] is null then '實況雨量未輸入'
when [sklj]=0 then '0毫米'
when [sklj]=1.0 then '0.01-9.99毫米'
when [sklj]=2.0 then '10-24.99毫米'
when [sklj]=3.0 then '25-49.99毫米'
when [sklj]=4.0 then '50-99.99毫米'
when [sklj]=5.0 then '100毫米以上'
else '其他量級'
end
FROM [dbo].[qyrainjg08]
where iiiii='53889' and dateday>='20070201' and dateday<='20070228'
truncate table qyrainjg08
exec SP_qyRainJG08至於还有您老给的这一段代码插入到什么地方呢?
那是查詢時用到的語句啊,你前台程序會用到
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Rocky
-- Create date: 2007.03.24
-- Description: 將指定日期之后的預測和實況結果插入到結果表[qyrainjg08]
-- =============================================
create PROCEDURE dbo.SP_qyRainJG08
@date smalldatetime=null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF @date IS NULL
SET @date = GETDATE()
DECLARE @StrDate varchar(10) SET @StrDate=replace(convert(varchar(10),@date,120),'-','') -- Insert statements for procedure here
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'024' as ybspan,a.[024] as yblj,b.r0808 as sklj into #temp
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,1,a.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'048' as ybspan,a.[048] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,2,a.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'072' as ybspan,a.[072] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,3,a.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'096' as ybspan,a.[096] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,4,a.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'120' as ybspan,a.[120] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,5,a.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'144' as ybspan,a.[144] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,6,a.dateday),120),'-','')
WHERE a.dateday>=@StrDate
union all
select rtrim(ltrim(a.iiiii)) as iiiii,rtrim(ltrim(a.dateday)) as dateday
,'168' as ybspan,a.[168] as yblj,b.r0808 as sklj
from [qyrainyb08] a (nolock)
left join [rain_table] b (nolock) on a.iiiii=b.iiiii and b.dateday=replace(convert(varchar(10),dateadd(dd,7,a.dateday),120),'-','')
WHERE a.dateday>=@StrDate insert [qyrainjg08]([iiiii],[DateDay],[ybspan],[yblj],[sklj])
SELECT * FROM #temp A (nolock) WHERE NOT EXISTS (SELECT 1 FROM [qyrainjg08] (nolock) where [iiiii]=A.[iiiii] and dateday=A.dateday AND [ybspan]=A.[ybspan])
truncate table #temp
drop table #temp
END
GO
USE hbqxsql
GO
/****** 物件: Table [dbo].[tblLevel] 指令碼日期: 03/24/2007 15:06:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblLevel](
[lj] [decimal](18, 1) NOT NULL,
[ljName] [varchar](50) not NULL,
CONSTRAINT [PK_tblLevel] PRIMARY KEY CLUSTERED
(
[lj] ASC
) ON [PRIMARY]
) ON [PRIMARY]GO
SET ANSI_PADDING OFF--插入數據
insert [tblLevel]
select '0','0毫米'
union all select 1.0 , '0.01-9.99毫米'
union all select 2.0 , '10-24.99毫米'
union all select 3.0 , '25-49.99毫米'
union all select 4.0 , '50-99.99毫米'
union all select 5.0 , '100毫米以上'--查詢
SELECT [iiiii],[DateDay],[ybspan],[yblj],[sklj],b.ljname
FROM [dbo].[qyrainjg08] a (nolock) inner join tblLevel b on a.sklj=b.lj
where iiiii='53889' and dateday>='20070201' and dateday<='20070228'
还有就是清空结果表后,再执行exec SP_qyRainJG08,再执行您老给的那段最长的代码,还是执行不出来查询结果……