有三个表,把它们联合后汇总,我要根据用户的选择来确定汇总的分类字段、汇总字段、条件,请教!我的设想是:
1.对用户的选择的汇总的分类字段、汇总字段、条件进行判断并生成相应的字符串。
2.根据分析的字符串在存储过程中建一个视图。
3.对视图进行汇总。
4.删除视图请问这种方法的效率怎么样,会不会很慢,有没有其它好一点的方法?谢谢
1.对用户的选择的汇总的分类字段、汇总字段、条件进行判断并生成相应的字符串。
2.根据分析的字符串在存储过程中建一个视图。
3.对视图进行汇总。
4.删除视图请问这种方法的效率怎么样,会不会很慢,有没有其它好一点的方法?谢谢
动态sql语句基本语法
1 :普通SQL语句可以用Exec执行 eg: Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N 2:字段名,表名,数据库名之类作为变量时,必须用动态SQL eg:
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- 错误,不会提示错误,但结果为固定值FiledName,并非所要。
Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格 当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = 'FiledName' --设置字段名 declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确 3. 输出参数
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中? declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num
insert into t1(code) select 'a1'
insert into t1(code) select 'a2'
insert into t1(code) select 'a3'create table t2(id int identity(1,1),t1_id int,name varchar(10))
insert into t2(t1_id,name) select 1,'b1'
insert into t2(t1_id,name) select 2,'b2'
insert into t2(t1_id,name) select 2,'b3'
insert into t2(t1_id,name) select 1,'b4'
insert into t2(t1_id,name) select 1,'b5'
insert into t2(t1_id,name) select 2,'b6'create table t3(id int identity(1,1),t2_id int,num int)
insert into t3(t2_id,num) select 1,10
insert into t3(t2_id,num) select 2,10
insert into t3(t2_id,num) select 3,10
insert into t3(t2_id,num) select 2,10
insert into t3(t2_id,num) select 3,10
insert into t3(t2_id,num) select 4,10
insert into t3(t2_id,num) select 6,10
insert into t3(t2_id,num) select 4,10
insert into t3(t2_id,num) select 5,10
gocreate procedure sp_test(@group varchar(200),@gather varchar(200),@condition varchar(200))
as
begin
if exists(select 1 from sysobjects where name='v_name' and xtype='v')
drop view v_name
declare @sql varchar(8000)
set @sql='create view v_name as select '
+@group+','+@gather
+' from (select a.id,a.code,b.name,c.num from t1 a,t2 b,t3 c where a.id=b.t1_id and b.id=c.t2_id) t where '
+@condition+' group by '+@group
exec(@sql)
select * from v_name
if exists(select 1 from sysobjects where name='v_name' and xtype='v')
drop view v_name
end
goexec sp_test 'id,code,name','sum(num) as num','id=1'
go/*
id code name num
----------- ---------- ---------- -----------
1 a1 b1 10
1 a1 b4 20
1 a1 b5 10
*/drop procedure sp_test
drop table t1,t2,t3
go
select ta.sid,ta.sbrand as 品牌,ta.sDepartment as 部门,ta.sDepartmentArea as 区域,ta.sTeminalShop as 卖场,ta.sCostAttribute AS 属性,ta.sProductCategory as 种类,ta.sApprovedFactoryMoney as 厂家批准金额,ta.cMoney as 公司批准金额,tb.sMoney as 实际使用,tc.提交金额,tc.报结金额,(tc.报结金额-isnull(tb.sMoney,0)) as 结余
from cost_stock as ta
left outer join
(select ssid,sum(sMoney) as sMoney from cost_sales group by ssid) as tb
on ta.sid=tb.ssid
left join
(select eSid,sum(eToFactoryMoney) as 提交金额,sum(eFinalToMoney) as 报结金额 from cost_end group by esid) as tc
on ta.sid=tc.esid
where '+@vCondition+')'×××××——————————————————————××××××
我使用了如以上的语句,但出错,提示不能转换为int