上CSDN的第一件事就是接触到动态SQL语句,头一次因为没看明白过来就匆匆回贴而闹笑话,
第二次是给高升的超长句子给愣住了……
每每想起这些糟事,不由得脸红耳热~~尽管以往的问题已经弄明白了,但对于一些复杂的动态语句还是没能够看明白。
唉,哪能不懂装懂呢?!恳请请各位发发善心帮帮我吧。问:
1,要学习动态语句,需要从哪儿找到有关的学习资料?
2,像以下的例子,如何才能把(一)转换成(二)的动态写法呢?需要解释详细的思维转化过程。(一)
--测试数据: #T
create table #T (ID varchar(5),DATE datetime,BZ tinyint)
insert into #T
select 'temp1','2007-12-20',1 union all
select 'temp3','2007-12-20',1 union all
select 'temp1','2007-12-21',2 union all
select 'temp2','2007-12-21',2 union all
select 'temp3','2007-12-21',3 union all
select 'temp1','2007-12-22',3 union all
select 'temp2','2007-12-22',4
select name=ID
,[2007-12-20]=max(case DATE when '2007-12-20' then BZ else 0 end)
,[2007-12-21]=max(case DATE when '2007-12-21' then BZ else 0 end)
,[2007-12-22]=max(case DATE when '2007-12-22' then BZ else 0 end)
from #T group by IDdrop table #T(二)--测试数据: #T
create table #T (ID varchar(5),DATE datetime,BZ tinyint)
insert into #T
select 'temp1','2007-12-20',1 union all
select 'temp3','2007-12-20',1 union all
select 'temp1','2007-12-21',2 union all
select 'temp2','2007-12-21',2 union all
select 'temp3','2007-12-21',3 union all
select 'temp1','2007-12-22',3 union all
select 'temp2','2007-12-22',4declare @sql varchar(8000)
select @sql = isnull(@sql+',','select name=ID,') -->为什么要用isnull函数呢?
+quotename(DATE)
+'=max(case DATE when '''+DATE+''' then BZ else 0 end)'
from (select distinct DATE=convert(varchar(10),DATE,120) from #T) a -->此句是怎样转过来的?set @sql=@sql+' from #T group by ID'exec (@sql)drop table #T
第二次是给高升的超长句子给愣住了……
每每想起这些糟事,不由得脸红耳热~~尽管以往的问题已经弄明白了,但对于一些复杂的动态语句还是没能够看明白。
唉,哪能不懂装懂呢?!恳请请各位发发善心帮帮我吧。问:
1,要学习动态语句,需要从哪儿找到有关的学习资料?
2,像以下的例子,如何才能把(一)转换成(二)的动态写法呢?需要解释详细的思维转化过程。(一)
--测试数据: #T
create table #T (ID varchar(5),DATE datetime,BZ tinyint)
insert into #T
select 'temp1','2007-12-20',1 union all
select 'temp3','2007-12-20',1 union all
select 'temp1','2007-12-21',2 union all
select 'temp2','2007-12-21',2 union all
select 'temp3','2007-12-21',3 union all
select 'temp1','2007-12-22',3 union all
select 'temp2','2007-12-22',4
select name=ID
,[2007-12-20]=max(case DATE when '2007-12-20' then BZ else 0 end)
,[2007-12-21]=max(case DATE when '2007-12-21' then BZ else 0 end)
,[2007-12-22]=max(case DATE when '2007-12-22' then BZ else 0 end)
from #T group by IDdrop table #T(二)--测试数据: #T
create table #T (ID varchar(5),DATE datetime,BZ tinyint)
insert into #T
select 'temp1','2007-12-20',1 union all
select 'temp3','2007-12-20',1 union all
select 'temp1','2007-12-21',2 union all
select 'temp2','2007-12-21',2 union all
select 'temp3','2007-12-21',3 union all
select 'temp1','2007-12-22',3 union all
select 'temp2','2007-12-22',4declare @sql varchar(8000)
select @sql = isnull(@sql+',','select name=ID,') -->为什么要用isnull函数呢?
+quotename(DATE)
+'=max(case DATE when '''+DATE+''' then BZ else 0 end)'
from (select distinct DATE=convert(varchar(10),DATE,120) from #T) a -->此句是怎样转过来的?set @sql=@sql+' from #T group by ID'exec (@sql)drop table #T
解决方案 »
- sql 查询问题
- 求SQL语句
- 如果快速检索内容以逗号隔开的字段?
- where cid=100 and name like '%abc%' 与 where name like '%abc%' and cid=100,效率有区别吗?
- 如何接触SQL Server2005 sa用户的登陆锁定
- 问一个按时间排序的触发器的问题
- 控制可查看信息的人员,不用in,数据结构如何设计
- 请教Sqlserver2000建索引问题,谢谢
- 求级联更新触发器
- 简单的SQL语句
- 怎么使用SQLDMO还原数据库并把它移动到 C:\Program Files\Microsoft SQL Server\MSSQL\Data 目录下
- 请问: sql 2005 的几个服务分别是做什么用的?
1,要学习动态语句,需要从哪儿找到有关的学习资料?
2,像以下的例子,如何才能把(一)转换成(二)的动态写法呢?需要解释详细的思维转化过程。---
基本的SQL语句主要你能大体构造出最终想要的SQL语句,
create table #T (ID varchar(5),DATE datetime,BZ tinyint)
insert into #T
select 'temp1','2007-12-20',1 union all
select 'temp3','2007-12-20',1 union all
select 'temp1','2007-12-21',2 union all
select 'temp2','2007-12-21',2 union all
select 'temp3','2007-12-21',3 union all
select 'temp1','2007-12-22',3 union all
select 'temp2','2007-12-22',4declare @sql varchar(8000)
select @sql = isnull(@sql+',','select name=ID,') -->为什么要用isnull函数呢?
+quotename(DATE)
+'=max(case DATE when '''+DATE+''' then BZ else 0 end)'
from (select distinct DATE=convert(varchar(10),DATE,120) from #T) a -->此句是怎样转过来的?set @sql=@sql+' from #T group by ID'print (@sql)drop table #T这样你会发现最后的SQL就是第一个的语句
没有初始化的变量都为null,加任何字符都为空
-->此句是怎样转过来的?
去掉重复确定唯一..declare @sql varchar(8000)
select @sql = isnull(@sql+',','select name=ID,') -->为什么要用isnull函数呢?
+quotename(DATE)
+'=max(case DATE when '''+DATE+''' then BZ else 0 end)'
from (select distinct DATE=convert(varchar(10),DATE,120) from #T) a -->此句是怎样转过来的?set @sql=@sql+' from #T group by ID'print @sql--这里显示生成的语句
create table t1 (Materialid int,Stockid int,Qty int)
insert t1 select 101,501 , 100
insert t1 select 101 , 502 , 120
insert t1 select 101 , 503 , 130
insert t1 select 102 , 601 , 160
insert t1 select 102 , 602 , 200
create table t2 (Fid int,stockid int,stockname varchar(10))
insert t2 select 1,501,'材料仓A'
insert t2 select 2,502,'材料仓B'
insert t2 select 3,503,'材料仓c'
insert t2 select 4,601,'成品仓A'
insert t2 select 5,602,'成品仓B'
create table t3 (Fid int,Materialid int,Materialname varchar(10))
insert t3 select 1,101, '电阻'
insert t3 select 2,102,'打印机 '
godeclare @s nvarchar(4000)
set @s='select [Materialname]=isnull(t3.Materialname,''合计'')'
select @s=@s+','+quotename(stockname)+'=sum(case when Stockid='+quotename(stockid,'''')+' then Qty else 0 end)'
from T2
group by stockname,stockid
set @s=@s+',[数量合计]=sum(Qty) from T1 join T3 on t1.Materialid=t3.Materialid group by t3.Materialname with rollup'
exec(@s)/*
Materialname 材料仓A 材料仓B 材料仓c 成品仓A 成品仓B 数量合计
------------ ----------- ----------- ----------- ----------- ----------- -----------
打印机 0 0 0 160 200 360
电阻 100 120 130 0 0 350
合计 100 120 130 160 200 710
*/
--生成的语句如下:
select
[Materialname]=isnull(t3.Materialname,'合计'),
[材料仓A]=sum(case when Stockid='501' then Qty else 0 end),
[材料仓B]=sum(case when Stockid='502' then Qty else 0 end),
[材料仓c]=sum(case when Stockid='503' then Qty else 0 end),
[成品仓A]=sum(case when Stockid='601' then Qty else 0 end),
[成品仓B]=sum(case when Stockid='602' then Qty else 0 end),
[数量合计]=sum(Qty)
from
T1
join
T3 on t1.Materialid=t3.Materialid
group by t3.Materialname with rollupgo
create table T( ID int,name nvarchar(10), url nvarchar(10) ,ver nvarchar(10))
insert T select 1, 'name1', 'url1', 'ver1'
insert T select 2, 'name2', 'url2', 'ver2'
insert T select 3, 'name3', 'url3', 'ver3'--drop table Tdeclare @s nvarchar(4000),@s2 nvarchar(4000),@s3 nvarchar(4000)
select @s='',@s2='',@s3=''
select @s=@s+','+quotename(ID)+'=max(case when ID='+rtrim(ID)+' then name end)',
@s2=@s2+','+quotename(ID)+'=max(case when ID='+rtrim(ID)+' then url end)',
@s3=@s3+','+quotename(ID)+'=max(case when ID='+rtrim(ID)+' then ver end)'
from T group by IDselect @s=stuff(@s,1,1,''), @s2=stuff(@s2,1,1,''), @s3=stuff(@s3,1,1,'')exec(' select '+@s+' from T union all select '+@s2+' from T union all select '+@s3+' from T')/*
1 2 3
---------- ---------- ----------
name1 name2 name3
url1 url2 url3
ver1 ver2 ver3
*/
godeclare @s nvarchar(4000),@s2 nvarchar(4000),@s3 nvarchar(4000),@s4 nvarchar(4000),@i int
select @s='',@s2='',@s3='',@i=1
select @s=@s+','+quotename(ID)+'=max(case when ID='+rtrim(ID)+' then name end)',
@s2=@s2+','+quotename(ID)+'=max(case when ID='+rtrim(ID)+' then url end)',
@s3=@s3+','+quotename(ID)+'=max(case when ID='+rtrim(ID)+' then ver end)',
@s4=isnull(@s4+' , ','select ')+' [COl'+rtrim(@i)+']= cast('+rtrim(ID)+' as nvarchar(20))',
@i=@i+1
from T group by IDselect @s=stuff(@s,1,1,''), @s2=stuff(@s2,1,1,''), @s3=stuff(@s3,1,1,'')exec(@s4+' union all select '+@s+' from T union all select '+@s2+' from T union all select '+@s3+' from T')/*
COl1 COl2 COl3
-------------------- -------------------- --------------------
1 2 3
name1 name2 name3
url1 url2 url3
ver1 ver2 ver3
*/create table T(学号 varchar(2),姓名 nvarchar(5),时间 datetime,实验结果 nvarchar(10))
insert T select '01','张三','2007-12-10','通过'
insert T select '01','张三','2007-12-11','通过'
insert T select '02','李四','2007-12-10','通过'
insert T select '03','王五','2007-12-11','失败'
insert T select '03','王五','2007-12-12','失败'
insert T select '03','王五','2007-12-13','通过' godeclare @i int,@s nvarchar(4000)
select @i=max(con) from (select con=count(1) from T group by 学号)TT
set @s=''
while @i!<1
select @s=',[实验'+rtrim(@i)+']=max(case when con='+rtrim(@i)+' then [实验结果] else '''' end)'+@s,@i=@i-1exec('select 学号,姓名'+@s+' from
(select *,con=(select count(1) from T where 学号=a.学号 and 时间!>a.时间 ) from T a)Tmp
group by 学号,姓名 order by 学号 ')
/*
学号 姓名 实验1 实验2 实验3
---- ----- ---------- ---------- ----------
01 张三 通过 通过
02 李四 通过
03 王五 失败 失败 通过
*/
--生成的语句如下:
select 学号,姓名,[实验1]=max(case when con=1 then [实验结果] else '' end),[实验2]=max(case when con=2 then [实验结果] else '' end),[实验3]=max(case when con=3 then [实验结果] else '' end) from
(select *,con=(select count(1) from T where 学号=a.学号 and 时间!>a.时间 ) from T a)Tmp
group by 学号,姓名 order by 学号
说法还是不够明白,把动态语句print出来,是一种从"复杂->简单"的方法,可我现在就没法从迈出从"简单->复杂"这一步,请给我一些逆向的提示.roy
解释得详细,较易理解, 对于为什么要使用isnull,这点明白了,但"去掉重复确定唯一"这句还是不明白,得再琢磨.
让我消化一下你提供的例子再提问.
看样子,要理解了你提供的例子才能把(一) ---> (二) 的转换弄明白.小梁,
从量变到质变? 这过程中是怎来的?
我还是没明白啊! 哈哈...
石林#黄果树
请把你的拼接方法一步步的写出来,教笨人得详细具体一点嘛!
:(
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
关键还是放在构造这个语句上
拜读,学习中!先说说 Roy 所给的例子,其中有的不能编译,我稍为修改了一下:-->测试数据
create table t1
(
Materialid int
,Stockid int
,Qty int
)
insert t1 select 101,501,100
insert t1 select 101,502,120
insert t1 select 101,503,130
insert t1 select 102,601,160
insert t1 select 102,602,200 create table t2
(
Fid int
,stockid int
,stockname varchar(10)
)
insert t2 select 1,501,'材料仓A'
insert t2 select 2,502,'材料仓B'
insert t2 select 3,503,'材料仓c'
insert t2 select 4,601,'成品仓A'
insert t2 select 5,602,'成品仓B'create table t3
(
Fid int
,Materialid int
,Materialname varchar(10)
)
insert t3 select 1,101,'电阻'
insert t3 select 2,102,'打印机'
godeclare @s nvarchar(4000)
set @s='select [Materialname]=isnull(t3.Materialname,''合计'')'
select @s=@s+','+quotename(stockname)+'=sum(case when Stockid='+quotename(stockid,'''')+' then Qty else 0 end)'
from T2
group by stockname,stockid
set @s=@s+',[数量合计]=sum(Qty) from T1 join T3 on t1.Materialid=t3.Materialid group by t3.Materialname with rollup'
exec(@s)DROP TABLE T1
DROP TABLE T2
DROP TABLE T3
/*
Materialname 材料仓A 材料仓B 材料仓c 成品仓A 成品仓B 数量合计
------------ ----------- ----------- ----------- ----------- ----------- -----------
打印机 0 0 0 160 200 360
电阻 100 120 130 0 0 350
合计 100 120 130 160 200 710
*/
/*==================================================================*/
--生成的语句如下:-->测试数据
create table t1
(
Materialid int
,Stockid int
,Qty int
)
insert t1 select 101,501,100
insert t1 select 101,502,120
insert t1 select 101,503,130
insert t1 select 102,601,160
insert t1 select 102,602,200 create table t2
(
Fid int
,stockid int
,stockname varchar(10)
)
insert t2 select 1,501,'材料仓A'
insert t2 select 2,502,'材料仓B'
insert t2 select 3,503,'材料仓c'
insert t2 select 4,601,'成品仓A'
insert t2 select 5,602,'成品仓B'create table t3
(
Fid int
,Materialid int
,Materialname varchar(10)
)
insert t3 select 1,101,'电阻'
insert t3 select 2,102,'打印机'
goselect * from t1
select * from t2
select * from t3
select
[Materialname]=isnull(t3.Materialname,'合计')
,[材料仓A]=sum(case when Stockid='501' then Qty else 0 end)
,[材料仓B]=sum(case when Stockid='502' then Qty else 0 end)
,[材料仓c]=sum(case when Stockid='503' then Qty else 0 end)
,[成品仓A]=sum(case when Stockid='601' then Qty else 0 end)
,[成品仓B]=sum(case when Stockid='602' then Qty else 0 end)
,[数量合计]=sum(Qty)
from
T1
join
T3 on t1.Materialid=t3.Materialid
group by t3.Materialname with rollupgoDROP TABLE T1
DROP TABLE T2
DROP TABLE T3/*==================================================================*/
create table T
(
ID int
,name nvarchar(10)
,url nvarchar(10)
,ver nvarchar(10)
)insert T select 1, 'name1', 'url1', 'ver1'
insert T select 2, 'name2', 'url2', 'ver2'
insert T select 3, 'name3', 'url3', 'ver3'select * from Tdeclare @s nvarchar(4000)
,@s2 nvarchar(4000)
,@s3 nvarchar(4000)set @s=''
set @s2=''
set @s3=''/*
select @s=@s+','+quotename(ID)+'=max(case when ID='+rtrim(ID)+' then name end)',
@s2=@s2+','+quotename(ID)+'=max(case when ID='+rtrim(ID)+' then url end)',
@s3=@s3+','+quotename(ID)+'=max(case when ID='+rtrim(ID)+' then ver end)'
from T group by ID
*/select @s= @s
+','
+quotename(ID)
+'=max(case when ID='
+rtrim(ID)
+' then name end)'
,@s2= @s2
+','
+quotename(ID)
+'=max(case when ID='
+rtrim(ID)
+' then url end)'
,@s3= @s3
+','
+quotename(ID)
+'=max(case when ID='
+rtrim(ID)
+' then ver end)'
from T group by ID
select @s=stuff(@s,1,1,''), @s2=stuff(@s2,1,1,''), @s3=stuff(@s3,1,1,'')exec(' select '+@s+' from T union all select '+@s2+' from T union all select '+@s3+' from T')drop table T
/*
1 2 3
---------- ---------- ----------
name1 name2 name3
url1 url2 url3
ver1 ver2 ver3
*/
go/*==================================================================*/
create table T
(
ID int
,name nvarchar(10)
,url nvarchar(10)
,ver nvarchar(10)
)insert T select 1, 'name1', 'url1', 'ver1'
insert T select 2, 'name2', 'url2', 'ver2'
insert T select 3, 'name3', 'url3', 'ver3'
declare @s nvarchar(4000)
,@s2 nvarchar(4000)
,@s3 nvarchar(4000)
,@s4 nvarchar(4000)
,@i intset @s=''
set @s2=''
set @s3=''
set @i=1select @s = @s
+','
+quotename(ID)
+'=max(case when ID='
+rtrim(ID)
+' then name end)'
,@s2 = @s2
+','
+quotename(ID)
+'=max(case when ID='
+rtrim(ID)
+' then url end)'
,@s3 = @s3
+','
+quotename(ID)
+'=max(case when ID='
+rtrim(ID)
+' then ver end)'
,@s4 = isnull(@s4+' , ','select ')
+' [COl'
+rtrim(@i)
+']= cast('+rtrim(ID)
+' as nvarchar(20))'
,@i=@i+1
from T group by IDselect @s=stuff(@s,1,1,''), @s2=stuff(@s2,1,1,''), @s3=stuff(@s3,1,1,'')exec(@s4+' union all select '+@s+' from T union all select '+@s2+' from T union all select '+@s3+' from T')drop table T
/*
COl1 COl2 COl3
-------------------- -------------------- --------------------
1 2 3
name1 name2 name3
url1 url2 url3
ver1 ver2 ver3
*/create table T
(
学号 varchar(2)
,姓名 nvarchar(5)
,时间 datetime
,实验结果 nvarchar(10)
)
insert T select '01','张三','2007-12-10','通过'
insert T select '01','张三','2007-12-11','通过'
insert T select '02','李四','2007-12-10','通过'
insert T select '03','王五','2007-12-11','失败'
insert T select '03','王五','2007-12-12','失败'
insert T select '03','王五','2007-12-13','通过' goSELECT * FROM Tdeclare @i int
,@s nvarchar(4000)select @i=max(con)
from (select con=count(1) from T group by 学号)TT
set @s=''
while @i!<1
select @s=',[实验'+rtrim(@i)+']=max(case when con='+rtrim(@i)+' then [实验结果] else '''' end)'+@s,@i=@i-1exec('select 学号,姓名'+@s+' from
(select *,con=(select count(1) from T where 学号=a.学号 and 时间!>a.时间 ) from T a)Tmp
group by 学号,姓名 order by 学号 ')
/*
学号 姓名 实验1 实验2 实验3
---- ----- ---------- ---------- ----------
01 张三 通过 通过
02 李四 通过
03 王五 失败 失败 通过
*/
--生成的语句如下:select 学号
,姓名
,[实验1]=max(case when con=1 then [实验结果] else '' end)
,[实验2]=max(case when con=2 then [实验结果] else '' end)
,[实验3]=max(case when con=3 then [实验结果] else '' end)
from (select *,con=(select count(1) from T where 学号=a.学号 and 时间!>a.时间 ) from T a)Tmp
group by 学号,姓名 order by 学号DROP TABLE T似乎一时间还是不能解开我的疑惑,脑子有点发涨了。
insert into #T
select 'temp1','2007-12-20',1 union all
select 'temp3','2007-12-20',1 union all
select 'temp1','2007-12-21',2 union all
select 'temp2','2007-12-21',2 union all
select 'temp3','2007-12-21',3 union all
select 'temp1','2007-12-22',3 union all
select 'temp2','2007-12-22',4select * from #Tdeclare @sql varchar(8000)
select @sql = isnull(@sql+',','select name=ID,') -->@sql=null,为什么还要@sql+','?
+quotename(DATE)
+'=max(case DATE when '''+DATE+''' then BZ else 0 end)'
from (select distinct DATE=convert(varchar(10),DATE,120) from #T) a -->此句是应划到那个句子上?set @sql=@sql+' from #T group by ID'exec (@sql)drop table #T
再贴一次吧:
create table #T (ID varchar(5),DATE datetime,BZ tinyint)
insert into #T
select 'temp1','2007-12-20',1 union all
select 'temp3','2007-12-20',1 union all
select 'temp1','2007-12-21',2 union all
select 'temp2','2007-12-21',2 union all
select 'temp3','2007-12-21',3 union all
select 'temp1','2007-12-22',3 union all
select 'temp2','2007-12-22',4select * from #Tdeclare @sql varchar(8000)
select @sql = isnull(@sql+',','select name=ID,') -->@sql=null,为什么还要@sql+','?
+quotename(DATE)
+'=max(case DATE when '''+DATE+''' then BZ else 0 end)'
from (select distinct DATE=convert(varchar(10),DATE,120) from #T) a -->此句是应划到那个句子上?set @sql=@sql+' from #T group by ID'exec (@sql)drop table #T
你要是嫌sql太复杂可以从简单的语句写起,要经常使用print 子句来查看在哪错了
select @sql = isnull(@sql+',','select name=ID,'),那么@sql='select name=ID,'
当然你可不要这样写,可以先给定初值.
set @s='1,2,3,4,5'
希望在tb中插入5行,id列值分别为1,2,3,4,5那么想一下,这个怎么写
insert tb select 1
union all select 2
union all select 3
union all select 4
union all select 5
这就是最终你的动态语句要执行的代码的原型. 想想如何根具1,2,3,4,5得到这样的原型.那么怎么把 1,2,3,4,5 转换成上面那样的字串呢?首先比对,它们是以","分隔的, 而生成的语句可以理解为是以 "union all select" 这个字串来分隔的这样直接将,替换为union all select 可以得到
1union all select2union all select3union all select4union all select5你可以看到,实际上,还需要在union all select前后加空格. 处理完空格后,就会得到1 union all select 2 union all select 3 union all select 4 union all select 5那么这个动态语句的主体你已经构建完了.
在前面再加上
insert tb select 就得到你要的东西了.刚开始写动态语句,不熟悉的时候,不求一步到位,能替换一部分就替换一部分, 常用 print @s 来看看你构建出了什么样的语句,切勿心浮气躁的来exec(@s)结果报错,又查错会浪费你很多时间.当熟练之后,print @s基本就可以不用了.
你要是嫌sql太复杂可以从简单的语句写起,要经常使用print 子句来查看在哪错了
________________________________________________________________________________有呀,要是没用print ,我又怎能写出(一)那段来呢?
你看,(一)那段就是从下面这句变出来的,但至于怎样才能变成(二),实在弄不出来了,
要是print能解决,我就不用发贴求救了, :)select name=ID,[2007-12-20]=max(case DATE when '2007-12-20' then BZ else 0 end),[2007-12-21]=max(case DATE when '2007-12-21' then BZ else 0 end),[2007-12-22]=max(case DATE when '2007-12-22' then BZ else 0 end) from #T group by ID因为@sql没有给初值,为空 null+‘任何值’=null ,
select @sql = isnull(@sql+',','select name=ID,'),那么@sql='select name=ID,'
这句还没明白,是不是我断句的地方错了?欢迎各位继续拍砖!
+quotename(DATE)
+'=max(case DATE when '''+DATE+''' then BZ else 0 end)' --我把 isnull 的结束位置弄错了,以为在这地方其实,应该是这样:
@sql = isnull(@sql+',','select name=ID,') 以后每次动态地加入后面的列,很巧妙地执行了日期列的循环.
上CSDN后见到各位大大都可以用动态的方法写下来,觉得很了不起,
原以为要用游标的方法才能实现的,怎简练的几句就写下来了,
哈哈,又学到一招!回头再看Roy的例子,试一试……:)
(
Name varchar(10) ,
Subject varchar(10) ,
Result int
)insert into #t(Name , Subject , Result) values( '张三 ', '语文 ', '73 ')
insert into #t(Name , Subject , Result) values( '张三 ', '数学 ', '83 ')
insert into #t(Name , Subject , Result) values( '张三 ', '物理 ', '93 ')
insert into #t(Name , Subject , Result) values( '李四 ', '语文 ', '74 ')
insert into #t(Name , Subject , Result) values( '李四 ', '数学 ', '83 ')
insert into #t(Name , Subject , Result) values( '李四 ', '物理 ', '93 ')--静态写法
select name
,[语文]=sum(case when subject='语文 ' then Result else 0 end)
,[数学]=sum(case when subject='数学 ' then Result else 0 end)
,[物理]=sum(case when subject='物理 ' then Result else 0 end)
from #T
group by name
--动态的写法
declare @sql varchar(8000)select @sql= isnull(@sql+',','select [name],')
+quotename(subject)
+'=MAX(case when subject='''+subject+''' then Result else 0 end)'
from (select distinct subject from #T) aset @sql=@sql+' from #T group by name'exec(@sql)
有sql基础的话
认真学两个星期就可以了
首先要会动态sql的基本语法
然后看看几个简单的例子
要体会到每个句子,每个关键字的具体作用
接着就搜索统计汇总的帖子
跟着做30个帖子就开始熟悉了
祝你成功
是前几个月项目要做统计汇总学的当时还写了篇学习文章,很浅
你可以去看看
http://user.qzone.qq.com/64905449/blog/8
/*
T1:
Materialid Stockid Qty
----------- ----------- -----------
101 501 100
101 502 120
101 503 130
102 601 160
102 602 200
T2:
Fid stockid stockname
----------- ----------- ----------
1 501 材料仓A
2 502 材料仓B
3 503 材料仓c
4 601 成品仓A
5 602 成品仓B
T3:
Fid Materialid Materialname
----------- ----------- ------------
1 101 电阻
2 102 打印机
结果
Materialname 材料仓A 材料仓B 材料仓c 成品仓A 成品仓B 数量合计
------------ ----------- ----------- ----------- ----------- ----------- -----------
打印机 0 0 0 160 200 360
电阻 100 120 130 0 0 350
合计 100 120 130 160 200 710
*/
-->测试数据
create table t1
(
Materialid int
,Stockid int
,Qty int
)
insert t1 select 101,501,100
insert t1 select 101,502,120
insert t1 select 101,503,130
insert t1 select 102,601,160
insert t1 select 102,602,200 create table t2
(
Fid int
,stockid int
,stockname varchar(10)
)
insert t2 select 1,501,'材料仓A'
insert t2 select 2,502,'材料仓B'
insert t2 select 3,503,'材料仓c'
insert t2 select 4,601,'成品仓A'
insert t2 select 5,602,'成品仓B'create table t3
(
Fid int
,Materialid int
,Materialname varchar(10)
)
insert t3 select 1,101,'电阻 '
insert t3 select 2,102,'打印机'declare @sql varchar(8000)select @sql=isnull(@sql+',','select [Materialname]=ISNULL(T3.Materialname,''合计''),')
+quotename(stockname)
+'=SUM(CASE WHEN stockid=+'''+ltrim(str(stockid))+''' then QTY ELSE 0 END)'
from (select stockname,stockid from T2 group by stockname,stockid) aset @sql=@sql
+', [数量合计]=SUM(QTY)'
+' from T1 left join T3 on T1.Materialid=T3.Materialid GROUP BY T3.Materialname with rollup'--print @sql
exec(@sql)
将指定的表,按指定的字段进行行列互换
--邹建 2004.04--*/
create proc p_zj
@tbname sysname, --要处理的表名
@fdname sysname, --做为转换的列名
@new_fdname sysname='' --为转换后的列指定列名
as
declare @s1 varchar(8000)
,@s2 varchar(8000)
,@s3 varchar(8000)
,@s4 varchar(8000)
,@s5 varchar(8000)
,@i varchar(10)select @s1=''
,@s2=''
,@s3=''
,@s4=''
,@s5=''
,@i='0'select @s1= @s1
+',@'
+@i
+' varchar(8000)'
,@s2= @s2
+',@'
+@i
+'='''+case isnull(@new_fdname,'') when '' then ''
else @new_fdname+'=' end+''''''+name+''''''''
-- ,@s2=@s2+',@'+@i+'=''性别='''''+name+''''''''
,@s3= @s3
+'select @'
+@i
+'=@'
+@i
+'+'',[''+['+@fdname+']+'']=''+cast(['+name+'] as varchar) from ['
+@tbname
+']'
,@s4=@s4
+',@'
+@i
+'=''select ''+@'
+@i
,@s5= @s5
+'+'' union all ''+@'
+@i
,@i= cast(@i as int)+1
from syscolumns
where object_id(@tbname)=id
and name<>@fdnameselect @s1=substring(@s1,2,8000)
,@s2=substring(@s2,2,8000)
,@s4=substring(@s4,2,8000)
,@s5=substring(@s5,16,8000)
exec('declare '+@s1+'
select '+@s2+@s3+'
select '+@s4+'
exec('+@s5+')')
go
什么是动态脚本: 脚本在设计规划时并不了解查询数据源的存储库、数据的选出条件、输出的数据格式等,需要根据输入条件进行动态调整组合。
1. 数据存储库的问题
一些系统因历史数据过大,需要将数据按时间段分表甚至分库存放。这样查询的时候就有可能因要查询的数据时间段而从不同的库表提取相同结构的数据。
2. 综合查询条件
一些应用系统的查询条件可能使用一些相关辅助信息,例如查找客户时参考客户联系人的相关信息。这样的查询可以根据传入的参数判断是否有在查询的条件中添加相关的脚本。
3. 统计报表输出
一些统计表格的表头经常因数据项的条目不同而变化,因此查询脚本的select部分也会根据需求而动态拼接调整。其实第三种情况的处理,我个人认为最宜在应用中实现,SQL的功能优势在于数据存储、查找、汇总,表格格式的展现调整实际上应该由前台处理,这样输出的表格格式可以更好看,更灵活,数据结构更稳定。
declare @sql_select varchar(2000)
declare @sql_from varchar(2000)
declare @sql_where varchar(2000)
declare @sql_order varchar(2000)set @sql_select = 'select ID '
-- 在这里完成SELECT字段列表的拼接
set @sql_select = @sql_select + ',[next field]' -- ...set @sql_from = 'from tableA '
-- 在这里完成FROM数据表列表的拼接
set @sql_from = @sql_from + ',[next table]' -- ...
/** or
set @sql_from = @sql_from + ' join [next table] on [join条件] ' -- ...
*/set @sql_where = 'where 1=1 '
-- 在这里完成条件表达式的拼接
set @sql_where = @sql_where + ' and ' -- ...set @sql_order = 'ORDER BY '
-- 在这里完成GROUP/ORDER列表的拼接
set @sql_order = @sql_order + '' -- ...-- 执行拼接好的脚本
exec (@sql_select+@sql_from+@sql_where+@sql_order)
2.综合查询条件
3.统计报表输出
________________谢谢tim spac,这三项正是我目前在应用中需要用到动态处理的.
其中"2.综合查询条件",这个问题上次 Limpire 就给了一个非常好的例子.
暂时使用得最多的还是在第二,第三项中,
至于第一项,有这样的需要,但还没动手试过。
动态写法很灵活,但它的可读性很差,初接触时觉得自己在读天书,
那些经过复杂串接的句子要是丢下一年装载再拿起来读,肯定是一件累活!
:)
动态脚本的性能、可读性都不是很理想,不得已而为之。
既然前台系统能够提供更好的数据报表,何不将“统计报表输出”的格式化工作交到前台处理呢?
将数据简单地Group出来,哪怕是粘贴到Excel表里,用Excel的数据透视功能生成所需的数据报表,这样的工作量要小得多。
此贴顶楼的例子(二)来自 Limpire(昨夜小楼)的一个回贴,
[原文在此]
http://topic.csdn.net/u/20071223/22/da09fe55-c5a1-489e-8328-d03ec9efc3ec.html衷心感谢各位,谢谢!
如有开罪之处,还请海函。:)新年来临之际,祝大家新一年里健康快乐!
——————————————————————————————————————————
好!
请你把转换过程step by step 的写出来看看,将(一)变成(二)
写出来的话,可以加分哦!