求教

[无心雨云]

表结构
id 关联ID 内容
1 1 0000d
2 1 11111g
3 1 22222e
4 2 0000w
5 2 111112
6 2 222223
7 3 0000w
8 4 11111e
9 4 22222w
10 5 22222d

想得到
3 1 22222e
6 2 222223
7 3 0000w
9 4 22222w
10 5 22222d

[水族杰纶]

select *
from tb t
where ID=(select max(ID)
          from tb
          where 关联ID=t.关联ID)

[liyaohui13098452233]

呵呵……

[水族杰纶]

--> --> (wufeng4552)生成測試數據
 
if not object_id('tb') is null
	drop table tb
Go
Create table tb([id] int,[关联ID] int,[内容] nvarchar(6))
Insert tb
select 1,1,N'0000d' union all
select 2,1,N'11111g' union all
select 3,1,N'22222e' union all
select 4,2,N'0000w' union all
select 5,2,N'111112' union all
select 6,2,N'222223' union all
select 7,3,N'0000w' union all
select 8,4,N'11111e' union all
select 9,4,N'22222w' union all
select 10,5,N'22222d'
Go
select *
from tb t
where ID=(select max(ID)
          from tb
          where 关联ID=t.关联ID)
order by 关联ID
/*
id          关联ID        内容
----------- ----------- ------
3           1           22222e
6           2           222223
7           3           0000w
9           4           22222w
10          5           22222d

(5 個資料列受到影響)

*/

[黄_瓜]

if not object_id('tb') is null
    drop table tb
Go
Create table tb([id] int,[关联ID] int,[内容] nvarchar(6))
Insert tb
select 1,1,N'0000d' union all
select 2,1,N'11111g' union all
select 3,1,N'22222e' union all
select 4,2,N'0000w' union all
select 5,2,N'111112' union all
select 6,2,N'222223' union all
select 7,3,N'0000w' union all
select 8,4,N'11111e' union all
select 9,4,N'22222w' union all
select 10,5,N'22222d'
Go
select *
from tb a
where not exists(select 1
          from tb
          where 关联ID=a.关联ID and [id]>a.[id])
/*
id          关联ID        内容
----------- ----------- ------
3           1           22222e
6           2           222223
7           3           0000w
9           4           22222w
10          5           22222d

(5 行受影响)


*/

[shixixi1987]

select * from 表名 where id in( select max(id) id from 表名 group by 关联ID)

[shixixi1987]

if not object_id('tb') is null
drop table tb
Go
Create table tb([id] int,[关联ID] int,[内容] nvarchar(6))
Insert tb
select 1,1,N'0000d' union all
select 2,1,N'11111g' union all
select 3,1,N'22222e' union all
select 4,2,N'0000w' union all
select 5,2,N'111112' union all
select 6,2,N'222223' union all
select 7,3,N'0000w' union all
select 8,4,N'11111e' union all
select 9,4,N'22222w' union all
select 10,5,N'22222d'
Go
select * from tb
go
select * from 表名 where id in( select max(id) id from 表名 group by 关联ID)
go

[给我力量]

select * from table where id in(select max(id) from table group by 关联id )