100分，求SQL,急！！！

2012-01-06 07:57:14 247 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

vovo2000 墨衍会员 · AI 创作全网分发关注 + 加入社区

表有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

跪求。急阿

...全文

2022网安保研经验帖 2022网安保研经验阅读详情

vovo2000

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

勿勿 2012-01-07

打赏
举报

+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]

q806294478 2012-01-07

打赏
举报

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

zjl8008 2012-01-07

打赏
举报

[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]
非常好的方法，学习了。。

kiss_vicente 2012-01-07

打赏
举报

[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

苦苦的潜行者 2012-01-07

打赏
举报

晕,我的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

苦苦的潜行者 2012-01-07

打赏
举报



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

我腫了 2012-01-07

打赏
举报



--表有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

一个字送 2012-01-07

打赏
举报

呵呵好东西。

nibin3462 2012-01-07

打赏
举报

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

dawogui 2012-01-07

打赏
举报

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 行受影响)



*/

xiaoxiao991 2012-01-07

打赏
举报

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

dongsheng10101 2012-01-07

打赏
举报

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 行）

陈永富 2012-01-07

打赏
举报

[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]
强大的开窗函数

Alessandro_ 2012-01-07

打赏
举报

[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]

myhaikuotiankong 2012-01-06

打赏
举报

[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]++

可乐AND爆米花 2012-01-06

打赏
举报

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))

dawugui 2012-01-06

打赏
举报

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 行）

*/

dawugui 2012-01-06

打赏
举报

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)))

中国风 2012-01-06

打赏
举报

--> --> (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

*/

开着拖拉机泡妞 2012-01-06

打赏
举报

/*
表有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
*/

加载更多回复（2）

100分，求SQL,急！！！

相关推荐