Home >Database >Mysql Tutorial > 查询sql语句

查询sql语句

WBOY
WBOYOriginal
2016-06-07 17:39:35998browse

usedb go createtabletablel ( idintidentity(1,1)notnull, propertyvarchar(20)notnull, datedatetimenotnull ) go insertintotablelvalues('CSD001','2012-12-12:12:10') insertintotablelvalues('CSD001','2012-12-12:12:13') insertintotablelvalues('CS

use db

go

 

create table tablel

(

 id int identity(1,1) not null,

 property varchar(20) not null,

 date datetime not null

)

go

 

insert into tablel values('CSD001','2012-12-12:12:10')

insert into tablel  values('CSD001','2012-12-12:12:13')

insert into tablel  values('CSD001','2012-12-12:12:14')

insert into tablel values('CSD001','2012-12-12:12:16')

 

insert into tablel values('CSD002','2012-12-12 12:13:14')

insert into tablel values('CSD002','2012-12-12 12:13:16')

insert into tablel values('CSD002','2012-12-12 12:13:18')

insert into tablel values('CSD002','2012-12-12 12:13:45)

 

insert into tablel values('CSD003','2012-12-12 12:13:14')

insert into tablel values('CSD003','2012-12-12 12:13:16')

insert into tablel values('CSD003','2012-12-12 12:13:18')

insert into tablel values('CSD003','2012-12-12 12:13:45)

 

insert into tablel values('CSD004,'2012-12-12 12:13:14')

insert into tablel values('CSD004','2012-12-12 12:13:16')

insert into tablel values('CSD004','2012-12-12 12:13:18')

insert into tablel values('CSD004','2012-12-12 12:13:45)

 

查询每个属性最小两个值;

 第一种方法为:

Select * from (select row_number() over(patittion  by property order by a.date asc) as rowIndex, * from tablel as a where property in ( select distict(property)  from tablel )) t where rowIndex

 

 第二种方法为:

       或者 select * from tablel as a where property in (select top 2 Property from tablel  where a.property=property order by a.date asc)

,香港虚拟主机,香港虚拟主机,香港虚拟主机
Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn