现有这样一张表:
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
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
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)
( select month,max(num1) num1 from tb group by month) t
where tb.month = t.month and tb.num1 = t.num1
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,15select
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])
drop table tb
gocreate 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.num1drop table tb/*
month num
---------- -----------
2007-02 85
2007-01 65(所影响的行数为 2 行)*/
drop table tb
gocreate 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 行)*/
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,15select Id,month,num1+num2 as 'max(num1)+ num2' from t a
where num1 in (select max(num1) from t group by month)
order by monthdrop table tId month max(num1)+ num2
----------- -------------------- ---------------
6 2007-01 65
4 2007-02 85(所影响的行数为 2 行)
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,15Select
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
*/
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,15select 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