求group by 后的sum以及第一条

[安琪有纪]

id t1 t2 t3
a1 1 x1 aa
a1 2 b2 1b
a2 3 c3 3c
a2 4 q4 1d

group by id , t1求sum t2 t3求top 1

结果
a1 3 x1 aa
a2 7 c3 3c

不用临时表

[xiaoku]

参考:
declare @t table (aa int, dd varchar(10))
insert into @t
select 1,'dc' union all
select 1,'cd' union all
select 2,'cd' union all
select 2,'dd'

select aa,max(dd) from @t
group by aa


（所影响的行数为 4 行）

aa
----------- ----------
1 dc
2 dd

（所影响的行数为 2 行）


----------------------------你要的
select id , sum(t1) , max(t2) , max(t3)
from tabel1
group by id

如果t2 和t3 基本上是英文加数字的话 以上就满足你了

[dawugui]

select id ,sum(t1) as t1 from a group by id
left jion
select t2,t3 from a b
where t1=(select top 1 t1 from a where id=b.id)

[ww3347]

create table table1 (id varchar(10), t1 int, t2 varchar(10), t3 varchar(10))
go
insert into table1
select 'a1', 1, 'x1', 'aa'
union select 'a1' , 2,'b2','1b'
union select 'a2' , 3,'c3','3c'
union select 'a2' , 4,'q4','1d'
go

select * from table1

select id ,
sumt1 = (select sum(t1) from table1 c where id=b.id),
t2,t3
from table1 b
where t1=(select top 1 t1 from table1 where id=b.id)
drop table table1
go

[ww3347]

id sumt1 t2 t3
---------- ----------- ---------- ----------
a1 3 x1 aa
a2 7 c3 3c

（所影响的行数为 2 行）

[ww3347]

select id , sum(t1) , max(t2) , max(t3)
from tabel1
group by id
-------------------
这样取出的T2和T3可能不是同一条记录的

[安琪有纪]

to:ww3347(新来的)
不错的答案，还想问一下如果t1列数值都一样怎么办

[安琪有纪]

假如没有identity列的话

[ww3347]

如果你没有能用来区分的列，就只能先再加上一个唯一标识列咯。

[Well]

--创建测试环境
create table test (id varchar(10),t1 int,t2 varchar(10),t3 varchar(10))
go
insert into test
select 'a1', 1, 'x1', 'aa'
union select 'a1' , 2,'b2','1b'
union select 'a2' , 3,'c3','3c'
union select 'a2' , 4,'q4','1d'
go
--测试
select id,sum(t1) t1
,(select top 1 t2 from test B where B.Id=A.ID) t2
,(select top 1 t3 from test B where B.Id=A.ID) t3
from test A group by id
--显示结果
id t1 t2 t3
a1 3 x1 aa
a2 7 c3 3c
--删除测试环境
drop table test
select * from