select 申请单流水号=a.[key],流水号=max(b.[key]),数量=sum(b.tamount) from test4 a left join test5 b on a.[key]=b.keyapply group by a.[key]
create table Tapplydtl([key] varchar(20), createdate varchar(30), keyoperator int) insert into Tapplydtl select '001', '2003-10-10', 100 insert into Tapplydtl select '002', '2003-11-11', 100 create table Tgoodsdtl([key] varchar(20), keyapply varchar(20), tamount int) insert into Tgoodsdtl select '01', '001', 20 insert into Tgoodsdtl select '02', '001', 30 insert into Tgoodsdtl select '03', '002', 30 create function fun(@keyapply varchar(20)) returns varchar(4000) as begin declare @n varchar(4000) select @n='' select @n=@n+[key]+',' from Tgoodsdtl where keyapply=@keyapply select @n=left(@n,len(@n)-1) return @n end select dbo.fun(a.[key]),a.[key],sum(tamount) from Tapplydtl a join Tgoodsdtl b on a.[key]=b.keyapply group by a.[key]
create table Tapplydtl([key] varchar(20), createdate varchar(30), keyoperator int) insert into Tapplydtl select '001', '2003-10-10', 100 insert into Tapplydtl select '002', '2003-11-11', 100 gocreate table Tgoodsdtl([key] varchar(20), keyapply varchar(20), tamount int) insert into Tgoodsdtl select '01', '001', 20 insert into Tgoodsdtl select '02', '001', 30 insert into Tgoodsdtl select '03', '002', 30 gocreate function fun(@keyapply varchar(20)) returns varchar(4000) as begin declare @n varchar(4000) select @n='' select @n=@n+[key]+',' from Tgoodsdtl where keyapply=@keyapply select @n=left(@n,len(@n)-1) return @n end goselect dbo.fun(a.[key]) 申请单流水号 ,a.[key] 流水号,sum(tamount) 数量 from Tapplydtl a join Tgoodsdtl b on a.[key]=b.keyapply group by a.[key]
--建议字段名不要用关键字:如key -- 先执行该函数 create function test(@id varchar(100)) returns varchar(8000) as begin declare @sql varchar(8000) set @sql='' select @sql=@sql+key+',' from Tgoodsdtl where keyapply=@id set @sql=left(@sql,len(@sql)-1) return(@sql) end --------------------------------------------------------------------------------------------------- select a.Key 申请单流水号,max(dbo.test(a.key)) 流水号,sum(b.tamount) 数量 from Tapplydtl a,Tgoodsdtl b where a.key=b.keyapply group by a.key
我执行的时候出错:将varchar值‘,’转换为数据类型为int的列时发生语法错误这是怎么回事?
create function getstr(@id varchar(100)) returns varchar(8000) as begin declare @sql varchar(8000) set @sql='' select @sql=@sql+cast([key] as varchar(10))+',' from Tgoodsdtl where keyapply=@id set @sql=left(@sql,len(@sql)-1) return(@sql) end go --调用 select a.[Key] 申请单流水号,dbo.getstr(a.[key]) 流水号,sum(b.tamount) 数量 from Tapplydtl a,Tgoodsdtl b where a.[key]=b.keyapply group by a.[key]
--方法1.临时表的处理方法(SQL7.0也适用) select keyapply,tamount,a=cast('' as varchar(8000)) into #t from Tgoodsdtl order by keyapply,[key] declare @a varchar(20),@b varchar(8000),@i int update #t set @b=case @a when keyapply then @b+','+keyapply else keyapply end ,@i=case @a when keyapply then @i+tamount else tamount end ,a=@b,tamount=@i,@a=keyapplyselect 申请单流水号=a.[key],流水号=b.a,数量=b.tamount from Tapplydtl a join( select keyapply,tamount=max(tamount),a=max(a) from #t group by keyapply ) b on a.[key]=b.keyapplydrop table #t go
--方法2.用函数(仅sql2000支持) --创建处理函数 create function f_merg(@keyapply varchar(20)) returns varchar(8000) as begin declare @re varchar(8000) set @re='' select @re=@re+','+[key] from Tgoodsdtl where keyapply=@keyapply set @re=substring(@re,2,8000) return(@re) end go--调用实现你的要求 select 申请单流水号=a.[key],流水号=dbo.f_merg(a.[key]),数量=sum(b.tamount) from Tapplydtl a join Tgoodsdtl b on a.[key]=b.keyapply group by a.[key] go
--临时表的错了一点,改一下: --方法1.临时表的处理方法(SQL7.0也适用) select [key],keyapply,tamount,a=cast('' as varchar(8000)) into #t from Tgoodsdtl order by keyapply,[key] declare @a varchar(20),@b varchar(8000),@i int update #t set @b=case @a when keyapply then @b+','+[key] else [key] end ,@i=case @a when keyapply then @i+tamount else tamount end ,a=@b,tamount=@i,@a=keyapplyselect 申请单流水号=a.[key],流水号=b.a,数量=b.tamount from Tapplydtl a join( select keyapply,tamount=max(tamount),a=max(a) from #t group by keyapply ) b on a.[key]=b.keyapplydrop table #t go
--下面是测试--测试数据 create table Tapplydtl([key] varchar(20),createdate varchar(30),keyoperator int) insert into Tapplydtl select '001','2003-10-10',100 union all select '002','2003-11-11',100 create table Tgoodsdtl([key] varchar(20),keyapply varchar(20),tamount int) insert into Tgoodsdtl select '01','001',20 union all select '02','001',30 union all select '03','002',30 go --方法1.临时表的处理方法(SQL7.0也适用)select [key],keyapply,tamount,a=cast('' as varchar(8000)) into #t from Tgoodsdtl order by keyapply,[key] declare @a varchar(20),@b varchar(8000),@i int update #t set @b=case @a when keyapply then @b+','+[key] else [key] end ,@i=case @a when keyapply then @i+tamount else tamount end ,a=@b,tamount=@i,@a=keyapplyselect 申请单流水号=a.[key],流水号=b.a,数量=b.tamount from Tapplydtl a join( select keyapply,tamount=max(tamount),a=max(a) from #t group by keyapply ) b on a.[key]=b.keyapplydrop table #t go --方法2.用函数(仅sql2000支持) --创建处理函数 create function f_merg(@keyapply varchar(20)) returns varchar(8000) as begin declare @re varchar(8000) set @re='' select @re=@re+','+[key] from Tgoodsdtl where keyapply=@keyapply set @re=substring(@re,2,8000) return(@re) end go--调用实现你的要求 select 申请单流水号=a.[key],流水号=dbo.f_merg(a.[key]),数量=sum(b.tamount) from Tapplydtl a join Tgoodsdtl b on a.[key]=b.keyapply group by a.[key] godrop table Tapplydtl,Tgoodsdtl drop function f_merg/*--测试结果方法1. 申请单流水号 流水号 数量 -------------------- ------------- ----------- 001 01,02 50 002 03 30(所影响的行数为 2 行)方法2. 申请单流水号 流水号 数量 -------------------- ------------- ----------- 001 01,02 50 002 03 30(所影响的行数为 2 行) --*/
left join test5 b on a.[key]=b.keyapply group by a.[key]
insert into Tapplydtl select '001', '2003-10-10', 100
insert into Tapplydtl select '002', '2003-11-11', 100 create table Tgoodsdtl([key] varchar(20), keyapply varchar(20), tamount int)
insert into Tgoodsdtl select '01', '001', 20
insert into Tgoodsdtl select '02', '001', 30
insert into Tgoodsdtl select '03', '002', 30
create function fun(@keyapply varchar(20))
returns varchar(4000) as
begin
declare @n varchar(4000)
select @n=''
select @n=@n+[key]+',' from Tgoodsdtl where keyapply=@keyapply
select @n=left(@n,len(@n)-1)
return @n
end
select dbo.fun(a.[key]),a.[key],sum(tamount)
from Tapplydtl a join Tgoodsdtl b
on a.[key]=b.keyapply
group by a.[key]
insert into Tapplydtl select '001', '2003-10-10', 100
insert into Tapplydtl select '002', '2003-11-11', 100 gocreate table Tgoodsdtl([key] varchar(20), keyapply varchar(20), tamount int)
insert into Tgoodsdtl select '01', '001', 20
insert into Tgoodsdtl select '02', '001', 30
insert into Tgoodsdtl select '03', '002', 30
gocreate function fun(@keyapply varchar(20))
returns varchar(4000) as
begin
declare @n varchar(4000)
select @n=''
select @n=@n+[key]+',' from Tgoodsdtl where keyapply=@keyapply
select @n=left(@n,len(@n)-1)
return @n
end
goselect dbo.fun(a.[key]) 申请单流水号 ,a.[key] 流水号,sum(tamount) 数量
from Tapplydtl a join Tgoodsdtl b
on a.[key]=b.keyapply
group by a.[key]
-- 先执行该函数
create function test(@id varchar(100))
returns varchar(8000)
as
begin
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+key+',' from Tgoodsdtl where keyapply=@id
set @sql=left(@sql,len(@sql)-1)
return(@sql)
end
---------------------------------------------------------------------------------------------------
select a.Key 申请单流水号,max(dbo.test(a.key)) 流水号,sum(b.tamount) 数量 from Tapplydtl a,Tgoodsdtl b
where a.key=b.keyapply
group by a.key
returns varchar(8000)
as
begin
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+cast([key] as varchar(10))+',' from Tgoodsdtl where keyapply=@id
set @sql=left(@sql,len(@sql)-1)
return(@sql)
end
go
--调用
select a.[Key] 申请单流水号,dbo.getstr(a.[key]) 流水号,sum(b.tamount) 数量 from Tapplydtl a,Tgoodsdtl b
where a.[key]=b.keyapply
group by a.[key]
select keyapply,tamount,a=cast('' as varchar(8000)) into #t
from Tgoodsdtl order by keyapply,[key]
declare @a varchar(20),@b varchar(8000),@i int
update #t set @b=case @a when keyapply then @b+','+keyapply else keyapply end
,@i=case @a when keyapply then @i+tamount else tamount end
,a=@b,tamount=@i,@a=keyapplyselect 申请单流水号=a.[key],流水号=b.a,数量=b.tamount
from Tapplydtl a join(
select keyapply,tamount=max(tamount),a=max(a) from #t group by keyapply
) b on a.[key]=b.keyapplydrop table #t
go
--创建处理函数
create function f_merg(@keyapply varchar(20))
returns varchar(8000)
as
begin
declare @re varchar(8000)
set @re=''
select @re=@re+','+[key] from Tgoodsdtl where keyapply=@keyapply
set @re=substring(@re,2,8000)
return(@re)
end
go--调用实现你的要求
select 申请单流水号=a.[key],流水号=dbo.f_merg(a.[key]),数量=sum(b.tamount)
from Tapplydtl a join Tgoodsdtl b on a.[key]=b.keyapply
group by a.[key]
go
--方法1.临时表的处理方法(SQL7.0也适用)
select [key],keyapply,tamount,a=cast('' as varchar(8000)) into #t
from Tgoodsdtl order by keyapply,[key]
declare @a varchar(20),@b varchar(8000),@i int
update #t set @b=case @a when keyapply then @b+','+[key] else [key] end
,@i=case @a when keyapply then @i+tamount else tamount end
,a=@b,tamount=@i,@a=keyapplyselect 申请单流水号=a.[key],流水号=b.a,数量=b.tamount
from Tapplydtl a join(
select keyapply,tamount=max(tamount),a=max(a) from #t group by keyapply
) b on a.[key]=b.keyapplydrop table #t
go
create table Tapplydtl([key] varchar(20),createdate varchar(30),keyoperator int)
insert into Tapplydtl
select '001','2003-10-10',100
union all select '002','2003-11-11',100 create table Tgoodsdtl([key] varchar(20),keyapply varchar(20),tamount int)
insert into Tgoodsdtl
select '01','001',20
union all select '02','001',30
union all select '03','002',30
go
--方法1.临时表的处理方法(SQL7.0也适用)select [key],keyapply,tamount,a=cast('' as varchar(8000)) into #t
from Tgoodsdtl order by keyapply,[key]
declare @a varchar(20),@b varchar(8000),@i int
update #t set @b=case @a when keyapply then @b+','+[key] else [key] end
,@i=case @a when keyapply then @i+tamount else tamount end
,a=@b,tamount=@i,@a=keyapplyselect 申请单流水号=a.[key],流水号=b.a,数量=b.tamount
from Tapplydtl a join(
select keyapply,tamount=max(tamount),a=max(a) from #t group by keyapply
) b on a.[key]=b.keyapplydrop table #t
go
--方法2.用函数(仅sql2000支持)
--创建处理函数
create function f_merg(@keyapply varchar(20))
returns varchar(8000)
as
begin
declare @re varchar(8000)
set @re=''
select @re=@re+','+[key] from Tgoodsdtl where keyapply=@keyapply
set @re=substring(@re,2,8000)
return(@re)
end
go--调用实现你的要求
select 申请单流水号=a.[key],流水号=dbo.f_merg(a.[key]),数量=sum(b.tamount)
from Tapplydtl a join Tgoodsdtl b on a.[key]=b.keyapply
group by a.[key]
godrop table Tapplydtl,Tgoodsdtl
drop function f_merg/*--测试结果方法1.
申请单流水号 流水号 数量
-------------------- ------------- -----------
001 01,02 50
002 03 30(所影响的行数为 2 行)方法2.
申请单流水号 流水号 数量
-------------------- ------------- -----------
001 01,02 50
002 03 30(所影响的行数为 2 行)
--*/