现在有表biness和totle表,biness.user_entry_ID=totle.user_entry_ID, 一个biness.user_entry_id可以对应多个totle表中的user_entry_ID
如果biness.user_entry=1,会在totle表里查出四条数据,其中totle表里price,分别会为20,20,40.40
问题:可是我显示的时候想显示(2*20,2*40)注:biness与totle合成一个视图
答案:
create table biness (user_entry_ID int)
insert into biness select 1 union all select 2
create table totle(user_entry_ID int,price int)
insert into totle
select 1,20 union all
select 1,20 union all
select 1,40 union all
select 1,40 union all
select 2,10 union all
select 2,30 union all
select 2,40 union all
select 2,50
alter function get_sum(@id int)
returns varchar(100)
as
begin
declare @str varchar(100)
select @str=isnull(@str,'')+','+cast(count(*) as varchar(10))+'*'+ cast(price as varchar(10)) from totle
where user_entry_ID=@id
group by price
return stuff(@str,1,1,'')
end
select user_entry_ID,dbo.get_sum(user_entry_ID) from biness