語法錯誤
申明table變量
declare @table table
(
字段
)
select * from @table
動態sql
declare @tablename varchar(20)
declare @sql varchar(200)
set @sql='select * from '+@tablename
exec(@sql)
申明table變量
declare @table table
(
字段
)
select * from @table
動態sql
declare @tablename varchar(20)
declare @sql varchar(200)
set @sql='select * from '+@tablename
exec(@sql)
解决方案 »
- 关于浮点型Sum()求和。。
- 如何保存SQL查询结果?在SQLSERVER窗口上已经查到的结果 ,没有另存选项啊
- 一个高难度的SQL语句
- 这样的 Excel 表格的数据表结构如何创建?求解啊
- 我如何知道我的某个表的某个列,到底被哪些表的哪些列外键依赖?
- 一道数学简便计算问题
- 求高手关于两个表连接优化的问题,执行需要30分钟以上.
- 在存储过程中怎么获得2次insert 的@@identity值??
- 数据库插入问题 救救小弟啊
- sql server的用户与各自的数据库问题
- sql 如何查询出具体值对应的数据库中存储的文本范围不等式所在的行
- 表中横的一条记录如何根据里面的几个字段来变成竖的显示,急急急!
表变量不是一个SQL字符串,而是DECLARE @table AS TABLE (...)
create table #t
(
account nvarchar(50)
)
create table #t1
(
account nvarchar(50)
)
create table #t2
(
time varchar,
num int
)
insert into #t
select account from [dbo].[20140901_TS_accountlogin_log] where logincount=1
--select * from #t
declare @time3 int
declare @time4 int
declare @time5 varchar(100)
declare @table varchar(100)
declare @account varchar(100)
declare @account1 varchar(100)
declare @num int
set @num=0
set @time3='20140902'
set @time4='20140908'
--print @time3
while @time3<=@time4
begin
--print @time3
set @time5=cast(@time3 as varchar(100))
set @table='[dbo].['+@time5+'_TS_accountlogin_log]'
insert into #t1
exec('select account from '+@table+'')
--print @tabledeclare chongzhi scroll cursor for select account from #t
open chognzhi
fetch next from chongzhi into @account
while (@@FETCH_STATUS=0)
begin
declare chongzhi1 scroll cursor for select account from #t1
open chognzhi1
fetch next from chongzhi1 into @account1
while (@@FETCH_STATUS=0)
begin
if (@account=@account1 and @num=0)
set @num=@num+1
end
fetch next from chongzhi1 into @account1
end
insert into #t2
select @time3,@num
delete from #t1
close chongzhi1
deallocate chongzhi1
fetch next from chongzhi into @account
end
close chongzhi
deallocate chongzhi
set @time3=@time3+1
end
drop table #t
drop table #t1
drop table #t2
我觉得那个and要加,是最外面while的,但是加了就报错,下面一行有下划线,没加运行后也报错,游标不存在什么的,帮我看看end是否有加错?谢谢啦
create table #t
(
account nvarchar(50)
)
create table #t1
(
account nvarchar(50)
)
create table #t2
(
time varchar,
num int
)
insert into #t
select account from [dbo].[20140901_TS_accountlogin_log] where logincount=1
--select * from #t
declare @time3 int
declare @time4 int
declare @time5 varchar(100)
declare @table varchar(100)
declare @account varchar(100)
declare @account1 varchar(100)
declare @num int
set @num=0
set @time3='20140902'
set @time4='20140908'
--print @time3
while @time3<=@time4
begin
--print @time3
set @time5=cast(@time3 as varchar(100))
set @table='[dbo].['+@time5+'_TS_accountlogin_log]'
insert into #t1
exec('select account from '+@table+'')
--print @tabledeclare chongzhi scroll cursor for select account from #t
open chognzhi
fetch next from chongzhi into @account
while (@@FETCH_STATUS=0)
begin
declare chongzhi1 scroll cursor for select account from #t1
open chognzhi1
fetch next from chongzhi1 into @account1
while (@@FETCH_STATUS=0)
begin
if (@account=@account1 and @num=0)
set @num=@num+1
end
fetch next from chongzhi1 into @account1
end
insert into #t2
select @time3,@num
delete from #t1
close chongzhi1
deallocate chongzhi1
fetch next from chongzhi into @account
end
close chongzhi
deallocate chongzhi
set @time3=@time3+1
end
drop table #t
drop table #t1
drop table #t2
我觉得那个and要加,是最外面while的,但是加了就报错,下面一行有下划线,没加运行后也报错,游标不存在什么的,帮我看看end是否有加错?谢谢啦
create table #t
(
account nvarchar(50)
)
create table #t1
(
account nvarchar(50)
)
create table #t2
(
time varchar,
num int
)
insert into #t
select account from [dbo].[20140901_TS_accountlogin_log] where logincount=1
--select * from #t
declare @time3 int
declare @time4 int
declare @time5 varchar(100)
declare @table varchar(100)
declare @account varchar(100)
declare @account1 varchar(100)
declare @num int
set @num=0
set @time3='20140902'
set @time4='20140908'
--print @time3
while @time3<=@time4
begin
--print @time3
set @time5=cast(@time3 as varchar(100))
set @table='[dbo].['+@time5+'_TS_accountlogin_log]'
insert into #t1
exec('select account from '+@table+'')
--print @tabledeclare chongzhi scroll cursor for select account from #t
open chognzhi
fetch next from chongzhi into @account
while (@@FETCH_STATUS=0)
begin
declare chongzhi1 scroll cursor for select account from #t1
open chognzhi1
fetch next from chongzhi1 into @account1
while (@@FETCH_STATUS=0)
begin
if (@account=@account1 and @num=0)
set @num=@num+1
end
fetch next from chongzhi1 into @account1
end
insert into #t2
select @time3,@num
delete from #t1
close chongzhi1
deallocate chongzhi1
fetch next from chongzhi into @account
end
close chongzhi
deallocate chongzhi
set @time3=@time3+1
end
drop table #t
drop table #t1
drop table #t2
我觉得那个and要加,是最外面while的,但是加了就报错,下面一行有下划线,没加运行后也报错,游标不存在什么的,帮我看看end是否有加错?谢谢啦
將兩個fetch語句後的end改為begin,再試一下。
ACCOUNT NVARCHAR(50))
CREATE TABLE #t1 (
ACCOUNT NVARCHAR(50))
CREATE TABLE #t2 (
TIME VARCHAR,
num INT)
INSERT INTO #t
SELECT ACCOUNT
FROM [dbo].[20140901_ts_accountlogin_log]
WHERE logincount = 1
--select * from #t
DECLARE @time3 INT
DECLARE @time4 INT
DECLARE @time5 VARCHAR(100)
DECLARE @table VARCHAR(100)
DECLARE @account VARCHAR(100)
DECLARE @account1 VARCHAR(100)
DECLARE @num INT
SET @num = 0
SET @time3 = '20140902'
SET @time4 = '20140908'
--print @time3
WHILE @time3 <= @time4
BEGIN
--print @time3
SET @time5 = CAST(@time3 AS VARCHAR(100))
SET @table = '[dbo].['
+ @time5
+ '_TS_accountlogin_log]'
INSERT INTO #t1
EXEC( 'select account from ' + @table + '')
--print @table
DECLARE chongzhi SCROLL CURSOR FOR
SELECT ACCOUNT
FROM #t
OPEN chognzhi
FETCH NEXT FROM chongzhi
INTO @account
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE chongzhi1 SCROLL CURSOR FOR
SELECT ACCOUNT
FROM #t1
OPEN chognzhi1
FETCH NEXT FROM chongzhi1
INTO @account1
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@account = @account1
AND @num = 0)
SET @num = @num
+ 1
END
FETCH NEXT FROM chongzhi1
INTO @account1
END
INSERT INTO #t2
SELECT @time3,
@num
DELETE FROM #t1
CLOSE chongzhi1
DEALLOCATE chongzhi1
FETCH NEXT FROM chongzhi
INTO @account
END
CLOSE chongzhi
DEALLOCATE chongzhiDROP TABLE #t
DROP TABLE #t1
DROP TABLE #t2
所以你3楼标红的end是不需要的。