求一条简单SQL语句

zedan 2008-10-13 12:54:54
表:
id type data
1 1 a
2 2 b
3 1 c
4 1 d
5 2 e

查询返回二条数据,每种type下id最大的各一条。

谢谢。
...全文
191 点赞 收藏 27
写回复
27 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
肥龙上天 2008-10-15

写得快了点,更正下。呵呵
select id,type,data from (select *,row_number() over(partition by type order by id desc) as rn from tb ) t where rn in(1,2)
回复
肥龙上天 2008-10-15
select * from (select *,row_number() over(partition by type order by id desc) as rn from tb ) t where rn in(1,2)
回复
fcuandy 2008-10-15
http://topic.csdn.net/u/20080123/18/9731d130-0d4b-4c11-8d89-f2c3ca331f0c.html
回复
肥龙上天 2008-10-15
select * from tb a where id in (select max(id) from tb group by type  )
回复
pt1314917 2008-10-15
[Quote=引用 20 楼 zedan 的回复:]
需求有所变动,各位再看看。

表:
id type data
1 1 a
2 2 b
3 1 c
4 1 d
5 2 e
6 2 f

查询返回每种type下id最大的各二条。

谢谢。
[/Quote]


select * from 表 a where (select count(id) from 表 where type=a.type and id>a.id)<2

回复
zedan 2008-10-15
[Quote=引用 21 楼 hery2002 的回复:]
SQL codedeclare@tb1table(
idint,[type]int,
datachar(1)
)insertinto@tb1select1,1,'a'unionallselect3,1,'c'unionallselect4,1,'d'unionallselect2,2,'b'unionallselect5,2,'e'select[id],[type],[data]from(selectrow_number()over(partitionby[type]orderbyiddesc,[type])aspx ,*from@tb1) twherepx<=2/*id type data
----------- ----------- ----
4 1 d
3 1 …
[/Quote]

over? 2005下的? 我需要在2000和Access下的, 谢谢。
回复
hery2002 2008-10-15
declare @tb1 table
(
id int,
[type] int,
data char(1)
)

insert into @tb1
select 1,1,'a' union all
select 3,1,'c' union all
select 4,1,'d' union all
select 2,2,'b' union all
select 5,2,'e'

select [id],[type],[data] from (
select row_number() over(partition by [type] order by id desc,[type]) as px ,* from @tb1) t
where px<=2
/*
id type data
----------- ----------- ----
4 1 d
3 1 c
5 2 e
2 2 b
*/
回复
zedan 2008-10-15
需求有所变动,各位再看看。

表:
id type data
1 1 a
2 2 b
3 1 c
4 1 d
5 2 e
6 2 f

查询返回每种type下id最大的各二条。

谢谢。
回复
ws_hgo 2008-10-14
create table #DD
(
id int identity(1,1),
type int,
data char(1)
)

insert #DD select 1,'a'
union all select 2,'b'
union all select 1,'c'
union all select 1,'d'
union all select 2,'e'

select * from #DD DD where not exists (select * from #DD where DD.type=type and dd.id<id)
回复
fzcheng 2008-10-14

DECLARE @t TABLE(id INT,type INT,date NVARCHAR(10))
INSERT INTO @t
SELECT 1,1,'a' UNION ALL
SELECT 2,2,'b' UNION ALL
SELECT 3,1,'c' UNION ALL
SELECT 4,1,'d' UNION ALL
SELECT 5,2,'e'

SELECT * FROM @t a WHERE NOT EXISTS(SELECT * FROM @t WHERE type=a.type AND id>a.id)
回复
linguojin11 2008-10-14
[Quote=引用 16 楼 kingzhongusa 的回复:]
感觉一句话就行了啊
为什么要那么麻烦呢?请高手指教
select IDD=max(id),type ,data from 表名 group by type
[/Quote]
DATA没在聚合函数中
select a.*
from mm a right join (select max(id) id,type from mm group by type) b
on a.type=b.type and a.id=b.id
回复
kingzhongusa 2008-10-14
感觉一句话就行了啊
为什么要那么麻烦呢?请高手指教
select IDD=max(id),type ,data from 表名 group by type
回复
ljhcy99 2008-10-13

select B.ID,B.type,A.data
from table as A,
(select max(id) as ID,type
from table
group by type) AS B

where A.id=B.ID
and A.type=B.type

table 为 你原来的表。

你看看阿。
回复
zedan 2008-10-13
谢谢大家,很热闹,稍后结贴。
回复
wer123q 2008-10-13

declare @t table
(
id int identity(1,1),
type int,
data char(1)
)

insert @t select 1,'a'
union all select 2,'b'
union all select 1,'c'
union all select 1,'d'
union all select 2,'e'

select * from @t where id in( select max(ID) from @t group by type)
回复
幸运的意外 2008-10-13
2000中可以这样:
select * from 表
where
id in (select max(id) from 表 group by type)

2005中就简单了:
先通过表值添加一个标志字段,而后再选出
with cte as
(select
*,
row_num = row_number() over(partition by type order by id desc)
from 表
)

select id,type,data
from cte
where row_num = 1
回复
hyde100 2008-10-13

declare @tb1 table
(
id int,
[type] int,
data char(1)
)

insert into @tb1
select 1,1,'a' union all
select 3,1,'c' union all
select 4,1,'d' union all
select 2,2,'b' union all
select 5,2,'e'

select
id,
[type],
data
from @tb1 as t1
where not exists
(
select 1
from @tb1 as t2
where t2.[type]= t1.[type] and t1.id < t2.id
)
结果:
id type data
------------
4 1 d
5 2 e

思路:
1)先确定在相同的type下进行比较,既t2.[type]= t1.[type]的条件。
2)相同的type下,不存在该当id小于任何id的情况(既最大的id才不小于相同type下任意id,因为它等于最大的id)的数据被选出来。条件 not exists...t1.id < t2.id
回复
bkhm07 2008-10-13
8楼正解
回复
xiao_jun_0820 2008-10-13
select * from #tmp b
where exists
(select max(a.id),a.type from #tmp a group by a.type having b.id=max(a.id));
回复
RPFly 2008-10-13
Select type ,max(id) as ID
From 表
Group by type

回复
相关推荐
发帖
应用实例
创建于2007-09-28

2.7w+

社区成员

MS-SQL Server 应用实例
申请成为版主
帖子事件
创建了帖子
2008-10-13 12:54
社区公告
暂无公告