求教：分组中最大值与其对应的其他值相加怎么做？
现有这样一张表：
Idmonthnum1num2
12007-01108
22007-01205
32007-01302
42007-02805
52007-02602
62007-015015
想得到如下结果：
num1的最大值 + 对应的num2的值
例如：
Idmonthmax(num1)+ num2
32007-0132
42007-0285
[解决办法]
select
t.Id,t.month,t.num1+t.num2 as num
from
表 t
where
not exists(select 1 from 表 where [month]=t.[month] and num1> t.num1)
[解决办法]
62007-015015
2007-01为何不是50+15
[解决办法]
declare @t table(Id int,month varchar(10),num1 int,num2 int)
insert into @t select 1, '2007-01 ',10,8
insert into @t select 2, '2007-01 ',20,5
insert into @t select 3, '2007-01 ',30,2
insert into @t select 4, '2007-02 ',80,5
insert into @t select 5, '2007-02 ',60,2
insert into @t select 6, '2007-02 ',50,15
select
t.Id,t.month,t.num1+t.num2 as num
from
@t t
where
not exists(select 1 from @t where [month]=t.[month] and num1> t.num1)
/*
Id month num
----------- ---------- -----------
3 2007-01 32
4 2007-02 85
*/
[解决办法]
上面寫了兩種，我寫出第三種方法
Select
Id,
[month],
num1+ num2 As num
From
表 A
Where num1 = (Select Max(num1) From 表 Where [month] = A.[month])
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb(Id int,[month] varchar(10),num1 int,num2 int)
insert into tb(Id,[month],num1,num2) values(1, '2007-01 ',10,8)
insert into tb(Id,[month],num1,num2) values(2, '2007-01 ',20,5)
insert into tb(Id,[month],num1,num2) values(3, '2007-01 ',30,2)
insert into tb(Id,[month],num1,num2) values(4, '2007-02 ',80,5)
insert into tb(Id,[month],num1,num2) values(5, '2007-02 ',60,2)
insert into tb(Id,[month],num1,num2) values(6, '2007-01 ',50,15)
go
select tb.month , tb.num1 + tb.num2 as num from tb,
( select month,max(num1) num1 from tb group by month) t
where tb.month = t.month and tb.num1 = t.num1
drop table tb
/*
month num
---------- -----------
2007-02 85
2007-01 65
（所影响的行数为 2 行）
*/
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb(Id int,[month] varchar(10),num1 int,num2 int)
insert into tb(Id,[month],num1,num2) values(1, '2007-01 ',10,8)
insert into tb(Id,[month],num1,num2) values(2, '2007-01 ',20,5)
insert into tb(Id,[month],num1,num2) values(3, '2007-01 ',30,2)
insert into tb(Id,[month],num1,num2) values(4, '2007-02 ',80,5)
insert into tb(Id,[month],num1,num2) values(5, '2007-02 ',60,2)
insert into tb(Id,[month],num1,num2) values(6, '2007-01 ',50,15)
go
select tb.[month] , tb.num1 + tb.num2 as num from tb,
( select [month],max(num1) num1 from tb group by [month]) t
where tb.[month] = t.month and tb.num1 = t.num1
order by tb.[month]
drop table tb
/*
month num
---------- -----------
2007-01 65
2007-02 85
（所影响的行数为 2 行）
*/
[解决办法]
create table t(Id int,month varchar(20),num1 int,num2 int)
insert t select 1, '2007-01 ',10,8
union all select 2, '2007-01 ',20,5
union all select 3, '2007-01 ',30,2
union all select 4, '2007-02 ',80,5
union all select 5, '2007-02 ',60,2
union all select 6, '2007-01 ',50,15
select Id,month,num1+num2 as 'max(num1)+ num2 ' from t a
where num1 in (select max(num1) from t group by month)
order by month
drop table t
Id month max(num1)+ num2
----------- -------------------- ---------------
6 2007-01 65
4 2007-02 85
（所影响的行数为 2 行）
[解决办法]
declare @t table(Id int,month varchar(10),num1 int,num2 int)
insert into @t select 1, '2007-01 ',10,8
insert into @t select 2, '2007-01 ',20,5
insert into @t select 3, '2007-01 ',30,2
insert into @t select 4, '2007-02 ',80,5
insert into @t select 5, '2007-02 ',60,2
insert into @t select 6, '2007-02 ',50,15
Select
Id,
[month],
num1+ num2 As num
From
@t A
Where num1 = (Select Max(num1) From @t Where [month] = A.[month])
Order By Id
/*
Id month num
----------- ---------- -----------
3 2007-01 32
4 2007-02 85
*/
[解决办法]
没理解错啊，你的测试数据 3 2007-01 30 2和6 2007-01 50 15 ，num1+num2的值就是65
[解决办法]
declare @t table(Id int,month varchar(10),num1 int,num2 int)
insert into @t select 1, '2007-01 ',10,8
insert into @t select 2, '2007-01 ',20,5
insert into @t select 3, '2007-01 ',30,2
insert into @t select 4, '2007-02 ',80,5
insert into @t select 5, '2007-02 ',60,2
insert into @t select 6, '2007-02 ',50,15
select id,month,num1+num2 as 'max(num1)+num2 ' from @t a
where not exists(select 1 from @t where a.month=month and a.num1 <num1)
id month max(num1)+num2
----------- ---------- --------------
3 2007-01 32
4 2007-02 85
