這不是分組求和嗎?select id,pay=sum(pay) from tb group by id
解决方案 »
- SQLSERVER2005数据库中存储过程的问题
- SQL代码,这个编译通过了,但是运行出错呀..是怎么了
- 触发器update,第三个数等于第一、二个数的和?
- SQL中怎样建关系???
- ASPNETPAGGER分页控件需要结合存储过程才能实现分页功能,求个分页存储过程。
- group by 分组问题,因为有几张表,求助高手,在线等10分钟
- sql语句在线等(急)
- 一个特别复杂的报表,请高手进来指导,谢谢!
- sql server 高手请进,在线等待,马上结帐。。。。。
- 用PB+SQL SERVER开发的程序,需要那些dll才能在新机器上运行?
- sql中的语法问题.
- SQL语句WHERE 中LIKE后加字符型变量如何写?
insert into tb
select 1 , '1000(CNY)'
union select 1 , '1000(HKD'
union select 2 , '1000(CNY)'insert into tb select 2 , '1000(hkd)'
insert into tb select 3 , '1000(hkd)'select * from tbselect id,max(m.c)
from (select a.id,a.pay+'+'+b.pay as c
from tb a,tb b
where a.id=b.id and a.pay<>b.pay ) m
group by id
union
select id,max(pay)
from tb
group by id
having count(*)=1
returns varchar(100)
as
begin
declare @re varchar(1000)
set @re=''
select @re=@re+'+'+pay from t1 where id=@id
return stuff(@re,1,1,'')
end
go
select id,dbo.f1(id) from t1
group by id
insert into t1
select 1 , '1000(CNY)'
union select 1 , '1000(HKD)'
union select 2 , '1000(CNY)'
insert into t1 select 2 , '1000(hkd)'
insert into t1 select 3 , '1000(hkd)'
go
create function f1(@id int)
returns varchar(1000)
as
begin
declare @re varchar(1000)
set @re=''
select @re=@re+'+'+pay from t1 where id=@id
return stuff(@re,1,1,'')
end
go
select id,dbo.f1(id) from t1
group by iddrop table t1
drop function dbo.f1
create table t1(id int,pay varchar(20))
insert into t1
select 1 , '1000(CNY)'
union select 1 , '1000(HKD)'
union select 2 , '1000(CNY)'
insert into t1 select 2 , '1000(hkd)'
insert into t1 select 3 , '1000(hkd)'
goselect id,min(pay)+case count(1) when 1 then '' else '+'+max(pay) end from t1
group by iddrop table t1
--测试数据
create table tb(id int,pay varchar(20))
insert into tb
select 1 , '1000(CNY)'
union select 1 , '1000(HKD'
union select 2 , '1000(CNY)'
insert into tb select 2 , '1000(hkd)'
insert into tb select 3 , '1000(hkd)'
go
--建函数
create function f_h(@id int)
returns varchar(1000)
as
begin
declare @char varchar(1000)
set @char=''
select @char=@char+'+'+pay from tb where id=@id
return right(@char,len(@char)-1)
end
go
--查询
select id,dbo.f_h(id) as pay from tb group by id
--删除测试数据
drop table tb
drop function f_h
/*结果
id pay
----------- ---------------------
1 1000(CNY)+1000(HKD
2 1000(CNY)+1000(hkd)
3 1000(hkd)(所影响的行数为 3 行)*/
Create Procedure testAS---生成结果集
If Object_id('tempdb..#Result') is not null
Drop Table #Result
Create table #Result
(
[ID] nvarchar(10),
pay nvarchar(50)
)
insert into #Result
select id,'' as pay from table1 group by id---对结果集做循环,将要处理的数据集中的数据一一更新到结果集中
Declare @id nvarchar(10),
@pay nvarchar(50)
Declare D_Loop Cursor For
Select id,pay from table1
Open D_Loop
FETCH NEXT FROM D_Loop INTO @id,@pay
While (@@FETCH_STATUS=0) Begin Update #result Set pay=pay+'+'+@pay Where id=@id FETCH NEXT FROM D_Loop INTO @id,@pay
End
----释放游标
Close D_Loop
Deallocate D_Loop---去掉字段pay的每一个字符'+'
Update #result set pay=substring(pay,2,len(pay)-1)
---显示结果集
Select * from #result order by id--exec test