求一个SQL立马结贴给分。。。。

[my97datepicker]

表

ID amount


查询表，将ID相同的amount全部加起来查询出整个表，amount为nvarchar类型

[水族杰纶]

select ID,
       sum(amount)amount
from tb 
where isnumeric(amount)=1
group by id

[SQLCenter]

if object_id('#') is not null drop table #
create table #(ID int, amount varchar(8))
insert into #
select 1, 'F' union all
select 2, 'E' union all
select 2, 'D' union all
select 3, 'C' union all
select 3, 'B' union all
select 3, 'A'
go

select ID, amount = stuff((select ',' + amount from # where ID = t.ID for xml path('')) , 1, 1, '') from # as t group by ID
go
/*
ID  amount
---- --------
1    F
2    E,D
3    C,B,A
*/

[obuntu]

select id,sum(convert(amount as int)) as amount_sum
from table
group by id

[SQLCenter]

这样？

[水族杰纶]

--amount如果有小数必须先转换
declare @t table(ID int,amount nvarchar(10))
insert @t select 1,'12.3'
insert @t select 1,'12'
insert @t select 2,'1.0'
select ID,
       sum(cast(amount as dec(18,2)))amount
from @t 
where isnumeric(amount)=1
group by ID

[水族杰纶]

[Quote=引用 4 楼 sqlcenter 的回复:]
这样？
[/Quote]
都是靠猜

[my97datepicker]

算了，虽然不是我要的但是我已经解决了