都是表结构没有设计好导致的啊 SQL Server 啊 努力 奋斗-----------------------------------------------------
在函数内不可以使用 exec()函数执行动态SQL语句!!!!!! 郁闷!!!!!
create function mysearch(@s1 as varchar(20),@times as tinyint) returns varchar(20) as begin declare @tt smallint declare @rs varchar(20) set @tt = (@times-1)*4 +1 set @tt = charindex(',',@s1,@tt)+1 set @rs = substring(@s1,@tt,3) return (@rs) endmysearch(caidanhao,1)select zhuohao, mysearch(caidanhao,1) as 一号菜, mysearch(caidanhao,1) as 二号菜, mysearch(caidanhao,1) as 三号菜, mysearch(caidanhao,1) as 四号菜,
into #newtable from originaltable
select zhuohao as 桌号, mysearch(caidanhao,1) as 一号菜, mysearch(caidanhao,2) as 二号菜, ... mysearch(caidanhao,14) as 十四号菜, mysearch(caidanhao,15) as 十五号菜 into #newtable from originaltable
create table a(zhuohao int,caidanhao varchar(100)) insert into a select 12,'123,104' union all select 13,'103,105,106' union all select 14,'109,189,178,198'create function search(@s as varchar(100),@num as tinyint) returns varchar(10) as begin declare @i smallint declare @name varchar(10) set @i=(@num-1)*4+1 set @name=substring(@s,@i,3) return @name enddeclare @i int,@j int declare @sql varchar(4000) set @i=1 set @sql='' while @i<=15 begin select @sql=@sql+','+quotename(cast(@i as varchar)+'道菜')+'=dbo.search(caidanhao,'+cast(@i as varchar)+')' set @i=@i+1 end select @sql exec ('select zhuohao'+@sql+' into c from a')select * from c 結果: zhuohao 1道菜 2道菜 3道菜 4道菜 5道菜 6道菜 7道菜 8道菜 9道菜 10道菜 11道菜 12道菜 13道菜 14道菜 15道菜 ----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 12 123 104 13 103 105 106 14 109 189 178 198
在SQL里面嵌套函数就可以得结果了
郁闷!!!!!
returns varchar(20)
as
begin
declare @tt smallint
declare @rs varchar(20)
set @tt = (@times-1)*4 +1
set @tt = charindex(',',@s1,@tt)+1
set @rs = substring(@s1,@tt,3)
return (@rs)
endmysearch(caidanhao,1)select zhuohao,
mysearch(caidanhao,1) as 一号菜,
mysearch(caidanhao,1) as 二号菜,
mysearch(caidanhao,1) as 三号菜,
mysearch(caidanhao,1) as 四号菜,
into #newtable
from originaltable
mysearch(caidanhao,1) as 一号菜,
mysearch(caidanhao,2) as 二号菜,
...
mysearch(caidanhao,14) as 十四号菜,
mysearch(caidanhao,15) as 十五号菜
into #newtable
from originaltable
caidanhao(菜单号) caiming(菜名)
123 青菜
104 大白菜
最终要把点的菜所对应的菜用菜单名显示出来,(菜单号可要可不要)
谢谢啦,看那总方式比较好,我现在是做一个点菜方案,把菜单号写入数据库后用打印机把菜单名打印出来.让橱师做菜.
insert into a
select 12,'123,104' union all
select 13,'103,105,106' union all
select 14,'109,189,178,198'create function search(@s as varchar(100),@num as tinyint)
returns varchar(10)
as
begin
declare @i smallint
declare @name varchar(10)
set @i=(@num-1)*4+1
set @name=substring(@s,@i,3)
return @name
enddeclare @i int,@j int
declare @sql varchar(4000)
set @i=1
set @sql=''
while @i<=15
begin
select @sql=@sql+','+quotename(cast(@i as varchar)+'道菜')+'=dbo.search(caidanhao,'+cast(@i as varchar)+')'
set @i=@i+1
end
select @sql
exec ('select zhuohao'+@sql+' into c from a')select * from c
結果:
zhuohao 1道菜 2道菜 3道菜 4道菜 5道菜 6道菜 7道菜 8道菜 9道菜 10道菜 11道菜 12道菜 13道菜 14道菜 15道菜
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
12 123 104
13 103 105 106
14 109 189 178 198