今天面试的题目,没有想到什么好办法(主要是如何用尽量少的语句),请有兴趣的朋友帮忙看看。表1(制造商号码作为主键)
制造商号码 制造商名
A1 Aname1
A2 Aname2
A3 Aname3
A4 Aname4
B1 Bname1
B2 Bname2
B3 Bname3表2(制造商号码和商品号码联合作为主键)
制造商号码 商品号码 产量
A1 S1 1
A1 S2 2
A2 S2 3
A2 S3 4
A3 S1 5
A3 S4 6
A4 S3 7
A4 S5 8
B1 S6 9
B2 S6 10
B3 S1 11要求做两个存储过程
1。存储过程CreateTempTable,一个Varchar型参数,例如:
CreateTempTable 'A'
则生成下表
商品号码 制造商A1 制造商A2 制造商A3 制造商A4
S1 1 0 5 0
S2 2 3 0 0
S3 0 4 0 7
S4 0 0 6 0
S5 0 0 0 8
CreateTempTable 'B'
则生成下表
商品号码 制造商B1 制造商B2 制造商B3
S1 0 0 11
S6 9 10 0
就是生成以输入参数开头的商品产量表,典型的行列转置问题,每次生成的表的列数和列名都不固定。
2。在1生成的表中任意改产量,用一个WriteSourceTable的存储过程,更新回表2要求用尽量少的语句实现,语句(注意:不是指代码字符数)越少,分数越高。
(以SQL语句数量决定,比如Create Table可能要写很多行,但还是看作一条SQL语句。)
制造商号码 制造商名
A1 Aname1
A2 Aname2
A3 Aname3
A4 Aname4
B1 Bname1
B2 Bname2
B3 Bname3表2(制造商号码和商品号码联合作为主键)
制造商号码 商品号码 产量
A1 S1 1
A1 S2 2
A2 S2 3
A2 S3 4
A3 S1 5
A3 S4 6
A4 S3 7
A4 S5 8
B1 S6 9
B2 S6 10
B3 S1 11要求做两个存储过程
1。存储过程CreateTempTable,一个Varchar型参数,例如:
CreateTempTable 'A'
则生成下表
商品号码 制造商A1 制造商A2 制造商A3 制造商A4
S1 1 0 5 0
S2 2 3 0 0
S3 0 4 0 7
S4 0 0 6 0
S5 0 0 0 8
CreateTempTable 'B'
则生成下表
商品号码 制造商B1 制造商B2 制造商B3
S1 0 0 11
S6 9 10 0
就是生成以输入参数开头的商品产量表,典型的行列转置问题,每次生成的表的列数和列名都不固定。
2。在1生成的表中任意改产量,用一个WriteSourceTable的存储过程,更新回表2要求用尽量少的语句实现,语句(注意:不是指代码字符数)越少,分数越高。
(以SQL语句数量决定,比如Create Table可能要写很多行,但还是看作一条SQL语句。)
(以SQL语句数量决定,比如Create Table可能要写很多行,但还是看作一条SQL语句。)
---------------------------------------------------------------------------
这个要求好玩,估计用动态语句可能会被判错
create table 表1(制造商号码 varchar(20) primary key,制造商名 varchar(20))
create table 表2(制造商号码 varchar(20),商品号码 varchar(20),产量 int)--插入测试数据
insert 表1(制造商号码,制造商名)
select 'A1','Aname1' union all
select 'A2','Aname2' union all
select 'A3','Aname3' union all
select 'A4','Aname4' union all
select 'B1','Bname1' union all
select 'B2','Bname2' union all
select 'B3','Bname3'insert 表2(制造商号码,商品号码,产量)
select 'A1','S1','1' union all
select 'A1','S2','2' union all
select 'A2','S2','3' union all
select 'A2','S3','4' union all
select 'A3','S1','5' union all
select 'A3','S4','6' union all
select 'A4','S3','7' union all
select 'A4','S5','8' union all
select 'B1','S6','9' union all
select 'B2','S6','10' union all
select 'B3','S1','11'
go
--求解过程
create proc CreateTempTable @str varchar(1)
as
begin
declare @sql varchar(8000)
select @sql = 'select 商品号码'
select @sql = @sql + ',sum(case 制造商号码 when ''' + 制造商号码 + ''' then 产量 else 0 end) as 制造商' + 制造商号码
from 表1
where 制造商号码 like @str+'_'
select @sql = @sql + ' from 表2 group by 商品号码'
exec (@sql)
end
goexec CreateTempTable 'A'
--删除测试环境
drop table 表2
drop table 表1
drop proc CreateTempTable
/*--测试结果
商品号码 制造商A1 制造商A2 制造商A3 制造商A4
-------------------- ----------- ----------- ----------- -----------
S1 1 0 5 0
S2 2 3 0 0
S3 0 4 0 7
S4 0 0 6 0
S5 0 0 0 8
S6 0 0 0 0*/
CreateTempTable 'A'
则生成下表
商品号码 制造商A1 制造商A2 制造商A3 制造商A4
S1 1 0 5 0
S2 2 3 0 0
S3 0 4 0 7
S4 0 0 6 0
S5 0 0 0 8
CreateTempTable 'B'
则生成下表
商品号码 制造商B1 制造商B2 制造商B3
S1 0 0 11
S6 9 10 0
而你的是:'A'
商品号码 制造商A1 制造商A2 制造商A3 制造商A4
-------------------- ----------- ----------- ----------- -----------
S1 1 0 5 0
S2 2 3 0 0
S3 0 4 0 7
S4 0 0 6 0
S5 0 0 0 8
S6 0 0 0 0
'B'
商品号码 制造商B1 制造商B2 制造商B3
S1 0 0 11
S2 0 0 0
S3 0 0 0
S4 0 0 0
S5 0 0 0
S6 9 10 0多出很多垃圾数,而且你的存储过程不通用,主要是缺少了生成临时表的代码
declare @ExecStr varchar(2000)
Select @ExecStr='Select * from table1'
exec(@ExecStr)
实际执行语句只有最后1行
create proc CreateTempTable @str varchar(100)
as
begin
declare @sql varchar(8000) if exists (select * from tempdb.dbo.sysobjects where id = object_id('tempdb.dbo.temptable1'))
exec('Drop table tempdb.dbo.temptable1')
select @sql = 'select 商品号码'
select @sql = @sql + ',sum(case 制造商号码 when ''' + 制造商号码 + ''' then 产量 else 0 end) as 制造商' + 制造商号码
from 表1
where 制造商号码 like @str+'%'
select @sql = @sql + ' into tempdb.dbo.temptable1 from 表2 where 商品号码 in (Select 商品号码 from 表2 where 制造商号码 like '''+@str+'%'') group by 商品号码'
exec (@sql)
Select * from tempdb.dbo.temptable1
end
goexec CreateTempTable 'A'
create table 表1(制造商号码 varchar(20) primary key,制造商名 varchar(20))
create table 表2(制造商号码 varchar(20),商品号码 varchar(20),产量 int)--插入测试数据
insert 表1(制造商号码,制造商名)
select 'A1','Aname1' union all
select 'A2','Aname2' union all
select 'A3','Aname3' union all
select 'A4','Aname4' union all
select 'B1','Bname1' union all
select 'B2','Bname2' union all
select 'B3','Bname3'insert 表2(制造商号码,商品号码,产量)
select 'A1','S1','1' union all
select 'A1','S2','2' union all
select 'A2','S2','3' union all
select 'A2','S3','4' union all
select 'A3','S1','5' union all
select 'A3','S4','6' union all
select 'A4','S3','7' union all
select 'A4','S5','8' union all
select 'B1','S6','9' union all
select 'B2','S6','10' union all
select 'B3','S1','11'
go
--第一题,CreateTempTable创建的表名为tmp
create proc CreateTempTable @str varchar(1)
as
begin
declare @sql varchar(8000)
select @sql = 'select 商品号码' select @sql = @sql + ',sum(case 制造商号码 when ''' + 制造商号码
+ ''' then 产量 else 0 end) as 制造商' + 制造商号码
from 表1
where 制造商号码 in (select 制造商号码 from 表2 where 制造商号码 like @str+'_')
order by 制造商号码 select @sql = @sql + ' from 表2 where 制造商号码 like ''' + @str + '_'' group by 商品号码' declare @col varchar(8000)
set @col = 'create table tmp(商品号码 varchar(20)'
select @col = @col + ',制造商' + 制造商号码 + ' varchar(20)'
from 表1
where 制造商号码 like @str+'_'
order by 制造商号码 select @col = @col + ')' if exists (select 1 from sysobjects where id = object_id(N'tmp') and type = 'U')
drop table tmp
exec (@col) insert tmp
exec (@sql)
end
go
--测试
exec CreateTempTable 'A'select * from tmp
/*--测试结果
商品号码 制造商A1 制造商A2 制造商A3 制造商A4
------ ------------ -------------------- -------------------- ----------------
S1 1 0 5 0
S2 2 3 0 0
S3 0 4 0 7
S4 0 0 6 0
S5 0 0 0 8*/--第二题
go
create proc WriteSourceTable
as
begin
declare @sql varchar(8000)
select @sql = ''
select @sql = @sql + ' union all select ''' + right(name,2)
+ ''' as 制造商,商品号码,' + name + ' as 产量 from tmp'
from(
select name from syscolumns where id = object_id(N'tmp') and name like '制造商%'
) _x
select @sql = stuff(@sql,1,11,'')
create table #t(制造商号码 varchar(20),商品号码 varchar(20),产量 int)
insert #t
exec(@sql) update _a
set _a.产量 = _b.产量
from 表2 _a
join #t _b on _b.制造商号码 = _a.制造商号码 and _b.商品号码 = _a.商品号码 insert 表2
select * from #t _t
where not exists
(select 1 from 表2 t where t.制造商号码 = _t.制造商号码 and t.商品号码 = _t.商品号码)
and _t.产量 > 0 drop table #t
end
go--修改数据
update tmp
set 制造商A1 = 100--执行存储过程
exec WriteSourceTable--查看结果
select * from 表2--结果
/*
制造商号码 商品号码 产量
-------------------- -------------------- -----------
A1 S1 100
A1 S2 100
A2 S2 3
A2 S3 4
A3 S1 5
A3 S4 6
A4 S3 7
A4 S5 8
B1 S6 9
B2 S6 10
B3 S1 11
A1 S3 100
A1 S4 100
A1 S5 100
*/
--删除测试环境
drop table 表2
drop table 表1
drop proc CreateTempTable,WriteSourceTable
一些细节的东西你自己改改就可以了。洗洗睡啦。
2。第2个要求你还是没有达到。
1。exec CreateTempTable 'A1'
------------------------------
这个可以啊。把所有的
where 制造商号码 like @str+'_' 改为 where 制造商号码 like '%'+@str+'%'2。第2个要求你还是没有达到。
-----------------------------
你是说制造商号码有可能是‘A1’、‘A11’、‘A111’?
把所有的right(name,2) 改为 right(name,len(name)-3)修改了测试数据,再测。--创建测试环境
create table 表1(制造商号码 varchar(20) primary key,制造商名 varchar(20))
create table 表2(制造商号码 varchar(20),商品号码 varchar(20),产量 int)--插入测试数据
insert 表1(制造商号码,制造商名)
select 'A1','Aname1' union all
select 'A22','Aname2' union all
select 'A333','Aname3' union all
select 'A4444','Aname4' union all
select 'B1','Bname1' union all
select 'B2','Bname2' union all
select 'B3','Bname3'insert 表2(制造商号码,商品号码,产量)
select 'A1','S1','1' union all
select 'A1','S2','2' union all
select 'A22','S2','3' union all
select 'A22','S3','4' union all
select 'A333','S1','5' union all
select 'A333','S4','6' union all
select 'A4444','S3','7' union all
select 'A4444','S5','8' union all
select 'B1','S6','9' union all
select 'B2','S6','10' union all
select 'B3','S1','11'
go
--第一题,CreateTempTable创建的表名为tmp
create proc CreateTempTable @str varchar(1)
as
begin
declare @sql varchar(8000)
select @sql = 'select 商品号码' select @sql = @sql + ',sum(case 制造商号码 when ''' + 制造商号码
+ ''' then 产量 else 0 end) as 制造商' + 制造商号码
from 表1
where 制造商号码 in (select 制造商号码 from 表2 where 制造商号码 like '%'+ @str+'%')
order by 制造商号码 select @sql = @sql + ' from 表2 where 制造商号码 like ''%''+''' + @str + '''+ ''%'' group by 商品号码'
declare @col varchar(8000)
set @col = 'create table tmp(商品号码 varchar(20)'
select @col = @col + ',制造商' + 制造商号码 + ' varchar(20)'
from 表1
where 制造商号码 like '%'+ @str+'%'
order by 制造商号码 select @col = @col + ')' if exists (select 1 from sysobjects where id = object_id(N'tmp') and type = 'U')
drop table tmp
exec (@col) insert tmp
exec (@sql)
end
go
--测试
exec CreateTempTable 'A1'select * from tmp
/*--测试结果
商品号码 制造商A1 制造商A22 制造商A333 制造商A4444
-------------------- -------------------- -------------------- --------------------
S1 1 0 5 0
S2 2 3 0 0
S3 0 4 0 7
S4 0 0 6 0
S5 0 0 0 8*/--第二题
go
create proc WriteSourceTable
as
begin
declare @sql varchar(8000)
select @sql = ''
select @sql = @sql + ' union all select ''' + right(name,len(name)-3)
+ ''' as 制造商,商品号码,' + name + ' as 产量 from tmp'
from(
select name from syscolumns where id = object_id(N'tmp') and name like '制造商%'
) _x
select @sql = stuff(@sql,1,11,'')
create table #t(制造商号码 varchar(20),商品号码 varchar(20),产量 int)
insert #t
exec(@sql) update _a
set _a.产量 = _b.产量
from 表2 _a
join #t _b on _b.制造商号码 = _a.制造商号码 and _b.商品号码 = _a.商品号码 insert 表2
select * from #t _t
where not exists
(select 1 from 表2 t where t.制造商号码 = _t.制造商号码 and t.商品号码 = _t.商品号码)
and _t.产量 > 0 drop table #t
end
go--修改数据
update tmp
set 制造商A1 = 100--执行存储过程
exec WriteSourceTable--查看结果
select * from 表2--结果
/*
制造商号码 商品号码 产量
-------------------- -------------------- -----------
A1 S1 100
A1 S2 100
A22 S2 3
A22 S3 4
A333 S1 5
A333 S4 6
A4444 S3 7
A4444 S5 8
B1 S6 9
B2 S6 10
B3 S1 11
A1 S3 100
A1 S4 100
A1 S5 100
*/
--删除测试环境
drop table 表2
drop table 表1
drop proc CreateTempTable,WriteSourceTable
-------------------- -------------------- -------------------- -------------
S1 100 0 5 0
S2 100 3 0 0
S3 100 4 0 7
S4 100 0 6 0
S5 100 0 0 8如果本身为0的数据忽略,不另外新增表2记录,只更新有产量的记录。
--创建测试环境
create table 表1(制造商号码 varchar(20) primary key,制造商名 varchar(20))
create table 表2(制造商号码 varchar(20),商品号码 varchar(20),产量 int)--插入测试数据
insert 表1(制造商号码,制造商名)
select 'A1','Aname1' union all
select 'A22','Aname2' union all
select 'A333','Aname3' union all
select 'A4444','Aname4' union all
select 'B1','Bname1' union all
select 'B2','Bname2' union all
select 'B3','Bname3'insert 表2(制造商号码,商品号码,产量)
select 'A1','S1','1' union all
select 'A1','S2','2' union all
select 'A22','S2','3' union all
select 'A22','S3','4' union all
select 'A333','S1','5' union all
select 'A333','S4','6' union all
select 'A4444','S3','7' union all
select 'A4444','S5','8' union all
select 'B1','S6','9' union all
select 'B2','S6','10' union all
select 'B3','S1','11'
go
--第一题,CreateTempTable创建的表名为tmp
create proc CreateTempTable @str varchar(8000)
as
begin
declare @sql varchar(8000)
select @sql = 'select 商品号码' select @sql = @sql + ',sum(case 制造商号码 when ''' + 制造商号码
+ ''' then 产量 else 0 end) as 制造商' + 制造商号码
from 表1
where 制造商号码 in (select 制造商号码 from 表2 where 制造商号码 like '%'+ @str+'%')
order by 制造商号码 select @sql = @sql + ' from 表2 where 制造商号码 like ''%''+''' + @str + '''+ ''%'' group by 商品号码'
declare @col varchar(8000)
set @col = 'create table tmp(商品号码 varchar(20)'
select @col = @col + ',制造商' + 制造商号码 + ' varchar(20)'
from 表1
where 制造商号码 like '%'+ @str+'%'
order by 制造商号码 select @col = @col + ')' if exists (select 1 from sysobjects where id = object_id(N'tmp') and type = 'U')
drop table tmp
exec (@col) insert tmp
exec (@sql)
end
go
--测试
exec CreateTempTable 'A1'select * from tmp
/*--测试结果
商品号码 制造商A1
-------------------- --------------------
S1 1
S2 2
*/--第二题
go
create proc WriteSourceTable
as
begin
declare @sql varchar(8000)
select @sql = ''
select @sql = @sql + ' union all select ''' + right(name,len(name)-3)
+ ''' as 制造商,商品号码,' + name + ' as 产量 from tmp'
from(
select name from syscolumns where id = object_id(N'tmp') and name like '制造商%'
) _x
select @sql = stuff(@sql,1,11,'')
create table #t(制造商号码 varchar(20),商品号码 varchar(20),产量 int)
insert #t
exec(@sql) update _a
set _a.产量 = _b.产量
from 表2 _a
join #t _b on _b.制造商号码 = _a.制造商号码 and _b.商品号码 = _a.商品号码 insert 表2
select * from #t _t
where not exists
(select 1 from 表2 t where t.制造商号码 = _t.制造商号码 and t.商品号码 = _t.商品号码)
and _t.产量 > 0 drop table #t
end
go--修改数据
update tmp
set 制造商A1 = 100select * from tmp
/*
商品号码 制造商A1
-------------------- --------------------
S1 100
S2 100
*/--执行存储过程
exec WriteSourceTable--查看结果
select * from 表2--结果
/*
制造商号码 商品号码 产量
-------------------- -------------------- -----------
A1 S1 100
A1 S2 100
A22 S2 3
A22 S3 4
A333 S1 5
A333 S4 6
A4444 S3 7
A4444 S5 8
B1 S6 9
B2 S6 10
B3 S1 11
*/
--删除测试环境
drop table 表2
drop table 表1
drop proc CreateTempTable,WriteSourceTable
樓主是面試官...偶太有才了!
insert into 表1 select 'A1','Aname1'
union all select 'A2','Aname2'
union all select 'A3','Aname3'
union all select 'A4','Aname4'
union all select 'B1','Bname1'
union all select 'B2','Bname2'
union all select 'B3','Bname3'
gocreate table 表2(制造商号码 varchar(10),商品号码 varchar(10),产量 int)
insert into 表2 select 'A1','S1',1
union all select 'A1','S2',2
union all select 'A2','S2',3
union all select 'A2','S3',4
union all select 'A3','S1',5
union all select 'A3','S4',6
union all select 'A4','S3',7
union all select 'A4','S5',8
union all select 'B1','S6',9
union all select 'B2','S6',10
union all select 'B3','S1',11
goselect * from 表1
select * from 表2
create procedure CreateTempTable @a varchar(1) as
declare @b varchar(8000)
set @b=''
select @b=@b+',(select count(*) from 表2 where 商品号码=a.商品号码 and 制造商号码='''+制造商号码 from 表1 where left(制造商号码,1)=@a
select @b=stuff(replace(@b,',','''),'),1,2,'')+''')'
exec ('select 商品号码 '+@b+' from 表2 a where left(制造商号码,1)=''A'' group by 商品号码')
goexec CreateTempTable 'A'
今天跟那个公司面试的主管邮件联系过,被告知一个新的要求,临时表的名称也要采用动态参数,比如CreateTempTable 'A','tmp',WriteSourceTable 'tmp',请问mengmou()mengmou() ()可以再次指点吗?谢谢!也请热心的朋友关注此题,谢谢!
--创建测试环境
create table 表1(制造商号码 varchar(20) primary key,制造商名 varchar(20))
create table 表2(制造商号码 varchar(20),商品号码 varchar(20),产量 int)--插入测试数据
insert 表1(制造商号码,制造商名)
select 'A1','Aname1' union all
select 'A22','Aname2' union all
select 'A333','Aname3' union all
select 'A4444','Aname4' union all
select 'B1','Bname1' union all
select 'B2','Bname2' union all
select 'B3','Bname3'insert 表2(制造商号码,商品号码,产量)
select 'A1','S1','1' union all
select 'A1','S2','2' union all
select 'A22','S2','3' union all
select 'A22','S3','4' union all
select 'A333','S1','5' union all
select 'A333','S4','6' union all
select 'A4444','S3','7' union all
select 'A4444','S5','8' union all
select 'B1','S6','9' union all
select 'B2','S6','10' union all
select 'B3','S1','11'
go
--第一题,CreateTempTable动态创建表
create proc CreateTempTable @str varchar(8000),@tablename sysname
as
begin
declare @sql varchar(8000)
select @sql = 'select 商品号码' select @sql = @sql + ',sum(case 制造商号码 when ''' + 制造商号码
+ ''' then 产量 else 0 end) as 制造商' + 制造商号码
from 表1
where 制造商号码 in (select 制造商号码 from 表2 where 制造商号码 like '%'+ @str+'%')
order by 制造商号码 select @sql = @sql + ' from 表2 where 制造商号码 like ''%''+''' + @str + '''+ ''%'' group by 商品号码'
select @sql = replace(@sql,'''','''''')
declare @col varchar(8000)
set @col = 'create table '+ @tablename+ '(商品号码 varchar(20)'
select @col = @col + ',制造商' + 制造商号码 + ' varchar(20)'
from 表1
where 制造商号码 like '%'+ @str+'%'
order by 制造商号码 select @col = @col + ')' if exists (select 1 from sysobjects where id = object_id(@tablename) and type = 'U')
exec('drop table '+ @tablename)
exec (@col)
exec( 'insert '+ @tablename + ' exec ('''+@sql+''')')
end
go
--测试
exec CreateTempTable 'A1','tablename'select * from tablename
/*--测试结果
商品号码 制造商A1
-------------------- --------------------
S1 1
S2 2
*/
--第二题
go
create proc WriteSourceTable @tablename sysname
as
begin
if not exists (select 1 from sysobjects where id = object_id(@tablename) and type = 'U')
begin
select '没有这个表!'
return
end declare @sql varchar(8000)
select @sql = ''
select @sql = @sql + ' union all select ''' + right(name,len(name)-3)
+ ''' as 制造商,商品号码,' + name + ' as 产量 from '+ @tablename
from(
select name from syscolumns where id = object_id(@tablename) and name like '制造商%'
) _x
select @sql = stuff(@sql,1,11,'')
create table #t(制造商号码 varchar(20),商品号码 varchar(20),产量 int)
insert #t
exec(@sql) update _a
set _a.产量 = _b.产量
from 表2 _a
join #t _b on _b.制造商号码 = _a.制造商号码 and _b.商品号码 = _a.商品号码 insert 表2
select * from #t _t
where not exists
(select 1 from 表2 t where t.制造商号码 = _t.制造商号码 and t.商品号码 = _t.商品号码)
and _t.产量 > 0 drop table #t
end
go--修改数据
update tablename
set 制造商A1 = 100select * from tablename
/*
商品号码 制造商A1
-------------------- --------------------
S1 100
S2 100
*/--执行存储过程
exec WriteSourceTable 'tablename'--查看结果
select * from 表2--结果
/*
制造商号码 商品号码 产量
-------------------- -------------------- -----------
A1 S1 100
A1 S2 100
A22 S2 3
A22 S3 4
A333 S1 5
A333 S4 6
A4444 S3 7
A4444 S5 8
B1 S6 9
B2 S6 10
B3 S1 11
*/
--删除测试环境
drop table 表2
drop table 表1
drop proc CreateTempTable,WriteSourceTable
create procedure Creattmp @str varchar(20)
as
begin
declare @sql varchar(8000),@sql1 varchar(8000)
select @sql='select 商品号码'
set @sql1=''
select @sql=@sql+',sum(case when 制造商号码='''+制造商号码+''' then 产量 else 0 end) as 制造商'+制造商号码 from 表1 where 制造商号码 like '%'+@str+'%' ---group by 制造商号码
select @sql1=@sql1+'+sum(case when 制造商号码='''+制造商号码+''' then 产量 else 0 end)' from 表1 where 制造商号码 like '%'+@str+'%' ---group by 制造商号码
set @sql1=right(@sql1,len(@sql1)-1)
select @sql =@sql +' from 表2 group by 商品号码 having '+@sql1+'<>0'
exec(@sql)
end
--创建测试环境
create table 表2(制造商号码 varchar(20),商品号码 varchar(20),产量 int) insert 表2(制造商号码,商品号码,产量)
select 'A1 ', 'S1 ', '1 ' union all
select 'A1 ', 'S2 ', '2 ' union all
select 'A2 ', 'S2 ', '3 ' union all
select 'A2 ', 'S3 ', '4 ' union all
select 'A3 ', 'S1 ', '5 ' union all
select 'A3 ', 'S4 ', '6 ' union all
select 'A4 ', 'S3 ', '7 ' union all
select 'A4 ', 'S5 ', '8 ' union all
select 'B1 ', 'S6 ', '9 ' union all
select 'B2 ', 'S6 ', '10 ' union all
select 'B3 ', 'S1 ', '11 '
go SELECT
商品号码,
ISNULL(A1,0) AS '制造商A1',
ISNULL(A2,0) AS '制造商A2',
ISNULL(A3,0) AS '制造商A3',
ISNULL(A4,0) AS '制造商A4'
FROM (SELECT * FROM 表2 WHERE 制造商号码 like 'A%') t2
PIVOT (
SUM(产量) for 制造商号码 in ([A1],[A2],[A3],[A4])
) tt
ORDER BY 商品号码SELECT
商品号码,
ISNULL(B1,0) AS '制造商B1',
ISNULL(B2,0) AS '制造商B2',
ISNULL(B3,0) AS '制造商B3'
FROM (SELECT * FROM 表2 WHERE 制造商号码 like 'B%') t2
PIVOT (
SUM(产量) for 制造商号码 in ([B1],[B2],[B3])
) tt
ORDER BY 商品号码
弱弱地问一句: select @sql = replace(@sql, ' ' ' ', ' ' ' ' ' ') 是什么啊?repalce()执行什么功能?菜鸟继续学习中
java
select @sql = @sql + ',sum(case 制造商号码 when ' ' ' + 制造商号码 + ' ' ' then 产量 else 0 end) as 制造商 ' + 制造商号码
from 表1
where 制造商号码 like @str+ '% '
select @sql = @sql + ' into tempdb.dbo.temptable1 from 表2 where 商品号码 in (Select 商品号码 from 表2 where 制造商号码 like ' ' '+@str+ '% ' ') group by 商品号码 '
exec (@sql)
Select * from tempdb.dbo
#$%^&*()_
http://www.112ba.com
zuo de ye niu bi !
select 'A1 ', 'Aname1 ' union all
select 'A2 ', 'Aname2 ' union all
select 'A3 ', 'Aname3 ' union all
select 'A4 ', 'Aname4 ' union all
select 'B1 ', 'Bname1 ' union all
select 'B2 ', 'Bname2 ' union all
select 'B3 ', 'Bname3 ' insert t_Two(PrdtID, SaleID, Qty)
select 'A1 ', 'S1 ', '1 ' union all
select 'A1 ', 'S2 ', '2 ' union all
select 'A2 ', 'S2 ', '3 ' union all
select 'A2 ', 'S3 ', '4 ' union all
select 'A3 ', 'S1 ', '5 ' union all
select 'A3 ', 'S4 ', '6 ' union all
select 'A4 ', 'S3 ', '7 ' union all
select 'A4 ', 'S5 ', '8 ' union all
select 'B1 ', 'S6 ', '9 ' union all
select 'B2 ', 'S6 ', '10 ' union all
select 'B3 ', 'S1 ', '11 '
go
Create Proc sp_Sum(@Spilter varchar(20))
as
Declare @SQL varchar(500),
@PrdtID VarChar(20),
@OldPrdtID VarChar(20),
@PrdtName varchar(20),
@SaleID varchar(20),
@Qty int,
@count int,
@i int
Set @SQL = ' Declare @TblName Table (SaleID varchar(20)'
Declare CurTree Cursor For
Select PrdtID, PrdtName
From t_One
Where PrdtID like '%' + @Spilter + '%'
Order by PrdtID
Open CurTree
Fetch From CurTree Into @PrdtID, @PrdtName
While @@Fetch_Status = 0
Begin
Set @SQL = @SQL + ', ' + @PrdtName + ' int'
Fetch Next From CurTree Into @PrdtID, @PrdtName
End
Set @SQL = @SQL + ')'
--Exec @SQL
Close CurTree
Deallocate CurTree
Select @count = Count(PrdtID)
From t_One
Where PrdtID like '%' + @Spilter + '%'
Set @OldPrdtID = ''
--Set @i = 1
Declare CurTreeTwo Cursor For
Select PrdtID, SaleID, QTY
From t_Two
Where PrdtID like '%' + @Spilter + '%'
Order by SaleID, PrdtID
Open CurTreeTwo
Fetch From CurTreetwo Into @PrdtID, @SaleID, @Qty
While @@Fetch_Status = 0
Begin
Set @SQL = @SQL + 'insert @TblName Values(''' + @SaleID + ''''
Set @i = 1
--Set @OldPrdtID = @PrdtID
While @i <= @count
begin
if @Spilter + Cast(@i as varchar) = @PrdtID
begin
Set @SQL = @SQL + ', ' + cast(@Qty as varchar)
Set @i = @i + 1
Fetch Next From CurTreetwo Into @PrdtID, @SaleID, @Qty
end
Else
begin
Set @SQL = @SQL + ', 0 '
Set @i = @i + 1
end
if @i - 1= @count
Set @SQL = @SQL + ') '
end
--Fetch Next From CurTreetwo Into @PrdtID, @SaleID, @Qty
End
--Set @SQL = @SQL + ')'
Set @SQL = @SQL + 'Select * From @TblName '
Exec (@SQL)
Close CurTreetwo
Deallocate CurTreetwo
sp_Sum 'A'--结果
SaleID Aname1 Aname2 Aname3 Aname4
-------------------- ----------- ----------- ----------- -----------
S1 1 0 5 0
S2 2 3 0 0
S3 0 4 0 7
S4 0 0 6 8sp_Sum 'A'
--结果
SaleID Bname1 Bname2 Bname3
-------------------- ----------- ----------- -----------
S1 0 0 11
S6 9 10 0
2.CSS样式自己定;
2.CSS样式自己定;
Create Proc sp_Sum(@Spilter varchar(20))
as
Declare @SQL varchar(500),
@PrdtID VarChar(20),
@OldPrdtID VarChar(20),
@PrdtName varchar(20),
@SaleID varchar(20),
@Qty int,
@count int,
@i int
Set @SQL = ' Declare @TblName Table (SaleID varchar(20)'
Declare CurTree Cursor For
Select PrdtID, PrdtName
From t_One
Where PrdtID like '%' + @Spilter + '%'
Order by PrdtID
Open CurTree
Fetch From CurTree Into @PrdtID, @PrdtName
While @@Fetch_Status = 0
Begin
Set @SQL = @SQL + ', ' + @PrdtName + ' int'
Fetch Next From CurTree Into @PrdtID, @PrdtName
End
Set @SQL = @SQL + ')'
--Exec @SQL
Close CurTree
Deallocate CurTree