最近看游标,看的头都大了,还是没看明白,希望各位大大教教俺!最好是能给俺解释下每段代码的含义,谢谢了!假设某SQL数据库下存在以下各表:
Shop006、Shop007......ShopN:N>80
各表结构如下:
商品编码:varchar(12)
商品条码:varchar(18)
进货数量:varchar(225)1.现在想统计所有Shop表中的进货数量,如果是用游标的话,该如何写代码。
2.如果不使用游标,该如何实现此查询
Shop006、Shop007......ShopN:N>80
各表结构如下:
商品编码:varchar(12)
商品条码:varchar(18)
进货数量:varchar(225)1.现在想统计所有Shop表中的进货数量,如果是用游标的话,该如何写代码。
2.如果不使用游标,该如何实现此查询
declare @name varchar(100),@sql varchar(1000)declare cur cursor for
select name from sysobjects where patindex('Shop[0-9][0-9][0-9]',name)>0
order by name
open cur
fetch from cur into @name
while @@fetch_status=0
begin
set @sql='select '''+quotename(@name)+''' tbname,sum(进货数量) from '+quotename(@name)
insert # exec(@sql)
fetch from cur into @name
end
close cur
deallocate curselect * from #
drop table #
create table Shop006(商品编码 varchar(12),商品条码 varchar(18),进货数量 varchar(225))
create table Shop007(商品编码 varchar(12),商品条码 varchar(18),进货数量 varchar(225))
create table Shop008(商品编码 varchar(12),商品条码 varchar(18),进货数量 varchar(225))insert into shop006 select 'AA','4890000000001','25'
insert into shop006 select 'BB','4890000000002','30'
insert into shop007 select 'CC','4890000000003','35'
insert into shop008 select 'DD','4890000000004','15'
insert into shop008 select 'EE','4890000000005','20'-- 前提是假设第一个表是 Shop006 (可以修改), 另外一个不明白你的进货数量为什么是字符型的。declare @sql varchar(8000)
set @sql = ''select @sql = @sql + ' union select ''' + name + ''',[商品编码],[商品条码],cast([进货数量] as int) from ' + name
from sysobjects where name like 'shop%' and name > 'Shop006'set @sql = 'select ''Shop006'' as [店名],[商品编码],[商品条码],cast([进货数量] as int) [进货数量] from shop006 ' + @sqlexec(@sql)-- 结果
/*
店名 商品编码 商品条码 进货数量
------- ------------ ------------------ -----------
Shop006 AA 4890000000001 25
Shop006 BB 4890000000002 30
Shop007 CC 4890000000003 35
Shop008 DD 4890000000004 15
Shop008 EE 4890000000005 20(5 行受影响)
*/
declare @name varchar(100),@sql varchar(1000)declare cur cursor for
select name from sysobjects where patindex('Shop[0-9][0-9][0-9]',name)>0
order by name
open cur
fetch from cur into @name
while @@fetch_status=0
begin
set @sql='select '''+quotename(@name)+''' tbname,sum(convert(decimal(18,4),进货数量)) from '+quotename(@name)
insert into # exec(@sql)
fetch from cur into @name
end
close cur
deallocate curselect * from #
drop table #
游标是一条一条地读出for后的那条语句的记录(如select name from sysobjects where patindex('Shop[0-9][0-9][0-9]',name)>0)),取出有用的字段放到变量里,再用这些变量做其它操作。