有这样一个表:
ID TradeName DateTime
6 Aade 2009-8-27
12 Aade 2009-8-28
20 aail 2009-8-18
.........................我希望查询DateTime 到2009-8-31 相差2天的记录,并且相同TradeName取日期最大一条记录
怎样写Sql语句?
SELECT *
FROM db_trade WHERE (DATEDIFF(day, CONVERT(DATETIME, datetime, 102), CONVERT(DATETIME,
'2009-8-31', 102)) > 2)这样查询结果与我想的不同
ID TradeName DateTime
6 Aade 2009-8-27
12 Aade 2009-8-28
20 aail 2009-8-18
.........................我希望查询DateTime 到2009-8-31 相差2天的记录,并且相同TradeName取日期最大一条记录
怎样写Sql语句?
SELECT *
FROM db_trade WHERE (DATEDIFF(day, CONVERT(DATETIME, datetime, 102), CONVERT(DATETIME,
'2009-8-31', 102)) > 2)这样查询结果与我想的不同
create table [TB]([ID] int,[TradeName] varchar(4),[DateTime] datetime)
insert [TB]
select 6,'Aade','2009-8-27' union all
select 12,'Aade','2009-8-28' union all
select 20,'aail','2009-8-18'select * from TB T where not exists(select 1 from TB where t.[TradeName]=[TradeName] and t.[DateTime]>[DateTime])
and ABS(datediff(dd,'2009-8-31',[DateTime]))=2
where abs(datediff(dd,[DateTime],'2009-8-31'))>=2
and not exists(
select 1 from tb
where TradeName =t.TradeName
and [DateTime]>t.[DateTime])
还是这样?
select top 1 * from tb t
where abs(datediff(dd,[DateTime],'2009-8-31'))=2
order by DateTime desc
*
from
tb t
where
datetime <= '2009-08-29'
and
TradeName = (select max(TradeName) from tb where datetime <= '2009-08-29'
and
TradeName = t.TradeName)
ID TradeName DateTime
6 Aade 2009-8-27
12 Aade 2009-8-28
20 aail 2009-8-18
......................... 我希望查询DateTime 到2009-8-31 相差大于2天的记录,并且相同TradeName取日期最大一条记录 结果返回这两条数据
12 Aade 2009-8-28
20 aail 2009-8-18
怎样写Sql语句?
go
create table [TB]([ID] int,[TradeName] varchar(4),[DateTime] datetime)
insert [TB]
select 6,'Aade','2009-8-27' union all
select 12,'Aade','2009-8-28' union all
select 12,'A234','2009-8-30' union all
select 23,'qwe','2009-9-1' union all
select 20,'aail','2009-8-18'select * from tb a
where
not exists (select 1 from tb where TradeName=a.TradeName and id>a.id )
and abs(datediff(dd,[DateTime],'2009-8-31'))>2
/*
ID TradeName DateTime
----------- --------- -----------------------
12 Aade 2009-08-28 00:00:00.000
20 aail 2009-08-18 00:00:00.000(2 行受影响)
*/
if object_id('[TB]') is not null drop table [TB]
GO
create table [TB]([ID] int,[TradeName] varchar(4),[DateTime] datetime)
insert [TB]
select 6,'Aade','2009-08-27' union all
select 12,'Aade','2009-08-28' union all
select 20,'aail','2009-08-18'
select * from tb T
WHERE NOT EXISTS(SELECT * FROM TB WHERE [TradeName]=T.[TradeName] AND [DateTime]>T.[DateTime])
AND datediff(day,[DateTime],'2009-08-31')>2ID TradeName DateTime
----------- --------- -----------------------
12 Aade 2009-08-28 00:00:00.000
20 aail 2009-08-18 00:00:00.000(2 行受影响)
select * from tb a
where
not exists (select 1 from tb where TradeName=a.TradeName and [DateTime]>a.[DateTime] )
and abs(datediff(dd,[DateTime],'2009-8-31'))>2
go
create table [TB]([ID] int,[TradeName] varchar(4),[DateTime] datetime)
insert [TB]
select 6,'Aade','2009-8-27' union all
select 12,'Aade','2009-8-28' union all
select 12,'A234','2009-8-30' union all
select 23,'qwe','2009-9-1' union all
select 20,'aail','2009-8-18'select * from tb t
where abs(datediff(dd,[DateTime],'2009-8-31'))>=2
and not exists(
select 1 from tb
where TradeName =t.TradeName
and [DateTime]>t.[DateTime])
/*
ID TradeName DateTime
12 Aade 2009-08-28 00:00:00.000
20 aail 2009-08-18 00:00:00.000
*/偶也借下测试数据测试一下.