要实现的目标:
1.存在一个表ANSWERLOG,这个表记录了所有的记录
2.按表里的记录的日期把表分成对应月份的表,比如2008年9月的记录生成一个表,生成的表名为ANSWERLOG200809
3.生成的表是从当月的上一个月开始的,每个月都生成一个表
4.生成了一个表后就把原来的ANSWERLOG表中的相应记录删除掉.比如生成了ANSWERLOG200809这个新表后就把原表中2008年9月,这样原来的表只记录着当月的记录
5.用存储过程实现.下面是我用游标写的一个存储过程,没有语法错误,但是没有实现.
CREATE PROCEDURE aaa AS
declare @sql varchar(200)
set @sql= ' '
declare @del varchar(200)
set @del= ' '
declare @ID int
declare @vote_time varchar(12)
declare c1 cursor for
select [ID], vote_time from ANSWERLOG where [ID]=@ID ----vote_time为该记录的创建时间,精确到秒and vote_time=@vote_time
open c1
fetch next from c1 into @ID,@vote_time
while @@fetch_status=0
begin
set @sql='select * into ANSWERLOG'+substring(@vote_time,0,4)+substring(@vote_time,6,2)+'where vote_time='+@vote_time----------------------------实现表名的后缀
set @del='delete from ANSWERLOG where [ID]='+@ID---删除原表中的记录
exec(@sql)
exec(@del)
fetch next from c1 into @ID,@vote_time
end
close c1
deallocate c1
----------------------------------------------------
如果有更好的方法还请大侠们给出.不胜感激.
1.存在一个表ANSWERLOG,这个表记录了所有的记录
2.按表里的记录的日期把表分成对应月份的表,比如2008年9月的记录生成一个表,生成的表名为ANSWERLOG200809
3.生成的表是从当月的上一个月开始的,每个月都生成一个表
4.生成了一个表后就把原来的ANSWERLOG表中的相应记录删除掉.比如生成了ANSWERLOG200809这个新表后就把原表中2008年9月,这样原来的表只记录着当月的记录
5.用存储过程实现.下面是我用游标写的一个存储过程,没有语法错误,但是没有实现.
CREATE PROCEDURE aaa AS
declare @sql varchar(200)
set @sql= ' '
declare @del varchar(200)
set @del= ' '
declare @ID int
declare @vote_time varchar(12)
declare c1 cursor for
select [ID], vote_time from ANSWERLOG where [ID]=@ID ----vote_time为该记录的创建时间,精确到秒and vote_time=@vote_time
open c1
fetch next from c1 into @ID,@vote_time
while @@fetch_status=0
begin
set @sql='select * into ANSWERLOG'+substring(@vote_time,0,4)+substring(@vote_time,6,2)+'where vote_time='+@vote_time----------------------------实现表名的后缀
set @del='delete from ANSWERLOG where [ID]='+@ID---删除原表中的记录
exec(@sql)
exec(@del)
fetch next from c1 into @ID,@vote_time
end
close c1
deallocate c1
----------------------------------------------------
如果有更好的方法还请大侠们给出.不胜感激.
解决方案 »
- 各位帮忙解答这句语句
- 菜鳥看到這個問題,沒弄明白,還請幫助解釋一下。
- SQL中关于时间范围来源于另一个表的开始时间和结束时间问题?
- 一个列为自增列,如何用代码修改该列为非自增,谢谢。
- 请教高手,一个SQL的算法问题,邹捷大侠请进!
- 关于循环插入的问题!()
- 这个问题如何看待?
- 当一个视图关联的表进行改变后,视图重建的时间在前台会感受到吗,还是在后台单独执行?(表和视图都有10万条以上的纪录)
- 有关数据库精度的问题~~~~~~~~~~??高分
- 怎样在excel2000图表中添加自定义趋势线?
- 我想从数据库、患者基本信息表中归类,请高手帮忙写下SQL语句
- 怎样删除sql一列中所有行的尾部代码??字段是NTEXT的数据类型
from tb
where 日期>'2008-09-01' and 日期<'2008-10-01'
CREATE PROCEDURE aaa AS
declare @sql varchar(200)
set @sql= ' '
declare @del varchar(200)
set @del= ' '
--declare @ID int
declare @vote_time varchar(12)
declare c1 cursor for select convert(varchar(6),vote_time,120) from ANSWERLOG group by convert(varchar(6),vote_time,120) ----vote_time为该记录的创建时间,精确到秒
open c1
fetch next from c1 into @vote_time
while @@fetch_status=0
begin
set @sql='select * into ANSWERLOG'+@vote_time +'where convert(varchar(6),vote_time,120)='+@vote_time----------------------------实现表名的后缀
set @del='delete from ANSWERLOG convert(varchar(6),vote_time,120)='+@vote_time---删除原表中的记录
exec(@sql)
exec(@del)
fetch next from c1 into @vote_time
end
close c1
deallocate c1
@vote_time datetime --要统计哪年哪月
AS
declare @sql varchar(200)
--创建新表
set @sql= 'select * into ANSWERLOG'+convert(varchar(6),@vote_time,112)+' where 1=2'
exec(@sql)
--插本月记录
set @sql='insert into ANSWERLOG'+convert(varchar(6),@vote_time,112)
+' select * from ANSWERLOG where datediff(m,vote_time,'+convert(varchar(10),@vote_time,120)+')=0'
exec(@sql)
--删除源表记录
set @sql='delete ANSWERLOG where datediff(m,vote_time,'+convert(varchar(10),@vote_time,120)+')=0'
exec(@sql)
go
IF object_id('ANSWERLOG','u') IS NOT NULL
DROP TABLE ANSWERLOG
GOCREATE TABLE ANSWERLOG(id INT,vote_time DATETIME)
GO
INSERT ANSWERLOG SELECT 1,GETDATE()
UNION ALL SELECT 2,GETDATE()
UNION ALL SELECT 3,DATEADD(mm,-3,GETDATE())
UNION ALL SELECT 3,DATEADD(mm,-4,GETDATE())
GODECLARE @n INT,@i INT,@s VARCHAR(1000)
SELECT DISTINCT dt = CONVERT(VARCHAR(6),vote_time,112),idx=IDENTITY(INT) INTO # FROM ANSWERLOG
SELECT @n=@@ROWCOUNT,@i=1
WHILE @n>=@i
BEGIN
SELECT TOP 1 @s='SELECT * INTO ANSWERLOG' + dt + ' FROM ANSWERLOG WHERE CONVERT(VARCHAR(6),vote_time,112)=''' + dt + ''';
DELETE FROM ANSWERLOG WHERE CONVERT(VARCHAR(6),vote_time,112)=''' + dt + '''
' FROM # WHERE idx=@i
SET @i=@i+1
EXEC(@s)
END
SELECT * FROM ANSWERLOG
--SELECT * FROM ANSWERLOG200808
--SELECT * FROM ANSWERLOG200809
--SELECT * FROM ANSWERLOG200812
GO
DROP TABLE #
--DROP TABLE ANSWERLOG200808,ANSWERLOG200809,ANSWERLOG200812
GO
CREATE PROCEDURE aaa
@vote_time datetime --要统计哪年哪月
AS
declare @sql varchar(200)
--插本月记录至新表中
set @sql= 'select * into ANSWERLOG'+convert(varchar(6),@vote_time,112)
+' from ANSWERLOG where datediff(m,vote_time,'+convert(varchar(10),@vote_time,120)+')=0'
exec(@sql)
--删除源表记录
set @sql='delete ANSWERLOG where datediff(m,vote_time,'+convert(varchar(10),@vote_time,120)+')=0'
exec(@sql)
go
if object_id(N'ANSWERLOG','U') is not null
drop table ANSWERLOG;
go
create table ANSWERLOG
(name char(1) default('a'),
date datetime
)
;with xwj
as
(select date=cast('2008-11-05' as datetime)
union all
select date+1 from xwj where date<='2008-12-5'
)
insert into ANSWERLOG select naem='a',date from xwj;go
create procedure p_ANSWERLOG @year int,@month int
as
--declare @year int,@month int
--select @year=2008,@month=11;
declare @sql varchar(8000)
select @sql='if object_id(N''ANSWERLOG'+cast(@year as varchar(4))+right(cast(@month+100 as varchar(3)),2)+''',''U'') is not null'+char(13)+'begin'+char(13)+'raiserror(''表已存在'',16,1)'+char(13)+'return'+char(13) +'end'+char(13)
+'else'+char(13)+'begin'+char(13)+'select * into ANSWERLOG'+cast(@year as varchar(4))+right(cast(@month+100 as varchar(3)),2)+ ' from ANSWERLOG where year(date)='+cast(@year as varchar(4))+'and month(date)='+cast(@month as varchar(2)) +char(13)+'end'
exec(@sql)
delete from ANSWERLOG where year(date)=@year and month(date)=@monthexec p_ANSWERLOG @year=2008,@month=11select * from ANSWERLOG200811/*
a 2008-11-05 00:00:00.000
a 2008-11-06 00:00:00.000
a 2008-11-07 00:00:00.000
a 2008-11-08 00:00:00.000
a 2008-11-09 00:00:00.000
a 2008-11-10 00:00:00.000
a 2008-11-11 00:00:00.000
a 2008-11-12 00:00:00.000
a 2008-11-13 00:00:00.000
a 2008-11-14 00:00:00.000
a 2008-11-15 00:00:00.000
a 2008-11-16 00:00:00.000
a 2008-11-17 00:00:00.000
a 2008-11-18 00:00:00.000
a 2008-11-19 00:00:00.000
a 2008-11-20 00:00:00.000
a 2008-11-21 00:00:00.000
a 2008-11-22 00:00:00.000
a 2008-11-23 00:00:00.000
a 2008-11-24 00:00:00.000
a 2008-11-25 00:00:00.000
a 2008-11-26 00:00:00.000
a 2008-11-27 00:00:00.000
a 2008-11-28 00:00:00.000
a 2008-11-29 00:00:00.000
a 2008-11-30 00:00:00.000
*/
exec p_ANSWERLOG @year=2008,@month=11/*
消息 50000,级别 16,状态 1,第 1 行
表已存在(26 行受影响)
*/
drop table ANSWERLOG;
go
create table ANSWERLOG
(name char(1) default('a'),
date datetime
)
;with xwj
as
(select date=cast('2008-11-05' as datetime)
union all
select date+1 from xwj where date<='2008-12-5'
)
insert into ANSWERLOG select naem='a',date from xwj;go
alter procedure p_ANSWERLOG
as
declare @year int,@month int
select @year=year(getdate()),@month=month(getdate());
declare @sql varchar(8000)
select @sql='if object_id(N''ANSWERLOG'+cast(@year as varchar(4))+right(cast(@month+100 as varchar(3)),2)+''',''U'') is not null'+char(13)+'begin'+char(13)+'raiserror(''表已存在'',16,1)'+char(13)+'return'+char(13) +'end'+char(13)
+'else'+char(13)+'begin'+char(13)+'select * into ANSWERLOG'+cast(@year as varchar(4))+right(cast(@month+100 as varchar(3)),2)+ ' from ANSWERLOG'+char(13)+'end'
exec(@sql)
delete from ANSWERLOG exec p_ANSWERLOG
select * from ANSWERLOG200812/*
a 2008-11-05 00:00:00.000
a 2008-11-06 00:00:00.000
a 2008-11-07 00:00:00.000
a 2008-11-08 00:00:00.000
a 2008-11-09 00:00:00.000
a 2008-11-10 00:00:00.000
a 2008-11-11 00:00:00.000
a 2008-11-12 00:00:00.000
a 2008-11-13 00:00:00.000
a 2008-11-14 00:00:00.000
a 2008-11-15 00:00:00.000
a 2008-11-16 00:00:00.000
a 2008-11-17 00:00:00.000
a 2008-11-18 00:00:00.000
a 2008-11-19 00:00:00.000
a 2008-11-20 00:00:00.000
a 2008-11-21 00:00:00.000
a 2008-11-22 00:00:00.000
a 2008-11-23 00:00:00.000
a 2008-11-24 00:00:00.000
a 2008-11-25 00:00:00.000
a 2008-11-26 00:00:00.000
a 2008-11-27 00:00:00.000
a 2008-11-28 00:00:00.000
a 2008-11-29 00:00:00.000
a 2008-11-30 00:00:00.000
a 2008-12-01 00:00:00.000
a 2008-12-02 00:00:00.000
a 2008-12-03 00:00:00.000
a 2008-12-04 00:00:00.000
a 2008-12-05 00:00:00.000
a 2008-12-06 00:00:00.000
*/
exec p_ANSWERLOG/*
消息 50000,级别 16,状态 1,第 1 行
表已存在(26 行受影响)
*/