现有这样一张表：
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]