34,587
社区成员
发帖
与我相关
我的任务
分享
回楼上.
动态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
--两种都写了,搞定,收工.
create table Unit(UnitID int,UnitName varchar(20))
insert into unit values(1 , '中国第一集团')
insert into unit values(2 , '中国第二集团')
create table Bill(BillId int,UnitId int,Date datetime)
insert into Bill values(1 , 1 , '2007-01-01')
insert into Bill values(2 , 1 , '2007-01-02')
insert into Bill values(3 , 2 , '2007-02-01')
insert into Bill values(4 , 2 , '2007-02-02')
create table BillVsUnit(id int,BillId int,ItemId int,money int)
insert into BillVsUnit values(1 , 1 , 1 , 100)
insert into BillVsUnit values(2 , 2 , 2 , 200)
insert into BillVsUnit values(3 , 3 , 3 , 300)
insert into BillVsUnit values(4 , 4 , 4 , 400)
create table Item(id int,ItemName varchar(10),FundClass varchar(10),IncomeType varchar(10))
insert into item values(1 , '一' , '一般' , '类型1')
insert into item values(2 , '二' , '专户' , '类型2')
insert into item values(3 , '三' , '一般' , '类型3')
insert into item values(4 , '四' , '专户' , '类型1')
go
--静态SQL,指d.FundClass , d.IncomeType 值固定.
select a.UnitName 单位名称,a.UnitID 单位编码 ,d.itemname,d.id 项目编码,
sum(case when d.IncomeType = '类型1' and d.FundClass = '一般' then c.money else 0 end) '类型1_一般',
sum(case when d.IncomeType = '类型1' and d.FundClass = '专户' then c.money else 0 end) '类型1_专户',
sum(case when d.IncomeType = '类型2' and d.FundClass = '一般' then c.money else 0 end) '类型1_一般',
sum(case when d.IncomeType = '类型2' and d.FundClass = '专户' then c.money else 0 end) '类型1_专户',
sum(case when d.IncomeType = '类型3' and d.FundClass = '一般' then c.money else 0 end) '类型1_一般',
sum(case when d.IncomeType = '类型3' and d.FundClass = '专户' then c.money else 0 end) '类型1_专户'
from unit a,bill b , billvsunit c,item d
where a.unitid = b.unitid and b.billid = c.billid and c.itemid = d.id
group by a.UnitName,a.UnitID,d.itemname,d.id
order by a.UnitName,a.UnitID,d.itemname,d.id
/*
单位名称 单位编码 itemname 项目编码 类型1_一般 类型1_专户 类型1_一般 类型1_专户 类型1_一般 类型1_专户
-------------------- ----------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
中国第二集团 2 三 3 0 0 0 0 300 0
中国第二集团 2 四 4 0 400 0 0 0 0
中国第一集团 1 二 2 0 0 0 200 0 0
中国第一集团 1 一 1 100 0 0 0 0 0
(所影响的行数为 4 行)
*/
--动态SQL,指d.FundClass , d.IncomeType 值不固定.
declare @sql varchar(8000)
set @sql = 'select a.UnitName 单位名称,a.UnitID 单位编码 ,d.itemname,d.id 项目编码'
select @sql = @sql + ' , sum(case IncomeType +' + '''_''' + '+ FundClass when ''' + newitem + ''' then money else 0 end) [' + newitem + ']'
from (select distinct IncomeType + '_' + FundClass newitem from item) as t
set @sql = @sql + ' from unit a,bill b , billvsunit c,item d
where a.unitid = b.unitid and b.billid = c.billid and c.itemid = d.id
group by a.UnitName,a.UnitID,d.itemname,d.id
order by a.UnitName,a.UnitID,d.itemname,d.id'
exec(@sql)
/*
单位名称 单位编码 itemname 项目编码 类型1_一般 类型1_专户 类型2_专户 类型3_一般
-------------------- ----------- ---------- ----------- ----------- ----------- ----------- -----------
中国第二集团 2 三 3 0 0 0 300
中国第二集团 2 四 4 0 400 0 0
中国第一集团 1 二 2 0 0 200 0
中国第一集团 1 一 1 100 0 0 0
*/
--drop table unit,bill,billvsunit,item
--上面类型和用户写反了.
create table Unit(UnitID int,UnitName varchar(20))
insert into unit values(1 , '中国第一集团')
insert into unit values(2 , '中国第二集团')
create table Bill(BillId int,UnitId int,Date datetime)
insert into Bill values(1 , 1 , '2007-01-01')
insert into Bill values(2 , 1 , '2007-01-02')
insert into Bill values(3 , 2 , '2007-02-01')
insert into Bill values(4 , 2 , '2007-02-02')
create table BillVsUnit(id int,BillId int,ItemId int,money int)
insert into BillVsUnit values(1 , 1 , 1 , 100)
insert into BillVsUnit values(2 , 2 , 2 , 200)
insert into BillVsUnit values(3 , 3 , 3 , 300)
insert into BillVsUnit values(4 , 4 , 4 , 400)
create table Item(id int,ItemName varchar(10),FundClass varchar(10),IncomeType varchar(10))
insert into item values(1 , '一' , '一般' , '类型1')
insert into item values(2 , '二' , '专户' , '类型2')
insert into item values(3 , '三' , '一般' , '类型3')
insert into item values(4 , '四' , '专户' , '类型1')
go
--静态SQL,指d.FundClass , d.IncomeType 值固定.
select a.UnitName 单位名称,a.UnitID 单位编码 ,d.itemname,d.id 项目编码,
sum(case when d.IncomeType = '类型1' and d.FundClass = '一般' then c.money else 0 end) '类型1_一般',
sum(case when d.IncomeType = '类型1' and d.FundClass = '专户' then c.money else 0 end) '类型1_专户',
sum(case when d.IncomeType = '类型2' and d.FundClass = '一般' then c.money else 0 end) '类型1_一般',
sum(case when d.IncomeType = '类型2' and d.FundClass = '专户' then c.money else 0 end) '类型1_专户',
sum(case when d.IncomeType = '类型3' and d.FundClass = '一般' then c.money else 0 end) '类型1_一般',
sum(case when d.IncomeType = '类型3' and d.FundClass = '专户' then c.money else 0 end) '类型1_专户'
from unit a,bill b , billvsunit c,item d
where a.unitid = b.unitid and b.billid = c.billid and c.itemid = d.id
group by a.UnitName,a.UnitID,d.itemname,d.id
order by a.UnitName,a.UnitID,d.itemname,d.id
/*
单位名称 单位编码 itemname 项目编码 类型1_一般 类型1_专户 类型1_一般 类型1_专户 类型1_一般 类型1_专户
-------------------- ----------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
中国第二集团 2 三 3 0 0 0 0 300 0
中国第二集团 2 四 4 0 400 0 0 0 0
中国第一集团 1 二 2 0 0 0 200 0 0
中国第一集团 1 一 1 100 0 0 0 0 0
(所影响的行数为 4 行)
*/
--动态SQL,指d.FundClass , d.IncomeType 值不固定.
declare @sql varchar(8000)
set @sql = 'select a.UnitName 单位名称,a.UnitID 单位编码 ,d.itemname,d.id 项目编码'
select @sql = @sql + ' , sum(case IncomeType + FundClass when ''' + newitem + ''' then money else 0 end) [' + newitem + ']'
from (select distinct IncomeType + FundClass newitem from item) as t
set @sql = @sql + ' from unit a,bill b , billvsunit c,item d
where a.unitid = b.unitid and b.billid = c.billid and c.itemid = d.id
group by a.UnitName,a.UnitID,d.itemname,d.id
order by a.UnitName,a.UnitID,d.itemname,d.id'
exec(@sql)
/*
单位名称 单位编码 itemname 项目编码 类型1一般 类型1专户 类型2专户 类型3一般
-------------------- ----------- ---------- ----------- ----------- ----------- ----------- -----------
中国第二集团 2 三 3 0 0 0 300
中国第二集团 2 四 4 0 400 0 0
中国第一集团 1 二 2 0 0 200 0
中国第一集团 1 一 1 100 0 0 0
*/
--drop table unit,bill,billvsunit,item
create table Unit(UnitID int,UnitName varchar(20))
insert into unit values(1 , '中国第一集团')
insert into unit values(2 , '中国第二集团')
create table Bill(BillId int,UnitId int,Date datetime)
insert into Bill values(1 , 1 , '2007-01-01')
insert into Bill values(2 , 1 , '2007-01-02')
insert into Bill values(3 , 2 , '2007-02-01')
insert into Bill values(4 , 2 , '2007-02-02')
create table BillVsUnit(id int,BillId int,ItemId int,money int)
insert into BillVsUnit values(1 , 1 , 1 , 100)
insert into BillVsUnit values(2 , 2 , 2 , 200)
insert into BillVsUnit values(3 , 3 , 3 , 300)
insert into BillVsUnit values(4 , 4 , 4 , 400)
create table Item(id int,ItemName varchar(10),FundClass varchar(10),IncomeType varchar(10))
insert into item values(1 , '一' , '一般' , '类型1')
insert into item values(2 , '二' , '专户' , '类型2')
insert into item values(3 , '三' , '一般' , '类型3')
insert into item values(4 , '四' , '专户' , '类型1')
go
--静态SQL,指d.FundClass , d.IncomeType 值固定.
select a.UnitName 单位名称,a.UnitID 单位编码 ,d.itemname,d.id 项目编码,
sum(case when d.FundClass = '一般' and d.IncomeType = '类型1' then c.money else 0 end) '一般_类型1',
sum(case when d.FundClass = '一般' and d.IncomeType = '类型2' then c.money else 0 end) '一般_类型2',
sum(case when d.FundClass = '一般' and d.IncomeType = '类型3' then c.money else 0 end) '一般_类型3',
sum(case when d.FundClass = '专户' and d.IncomeType = '类型1' then c.money else 0 end) '专户_类型1',
sum(case when d.FundClass = '专户' and d.IncomeType = '类型2' then c.money else 0 end) '专户_类型2',
sum(case when d.FundClass = '专户' and d.IncomeType = '类型3' then c.money else 0 end) '专户_类型3'
from unit a,bill b , billvsunit c,item d
where a.unitid = b.unitid and b.billid = c.billid and c.itemid = d.id
group by a.UnitName,a.UnitID,d.itemname,d.id
order by a.UnitName,a.UnitID,d.itemname,d.id
/*
单位名称 单位编码 itemname 项目编码 一般_类型1 一般_类型2 一般_类型3 专户_类型1 专户_类型2 专户_类型3
-------------------- ----------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
中国第二集团 2 三 3 0 0 300 0 0 0
中国第二集团 2 四 4 0 0 0 400 0 0
中国第一集团 1 二 2 0 0 0 0 200 0
中国第一集团 1 一 1 100 0 0 0 0 0
(所影响的行数为 4 行)
*/
--动态SQL,指d.FundClass , d.IncomeType 值不固定.
declare @sql varchar(8000)
set @sql = 'select a.UnitName 单位名称,a.UnitID 单位编码 ,d.itemname,d.id 项目编码'
select @sql = @sql + ' , sum(case FundClass + IncomeType when ''' + newitem + ''' then money else 0 end) [' + newitem + ']'
from (select distinct FundClass + IncomeType newitem from item) as t
set @sql = @sql + ' from unit a,bill b , billvsunit c,item d
where a.unitid = b.unitid and b.billid = c.billid and c.itemid = d.id
group by a.UnitName,a.UnitID,d.itemname,d.id
order by a.UnitName,a.UnitID,d.itemname,d.id'
exec(@sql)
/*
单位名称 单位编码 itemname 项目编码 一般类型1 一般类型3 专户类型1 专户类型2
-------------------- ----------- ---------- ----------- ----------- ----------- ----------- -----------
中国第二集团 2 三 3 0 300 0 0
中国第二集团 2 四 4 0 0 400 0
中国第一集团 1 二 2 0 0 0 200
中国第一集团 1 一 1 100 0 0 0
*/
drop table unit,bill,billvsunit,item