求一条简单SQL语句

[zedan]

表：
id type data
1 1 a
2 2 b
3 1 c
4 1 d
5 2 e

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

谢谢。

[RPFly]

Select type ,max(id) as ID
From 表
Group by type

[xiao_jun_0820]

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

[bkhm07]

8楼正解

[hyde100]

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 
)
结果：
ｉｄ　ｔｙｐｅ　ｄａｔａ
－－－－－－－－－－－－
4	1	d
5	2	e

思路：
１）先确定在相同的ｔｙｐｅ下进行比较，既t2.[type]= t1.[type]的条件。
２）相同的ｔｙｐｅ下，不存在该当ｉｄ小于任何ｉｄ的情况（既最大的ｉｄ才不小于相同ｔｙｐｅ下任意ｉｄ，因为它等于最大的ｉｄ）的数据被选出来。条件 not exists．．．t1.id < t2.id 

[幸运的意外]

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

[wer123q]

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)

[zedan]

谢谢大家，很热闹，稍后结贴。

[ljhcy99]

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 为 你原来的表。

你看看阿。

[kingzhongusa]

感觉一句话就行了啊
为什么要那么麻烦呢？请高手指教
select IDD=max(id),type ,data from 表名 group by type

[linguojin11]

[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

[fzcheng]

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)

[ws_hgo]

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)

[zedan]

需求有所变动，各位再看看。

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

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

谢谢。

[hery2002]

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]

[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下的， 谢谢。

[pt1314917]

[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

[肥龙上天]

select * from tb a where id in (select max(id) from tb group by type  )

[fcuandy]

http://topic.csdn.net/u/20080123/18/9731d130-0d4b-4c11-8d89-f2c3ca331f0c.html

[肥龙上天]

select * from (select *,row_number() over(partition by type order by id desc) as rn from tb ) t where rn in(1,2)

[肥龙上天]

写得快了点，更正下。呵呵
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)