当前位置: 代码迷 >> Sql Server >> 分组中最大值与其对应的其他值相加如何做
  详细解决方案

分组中最大值与其对应的其他值相加如何做

热度:73   发布时间:2016-04-27 16:35:51.0
求教:分组中最大值与其对应的其他值相加怎么做?
现有这样一张表:
Id month num1 num2
1 2007-01 10 8
2 2007-01 20 5
3 2007-01 30 2
4 2007-02 80 5
5 2007-02 60 2
6 2007-01 50 15


想得到如下结果:
num1的最大值   +   对应的num2的值
例如:  
Id month max(num1)+   num2
3 2007-01 32
4 2007-02 85


------解决方案--------------------
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)
------解决方案--------------------
6 2007-01 50 15
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]
  相关解决方案