请教一条SQL语句，急急急，请高手指点

2011-11-19 10:33:31 103 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

控制障碍函数(Control Barrier Function,CBF) 一、定义介绍控制障碍函数阅读详情

数据表

keyId typeId userName addYear addMonth
1 0001 小张 2010 9
2 0001 小李 2011 8
3 0002 小王 2008 7
4 0002 小赵 2006 6
5 0003 小刘 2005 4

要求取得每个分类里面注册时间最晚的记录，也就是说取得每个分类里面，年份最近，月份最近的记录集，希望的查询结果是
keyId typeId userName addYear addMonth

2 0001 小李 2011 8
3 0002 小王 2008 7
5 0003 小刘 2005 4

...全文

ArcGIS中根据DEM和地形起伏度进行地貌分类划分大家好我是GIS蒲公英，数字高程模型(digital elevation model, DEM)经常都被用到专题地图的底图中，下面来我们来交流一下仅选择海拔和地形起伏度两个指标，用DEM来进行地貌类型的自动判别划分阅读详情

jkjj20

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

+ 关注 + 加入社区

切换为时间正序

苦苦的潜行者 2011-11-19

打赏
举报

[Quote=引用 14 楼 xiaolinyouni 的回复:]

SQL code
/*
(苦苦的潜行者)
*/
if object_id('t1') is not null
drop table t1
go
create table t1(keyid int,typeid varchar(10),username varchar(10),addyear varchar(10),addmonth varchar(10))
go
insert t1
selec……
[/Quote]
将convert(varchar(5)..)换成convert(varchar(6)..)不然当月份格式为双数时会出错.

苦苦的潜行者 2011-11-19

打赏
举报

/*

(苦苦的潜行者)

*/

if object_id('t1') is not null

drop table t1

go

create table t1(keyid int,typeid varchar(10),username varchar(10),addyear varchar(10),addmonth varchar(10))

go

insert t1

select 1,'0001','小张','2010','9' union all

select 2,'0001','小李','2011','8' union all

select 3,'0002','小王','2008','7' union all

select 4,'0002','小赵','2006','6' union all

select 5,'0005','小刘','2005','4'

go

select * from t1 a where

not exists (select 1 from t1 

where a.typeid=typeid

and convert(varchar(5),a.addyear+'-'+a.addmonth,120)<convert(varchar(5),addyear+'-'+addmonth,120)

)

/*

（所影响的行数为 3 行）

keyid  typeid username addyear addmonth

----    ----    ------  -----  ----

2	0001	小李	2011	8

3	0002	小王	2008	7

5	0005	小刘	2005	4

*/

go

drop table t1

中国风 2011-11-19

打赏
举报

再贴一方法

SQL05以上版本可这样用



select *

from (select *,row_number()over(partition by [typeId] order by [addYear] desc,[addMonth] desc) as row from #T a)T 

where row=1

ww221216 2011-11-19

打赏
举报

select keyId,typeId,userName,addYear,addMonth
from tb
where addYear in(select typeId,max(addYear) from tb group by typeId);

中国风 2011-11-19

打赏
举报

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

6楼最后少个括号，大版你真快啊！纠结死这里的网速。。。
[/Quote]

ww221216 2011-11-19

打赏
举报

select keyId,typeId,userName,addYear,addMonth
from tb
where addYear in(select typeId,max(addYear) from tb group by typeId);

中国风 2011-11-19

打赏
举报

其它方法参照
http://topic.csdn.net/u/20080626/00/43d0d10c-28f1-418d-a05b-663880da278a.html

AcHerat 元老 2011-11-19

打赏
举报

6楼最后少个括号，大版你真快啊！纠结死这里的网速。。。

中国风 2011-11-19

打赏
举报

取keyId大小时



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

 

if not object_id('Tempdb..#T') is null

	drop table #T

Go

Create table #T([keyId] int,[typeId] nvarchar(4),[userName] nvarchar(2),[addYear] int,[addMonth] int)

Insert #T

select 1,N'0001',N'小张','2010',9 union all

select 2,N'0001',N'小李','2011',8 union all

select 3,N'0002',N'小王','2008',7 union all

select 4,N'0002',N'小赵','2006',6 union all

select 5,N'0003',N'小刘','2005',4

Go

Select * from #T as a 

where not exists(select 1 from #T where  [typeId]=a.[typeId] and [keyId]>a.[keyId])



/*

keyId	typeId	userName	addYear	addMonth

2	0001	小李	2011	8

4	0002	小赵	2006	6

5	0003	小刘	2005	4

*/

AcHerat 元老 2011-11-19

打赏
举报



select *

from tb t

where not exists (select 1 from tb where typeid = t.typeid and (addyear>t.addyear or (addyear = t.addyear and addmonth > t.addmonth))

中国风 2011-11-19

打赏
举报

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

 

if not object_id('Tempdb..#T') is null

	drop table #T

Go

Create table #T([keyId] int,[typeId] nvarchar(4),[userName] nvarchar(2),[addYear] int,[addMonth] int)

Insert #T

select 1,N'0001',N'小张','2010',9 union all

select 2,N'0001',N'小李','2011',8 union all

select 3,N'0002',N'小王','2008',7 union all

select 4,N'0002',N'小赵','2006',6 union all

select 5,N'0003',N'小刘','2005',4

Go

Select * from #T as a 

where not exists(select 1 from #T where  [typeId]=a.[typeId] and [addYear]>a.[addYear] or([addYear]=a.[addYear] and [addMonth]>a.[addMonth]))



/*

keyId	typeId	userName	addYear	addMonth

2	0001	小李	2011	8

3	0002	小王	2008	7

5	0003	小刘	2005	4

*/

jkjj20 2011-11-19

打赏
举报

这样不对哦，对于一个分类我只取一条记录，如果我的数据里面有多条同年份的记录你这个就搞不定了

pengxuan 2011-11-19

打赏
举报



if object_id('tb','U') is not null

   drop table tb

go

create table tb

(

 keyId int identity(1,1),

 typeId varchar(10),

 userName varchar(10),

 addYear int,

 addMonth int

)

go

insert into tb (typeId,userName,addYear,addMonth)

select '0001','小张',2010,9 union all

select '0001','小李',2011,8 union all

select '0002','小王',2008,7 union all

select '0002','小赵',2006,6 union all

select '0003','小刘',2005,4

go

select * from tb a where not exists(select 1 from tb where typeId=a.typeId and cast(cast(addYear as varchar)+'-'+cast(addMonth as varchar)+'-01' as datetime)>cast(cast(a.addYear as varchar)+'-'+cast(a.addMonth as varchar)+'-01' as datetime))

go

/*

keyId       typeId     userName   addYear     addMonth

----------- ---------- ---------- ----------- -----------

2           0001       小李         2011        8

3           0002       小王         2008        7

5           0003       小刘         2005        4



(3 行受影响)





*/

hllfl 2011-11-19

打赏
举报

select keyId,typeId,userName,addYear,addMonth

from tb 

where addYear in(select typeId,max(addYear) from tb group by typeId);

hllfl 2011-11-19

打赏
举报

select keyId,typeId,userName,addYear,addMonth

from tb 

where addYear in(select max(addYear) from tb);

请教一条SQL语句，急急急，请高手指点

相关推荐