参考: 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 基本上是英文加数字的话 以上就满足你了
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)
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' goselect * from table1select 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
id sumt1 t2 t3 ---------- ----------- ---------- ---------- a1 3 x1 aa a2 7 c3 3c(所影响的行数为 2 行)
select id , sum(t1) , max(t2) , max(t3) from tabel1 group by id ------------------- 这样取出的T2和T3可能不是同一条记录的
to:ww3347(新来的) 不错的答案,还想问一下如果t1列数值都一样怎么办
--创建测试环境 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
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 基本上是英文加数字的话 以上就满足你了
left jion
select t2,t3 from a b
where t1=(select top 1 t1 from a where id=b.id)
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'
goselect * from table1select 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
---------- ----------- ---------- ----------
a1 3 x1 aa
a2 7 c3 3c(所影响的行数为 2 行)
from tabel1
group by id
-------------------
这样取出的T2和T3可能不是同一条记录的
不错的答案,还想问一下如果t1列数值都一样怎么办
--创建测试环境
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