100分，求SQL,急！！！

[vovo2000]

表有3个字段
id type num
1 a 10
1 b 20
1 c 30
1 d 40
2 a 50
2 b 80
2 c 30
2 d 80
3 a 300
3 b 700
3 c 700
3 d 700

请问怎么求得同一个id里面，num为最大的id和type
当同一个id有复数个同时为最大值的时候，只取type为最大的那个
上面的数据里想取得的数据是
1 d 40
2 d 80
3 d 700

跪求。急阿

[开着拖拉机泡妞]

/*
表有3个字段
id type num
1 a 10
1 b 20
1 c 30
1 d 40
2 a 50
2 b 80
2 c 30
2 d 80
3 a 300
3 b 700
3 c 700
3 d 700

请问怎么求得同一个id里面，num为最大的id和type
当同一个id有复数个同时为最大值的时候，只取type为最大的那个
上面的数据里想取得的数据是
1 d 40
2 d 80
3 d 700
*/
drop table tbl
create table tbl(
id char(2),
[type] char(2),
num int
)

insert into tbl values('1','a',10)
insert into tbl values('1','b',20)
insert into tbl values('1','c',30)
insert into tbl values('1','d',40)
insert into tbl values('2','a',50)
insert into tbl values('2','b',80)
insert into tbl values('2','c',30)
insert into tbl values('2','d',80)
insert into tbl values('3','a',300)
insert into tbl values('3','b',700)
insert into tbl values('3','c',700)
insert into tbl values('3','d',700)

select id,char(max(ascii([type]))) as type,max(num) as num from tbl group by id

/*
id type num
1 d 40
2 d 80
3 d 700
*/

[中国风]

--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T') is null
	drop table #T
Go
Create table #T([id] int,[type] nvarchar(1),[num] int)
Insert #T
select 1,N'a',10 union all
select 1,N'b',20 union all
select 1,N'c',30 union all
select 1,N'd',40 union all
select 2,N'a',50 union all
select 2,N'b',80 union all
select 2,N'c',30 union all
select 2,N'd',80 union all
select 3,N'a',300 union all
select 3,N'b',700 union all
select 3,N'c',700 union all
select 3,N'd',700
Go
select ID,type,num 
from (select ID,type,num,row=ROW_NUMBER()over(partition by ID  order by num desc,row desc)
from (Select *,ROW_NUMBER()over(order by ID) as row from #T)t
)t2
where row=1

/*
ID	type	num
1	d	40
2	d	80
3	d	700
*/

[dawugui]

select t.* from tb t where not exists(select 1 from tb where id = t.id and (num > t.num or (num = t.num and type > t.type)))

[dawugui]

Create table tb([id] int,[type] nvarchar(1),[num] int)
Insert tb
select 1,N'a',10 union all
select 1,N'b',20 union all
select 1,N'c',30 union all
select 1,N'd',40 union all
select 2,N'a',50 union all
select 2,N'b',80 union all
select 2,N'c',30 union all
select 2,N'd',80 union all
select 3,N'a',300 union all
select 3,N'b',700 union all
select 3,N'c',700 union all
select 3,N'd',700
Go

select t.* from tb t where not exists(select 1 from tb where id = t.id and (num > t.num or (num = t.num and type > t.type)))

drop table tb

/*
id          type num         
----------- ---- ----------- 
1           d    40
2           d    80
3           d    700

（所影响的行数为 3 行）
*/

[可乐AND爆米花]

select * from Ta t where not exists (select 1 from Ta where id=t.id and (num>t.num or num=t.num and type>t.type))

[myhaikuotiankong]

[Quote=引用 7 楼 a544589668 的回复:]

select * from Ta t where not exists (select 1 from Ta where id=t.id and (num>t.num or num=t.num and type>t.type))
[/Quote]++

[Alessandro_]

[Quote=引用 4 楼 roy_88 的回复:]

SQL code
--> --> (Roy)生成測試數據

if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[type] nvarchar(1),[num] int)
Insert #T
select 1,N'a',10 union all
select 1,N'b',2……
[/Quote]

[code=SQL]

--大版

[/CODE]

[陈永富]

[Quote=引用 11 楼 q806294478 的回复:]
SQL code
Create table #tb([id] int,[type] varchar(5),[num] int)
Insert #tb
select 1,'a',10 union all
select 1,'b',20 union all
select 1,'c',30 union all
select 1,'d',40 union all
select 2,'a',5……
[/Quote]
强大的开窗函数

[dongsheng10101]

if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[type] nvarchar(1),[num] int)
Insert #T
select 1,N'a',10 union all
select 1,N'b',20 union all
select 1,N'c',30 union all
select 1,N'd',40 union all
select 2,N'a',50 union all
select 2,N'b',80 union all
select 2,N'c',30 union all
select 2,N'd',80 union all
select 3,N'a',300 union all
select 3,N'b',700 union all
select 3,N'c',700 union all
select 3,N'd',700
Go

select [id],(select top 1 [type] from #T where [id]=a.[id] order by [type] desc) [type],max([num])
from #T a
group by [id]

/*
id type num
----------- ---- -----------
1 d 40
2 d 80
3 d 700

（所影响的行数为 3 行）

[xiaoxiao991]

Create table #tb([id] int,[type] varchar(5),[num] int)
Insert #tb
select 1,'a',10 union all
select 1,'b',20 union all
select 1,'c',30 union all
select 1,'d',40 union all
select 2,'a',50 union all
select 2,'b',80 union all
select 2,'c',30 union all
select 2,'d',80 union all
select 3,'a',300 union all
select 3,'b',700 union all
select 3,'c',700 union all
select 3,'d',700

go
select id,max([type]),num from(
select id,[type],num,num1=MAX(num)over(partition by id) from #tb )a where num=num1 group by id,num

[dawogui]

select * from #tb a where not exists(select 1 from #tb where id=a.id and [type]>a.[type])
/*
id          type  num
----------- ----- -----------
1           d     40
2           d     80
3           d     700

(3 行受影响)

*/

[nibin3462]

drop table test
create table test
(
id int not null default(0),
type varchar(60) not null default(''),
num numeric(8) not null default(0),
)

insert into test values (1,'a',10)
insert into test values (1,'b',20)
insert into test values (1,'c',30)
insert into test values (1,'d',40)
insert into test values (2,'a',50)
insert into test values (2,'b',80)
insert into test values (2,'c',30)
insert into test values (2,'d',80)
insert into test values (3,'a',300)
insert into test values (3,'b',700)
insert into test values (3,'c',700)
insert into test values (3,'d',700)

select id,(select top 1 [TYPE] from test where id=a.id and num=a.num order by type desc) as type ,num from (
select max(num) as num,ID from test group by ID )
a

[一个字送]

呵呵 好东西。

[我腫了]

--表有3个字段
--id type num
--1 a 10
--1 b 20
--1 c 30
--1 d 40
--2 a 50
--2 b 80
--2 c 30
--2 d 80
--3 a 300
--3 b 700
--3 c 700
--3 d 700

--请问怎么求得同一个id里面，num为最大的id和type
--当同一个id有复数个同时为最大值的时候，只取type为最大的那个
--上面的数据里想取得的数据是
--1 d 40
--2 d 80
--3 d 700

SELECT id,MAX(type) AS type,num FROM T1 AS t
WHERE NOT EXISTS(SELECT 1 FROM T1 WHERE id=t.id AND num>t.num)
GROUP BY t.id,t.num 

[苦苦的潜行者]

create table t1(id int,type varchar(10),num int)
go
insert t1
select 1, 'a', 10 union all
select 1, 'b', 20 union all
select 1, 'c', 30 union all
select 1, 'd', 40 union all
select 2, 'a', 50 union all
select 2, 'b', 80 union all
select 2, 'c', 30 union all
select 2, 'd', 80 union all
select 3, 'a', 300 union all
select 3, 'b', 700 union all
select 3, 'c', 700 union all
select 3, 'd', 700
go
select * from t1 a 
where not exists (select 1 from t1 where a.id=id and a.num<=num and a.type<type)
/*
id type num
--  --- ---
1	d	40
2	d	80
3	d	700
*/
go
drop table t1

[苦苦的潜行者]

晕,我的22楼有错误,修正.

create table t1(id int,type varchar(10),num int)
go
insert t1
select 1, 'a', 10 union all
select 1, 'b', 20 union all
select 1, 'c', 30 union all
select 1, 'd', 40 union all
select 2, 'a', 50 union all
select 2, 'b', 80 union all
select 2, 'c', 30 union all
select 2, 'd', 80 union all
select 3, 'a', 300 union all
select 3, 'b', 700 union all
select 3, 'c', 700 union all
select 3, 'd', 700
go
select id,max(type) as type,num from t1 a 
where not exists (select 1 from t1 where a.id=id and a.num<num)
group by id,num
/*
id type num
--  --- ---
1	d	40
2	d	80
3	d	700
*/
go
drop table t1

[kiss_vicente]

[Quote=引用 8 楼 myhaikuotiankong 的回复:]

引用 7 楼 a544589668 的回复:

select * from Ta t where not exists (select 1 from Ta where id=t.id and (num>t.num or num=t.num and type>t.type))
[/Quote]
+1

[zjl8008]

[Quote=引用 4 楼 roy_88 的回复:]
SQL code
--> --> (Roy)生成測試數據

if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[type] nvarchar(1),[num] int)
Insert #T
select 1,N'a',10 union all
select 1……
[/Quote]
非常好的方法，学习了。。

[q806294478]

Create table #tb([id] int,[type] varchar(5),[num] int)
Insert #tb
select 1,'a',10 union all
select 1,'b',20 union all
select 1,'c',30 union all
select 1,'d',40 union all
select 2,'a',50 union all
select 2,'b',80 union all
select 2,'c',30 union all
select 2,'d',80 union all
select 3,'a',300 union all
select 3,'b',700 union all
select 3,'c',700 union all
select 3,'d',700

go
select id,max([type]),num from(
select id,[type],num,num1=MAX(num)over(partition by id) from #tb )a where num=num1 group by id,num

[勿勿]

+1
[Quote=引用 4 楼 roy_88 的回复:]
SQL code
--> --> (Roy)生成測試數據

if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[type] nvarchar(1),[num] int)
Insert #T
select 1,N'a',10 union all
select 1……
[/Quote]