表中有一个日期字段,记录的查询是按月份来查的,比如2010-07是查询7月份所有的数据现在的情况是该表中的记录不是按自然月添加的,比如现在是08月,表中的记录可能还是6月份的怎么才能取得6月份的数据?但然不能以2010-06做为条件,因为是不知道是不是有06月分的数据,有可能是5月,还有可能是7月如:
id date
1 2010-01-01
2 2010-02-01
3 2010-02-03
4 2010-03-20
5 2010-03-22怎么才取得3月份的数据?
id date
1 2010-01-01
2 2010-02-01
3 2010-02-03
4 2010-03-20
5 2010-03-22怎么才取得3月份的数据?
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([id] int,[date] datetime)
insert [TB]
select 1,'2010-01-01' union all
select 2,'2010-02-01' union all
select 3,'2010-02-03' union all
select 4,'2010-03-20' union all
select 5,'2010-03-22'
GO--> 查询结果
SELECT top 1 * FROM [TB]
order by date desc
--> 删除表格
--DROP TABLE [TB]
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([id] int,[date] datetime)
insert [TB]
select 1,'2010-01-01' union all
select 2,'2010-02-01' union all
select 3,'2010-02-03' union all
select 4,'2010-03-20' union all
select 5,'2010-03-22'
GOselect * from tb where convert(char(7),date,120)
=(select convert(char(7),max(date),120) from tb)id date
4 2010-03-20 00:00:00.000
5 2010-03-22 00:00:00.000
insert [TBH]
select 1,'2010-01-01' union all
select 2,'2010-02-01' union all
select 3,'2010-02-03' union all
select 4,'2010-03-20' union all
select 5,'2010-03-22'
GOSELECT T.*
FROM TBH t
LEFT JOIN
(SELECT CONVERT(NVARCHAR(6),MAX(CONVERT(nvarchar(12),dateID,112))) AS ID FROM TBH H)L
ON CHARINDEX(L.ID,CONVERT(NVARCHAR(12),T.dateID,112))>0
WHERE LEN(L.ID)>0id dateID
----------- -----------------------
4 2010-03-20 00:00:00.000
5 2010-03-22 00:00:00.000(2 row(s) affected)
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([id] int,[date] datetime)
insert [TB]
select 1,'2010-01-01' union all
select 2,'2010-02-01' union all
select 3,'2010-02-03' union all
select 4,'2010-03-20' union all
select 5,'2010-03-22'
GOselect * from tb
go
select top 1 * from tb
order by date desc