while循环不行吧,可以考虑要用游标
解决方案 »
- 一个sql查询的问题,在线等答案,有合适答案马上结贴。谢谢
- 帮忙,简单问题,想拿分的速度进,很快结贴!
- 大家帮帮忙呀!一个sql语句的问题!
- DTS远程导入数据
- 2个sql语句如何连接(在线等)
- 在sql server中调用ole自动化存储过程生成一个文本文件,为什么不对
- sp_executesql中的参数如果是float类型就不能比较大小????
- 为何在Windows2KADS中安装MS SQL 2000 中文企业版失败?
- Excel 向 SQL Server 2000 导入数据是我遇到的问题是,不能插入空值()
- 我想在运行程序时只对SQL数据库文件进行操作,不安装Sql?
- 菜鸟问题,函数截取
- sql存储过程如何实现将一张表数据插入或更新到另外一张表
--参考下,希望能对有启发
declare @db_id int
set @db_id=1
while @db_id<(select max(database_id) from sys.databases)
begin
select * from (select [name] from sys.databases where database_id=1) t
set @db_id=@db_id+1
end
declare @rows int
declare @name varchar(100)
declare @n1 int
declare @rows1 int
declare @name1 varchar(100)
declare @sql varchar(100)
set @n1=1
set @n=1
create table #temp
(
id int identity(1,1),
name varchar(100)
)
create table #temp1
(
id int identity(1,1),
name varchar(100)
)
insert into #temp
select name from master..sysdatabases where name like'%cqsd_extendsgamelog%'
set @rows = @@rowcount
while @n <= @rows
begin
select @name=name from #temp where id=@n
print(@name)
set @sql='insert into #temp1 select name from '+@name+'.dbo.sysobjects where name like''%TS_Leigong_log%'''
EXEC (@sql)
select * from #temp1
--select itemname,MAX(ngSkillLevel) from @name.[dbo].
set @n = @n + 1
delete from #temp1
end
drop table #temp
drop table #temp1
提示错误消息 105,级别 15,状态 1,第 1 行
字符串 '%TS_Le' 后的引号不完整。
消息 102,级别 15,状态 1,第 1 行
'%TS_Le' 附近有语法错误。 麻烦看一下
-->
declare @sql varchar(8000)
declare @rows int
declare @name varchar(100)
declare @n1 int
declare @rows1 int
declare @name1 varchar(100)
declare @sql varchar(8000)
declare @sql1 varchar(8000)
declare @sql2 varchar(8000)
set @n1=1
set @n=1
create table #temp
(
id int identity(1,1),
name varchar(100)
)
create table #temp1
(
id int identity(1,1),
name1 varchar(100)
)
create table #temp2
(
playername varchar(100),
itemname varchar(100),
level int
)
create table #temp3
(
playername varchar(100),
itemname varchar(100),
level int
)
insert into #temp
select name from master..sysdatabases where name like'%cqsd_extendsgamelog%'
set @rows = @@rowcount
while @n <= @rows
begin
print @n
select @name=name from #temp where id=@n
print(@name)
set @sql='insert into #temp1 select name from '+@name+'.dbo.sysobjects where name like''%TS_Leigong_log%'''
EXEC (@sql)
--select * from #temp1
set @rows1=@@rowcount
print @rows1
while @n1<= @rows1
begin
print @n1
select @name1=name1 from #temp1 where id=@n1
print @name1
set @sql1='insert into #temp2 select playername,itemname,MAX(ngSkillLevel) from '+@name+'.[dbo].['+@name1+'] where itemname like''%强化%'' group by playername,itemname order by MAX(ngSkillLevel)'
print(@sql1)
set @n1=@n1+1
print @name1
end
set @n1=1
set @n=@n + 1
delete from #temp1
end
set @sql2='insert into #temp3 select playername,itemname,max(level) from #temp2 group by playername,itemname order by MAX(level)'
EXEC (@sql2)
select * from #temp3
drop table #temp
drop table #temp1
drop table #temp2
drop table #temp31
cqsd_extendsgamelog_neice1(12 行受影响)
12
1
20140622_TS_Leigong_log
insert into #temp2 select playername,itemname,MAX(ngSkillLevel) from cqsd_extendsgamelog_neice1.[dbo].[20140622_TS_Leigong_log] where itemname like'%强化%' group by playername,itemname order by MAX(ngSkillLevel)
20140622_TS_Leigong_log
2
20140618_TS_Leigong_log
insert into #temp2 select playername,itemname,MAX(ngSkillLevel) from cqsd_extendsgamelog_neice1.[dbo].[20140618_TS_Leigong_log] where itemname like'%强化%' group by playername,itemname order by MAX(ngSkillLevel)
20140618_TS_Leigong_log
3
20140614_TS_Leigong_log
insert into #temp2 select playername,itemname,MAX(ngSkillLevel) from cqsd_extendsgamelog_neice1.[dbo].[20140614_TS_Leigong_log] where itemname like'%强化%' group by playername,itemname order by MAX(ngSkillLevel)
20140614_TS_Leigong_log
4
20140619_TS_Leigong_log
insert into #temp2 select playername,itemname,MAX(ngSkillLevel) from cqsd_extendsgamelog_neice1.[dbo].[20140619_TS_Leigong_log] where itemname like'%强化%' group by playername,itemname order by MAX(ngSkillLevel)
20140619_TS_Leigong_log
5
20140623_TS_Leigong_log
insert into #temp2 select playername,itemname,MAX(ngSkillLevel) from cqsd_extendsgamelog_neice1.[dbo].[20140623_TS_Leigong_log] where itemname like'%强化%' group by playername,itemname order by MAX(ngSkillLevel)
20140623_TS_Leigong_log
6
20140615_TS_Leigong_log
insert into #temp2 select playername,itemname,MAX(ngSkillLevel) from cqsd_extendsgamelog_neice1.[dbo].[20140615_TS_Leigong_log] where itemname like'%强化%' group by playername,itemname order by MAX(ngSkillLevel)
20140615_TS_Leigong_log
7
20140620_TS_Leigong_log
insert into #temp2 select playername,itemname,MAX(ngSkillLevel) from cqsd_extendsgamelog_neice1.[dbo].[20140620_TS_Leigong_log] where itemname like'%强化%' group by playername,itemname order by MAX(ngSkillLevel)
20140620_TS_Leigong_log
8
20140616_TS_Leigong_log
insert into #temp2 select playername,itemname,MAX(ngSkillLevel) from cqsd_extendsgamelog_neice1.[dbo].[20140616_TS_Leigong_log] where itemname like'%强化%' group by playername,itemname order by MAX(ngSkillLevel)
20140616_TS_Leigong_log
9
20140624_TS_Leigong_log
insert into #temp2 select playername,itemname,MAX(ngSkillLevel) from cqsd_extendsgamelog_neice1.[dbo].[20140624_TS_Leigong_log] where itemname like'%强化%' group by playername,itemname order by MAX(ngSkillLevel)
20140624_TS_Leigong_log
10
20140621_TS_Leigong_log
insert into #temp2 select playername,itemname,MAX(ngSkillLevel) from cqsd_extendsgamelog_neice1.[dbo].[20140621_TS_Leigong_log] where itemname like'%强化%' group by playername,itemname order by MAX(ngSkillLevel)
20140621_TS_Leigong_log
11
20140617_TS_Leigong_log
insert into #temp2 select playername,itemname,MAX(ngSkillLevel) from cqsd_extendsgamelog_neice1.[dbo].[20140617_TS_Leigong_log] where itemname like'%强化%' group by playername,itemname order by MAX(ngSkillLevel)
20140617_TS_Leigong_log
12
20140625_TS_Leigong_log
insert into #temp2 select playername,itemname,MAX(ngSkillLevel) from cqsd_extendsgamelog_neice1.[dbo].[20140625_TS_Leigong_log] where itemname like'%强化%' group by playername,itemname order by MAX(ngSkillLevel)
20140625_TS_Leigong_log(12 行受影响)
2
cqsd_extendsgamelog_neice2(8 行受影响)
8
1
20140625_TS_Leigong_log
insert into #temp2 select playername,itemname,MAX(ngSkillLevel) from cqsd_extendsgamelog_neice2.[dbo].[20140625_TS_Leigong_log] where itemname like'%强化%' group by playername,itemname order by MAX(ngSkillLevel)
20140625_TS_Leigong_log
2
20140625_TS_Leigong_log
insert into #temp2 select playername,itemname,MAX(ngSkillLevel) from cqsd_extendsgamelog_neice2.[dbo].[20140625_TS_Leigong_log] where itemname like'%强化%' group by playername,itemname order by MAX(ngSkillLevel)
20140625_TS_Leigong_log
3
20140625_TS_Leigong_log
insert into #temp2 select playername,itemname,MAX(ngSkillLevel) from cqsd_extendsgamelog_neice2.[dbo].[20140625_TS_Leigong_log] where itemname like'%强化%' group by playername,itemname order by MAX(ngSkillLevel)
20140625_TS_Leigong_log
4
20140625_TS_Leigong_log
insert into #temp2 select playername,itemname,MAX(ngSkillLevel) from cqsd_extendsgamelog_neice2.[dbo].[20140625_TS_Leigong_log] where itemname like'%强化%' group by playername,itemname order by MAX(ngSkillLevel)
20140625_TS_Leigong_log
5
20140625_TS_Leigong_log
insert into #temp2 select playername,itemname,MAX(ngSkillLevel) from cqsd_extendsgamelog_neice2.[dbo].[20140625_TS_Leigong_log] where itemname like'%强化%' group by playername,itemname order by MAX(ngSkillLevel)
20140625_TS_Leigong_log
6
20140625_TS_Leigong_log
insert into #temp2 select playername,itemname,MAX(ngSkillLevel) from cqsd_extendsgamelog_neice2.[dbo].[20140625_TS_Leigong_log] where itemname like'%强化%' group by playername,itemname order by MAX(ngSkillLevel)
20140625_TS_Leigong_log
7
20140625_TS_Leigong_log
insert into #temp2 select playername,itemname,MAX(ngSkillLevel) from cqsd_extendsgamelog_neice2.[dbo].[20140625_TS_Leigong_log] where itemname like'%强化%' group by playername,itemname order by MAX(ngSkillLevel)
20140625_TS_Leigong_log
8
20140625_TS_Leigong_log
insert into #temp2 select playername,itemname,MAX(ngSkillLevel) from cqsd_extendsgamelog_neice2.[dbo].[20140625_TS_Leigong_log] where itemname like'%强化%' group by playername,itemname order by MAX(ngSkillLevel)
20140625_TS_Leigong_log(8 行受影响)
3
cqsd_extendsgamelog_neice3(5 行受影响)
5
1
20140625_TS_Leigong_log
insert into #temp2 select playername,itemname,MAX(ngSkillLevel) from cqsd_extendsgamelog_neice3.[dbo].[20140625_TS_Leigong_log] where itemname like'%强化%' group by playername,itemname order by MAX(ngSkillLevel)
20140625_TS_Leigong_log
2
20140625_TS_Leigong_log
insert into #temp2 select playername,itemname,MAX(ngSkillLevel) from cqsd_extendsgamelog_neice3.[dbo].[20140625_TS_Leigong_log] where itemname like'%强化%' group by playername,itemname order by MAX(ngSkillLevel)
20140625_TS_Leigong_log
3
20140625_TS_Leigong_log
insert into #temp2 select playername,itemname,MAX(ngSkillLevel) from cqsd_extendsgamelog_neice3.[dbo].[20140625_TS_Leigong_log] where itemname like'%强化%' group by playername,itemname order by MAX(ngSkillLevel)
20140625_TS_Leigong_log
4
20140625_TS_Leigong_log
insert into #temp2 select playername,itemname,MAX(ngSkillLevel) from cqsd_extendsgamelog_neice3.[dbo].[20140625_TS_Leigong_log] where itemname like'%强化%' group by playername,itemname order by MAX(ngSkillLevel)
20140625_TS_Leigong_log
5
20140625_TS_Leigong_log
insert into #temp2 select playername,itemname,MAX(ngSkillLevel) from cqsd_extendsgamelog_neice3.[dbo].[20140625_TS_Leigong_log] where itemname like'%强化%' group by playername,itemname order by MAX(ngSkillLevel)
20140625_TS_Leigong_log(5 行受影响)
4
cqsd_extendsgamelog0(0 行受影响)
0(0 行受影响)
5
cqsd_extendsgamelog1(125 行受影响)语句红色的地方出现了问题,下面打印的表都是第一个循环结束后的最后一个表名20140625_TS_Leigong_log,麻烦看看问题在哪里?
where name like''%TS_Leigong_log%'''
这个地方是不是少了个单引号 like 后面
where name like''%TS_Leigong_log%'''
这个地方是不是少了个单引号 like 后面哦 没有 眼神不好
这个结果是什么