有点难？查出数据集中最新七天的数据...

[jonas401]

有一表A，其中一字段v_time为datetime型,包含时分秒
开始时间未定，从数据库提取出最新七天的数据

select top 7 * from(

select distinct(
convert( datetime,

( Cast( Year( v_time ) as char(12) )　　　--得出年
+ '-' +
Cast( Month( v_time ) as char(12) ) --得出月
+ '-' +
Cast( Day( v_time ) as char(12) ) -- 得出日
)

) --用convert转回datetime型
) --用distinct去掉重复
as v_temp

from A

) as temp order by v_temp DESC

这段查询已经能查出结果
寻求改进
up前分

[jonas401]

排了一下格式

select top 7 * from(

select distinct(
convert( datetime,

( Cast( Year( v_time ) as char(12) )　　　--得出年
+ '-' +
Cast( Month( v_time ) as char(12) ) --得出月
+ '-' +
Cast( Day( v_time ) as char(12) ) -- 得出日
)

) --用convert转回datetime型
) --用distinct去掉重复

as v_temp from A

) as temp order by v_temp DESC

[solidpanther]

select * from tablename where datediff(day,v_time ,getdate())<=7

[jonas401]

要求改了，改成，要查出前七天的这个时间段，提取起始时间就行了

[lsxaa]

select *
from a
where datediff(day,v_time ,getdate())<=7

[jonas401]

to solidpanther(╃╄╃我爱机器猫╄╃╄) :
起始时间未定，不一定是getdate()
要由数据库中的记录定

[lsxaa]

select 起始时间
from a
where datediff(day,v_time ,getdate())<=7

[jonas401]

都没理解题义，由数据库中的记录来决定前七天的起跟终时间

[jonas401]

晕哦，是我写不清楚，还是。。。

[solidpanther]

select t1.* from tablename t1,
(select min(v_time)as minv_time from tablename) t2
where datediff(day,v_time ,t2.minv_time)<=7

[pbsql]

最新7天的数据：
select * from t a
where not exists(select * from t where datediff(day,a.v_time,v_time)>=7)

[solidpanther]

sorry,前七天的：
select t1.* from tablename t1,
(select min(v_time)as minv_time from tablename) t2
where datediff(day,t2.minv_time,v_time )<=7
sorry,后七天的：
select t1.* from tablename t1,
(select max(v_time)as maxv_time from tablename) t2
where datediff(day,v_time ,t2.maxv_time)<=7

[jonas401]

也不对，
我说的七天不是说距最新一条记录的七天之内
而是指，比如说
9 8 6 5 3 2 1
要有个单位
而不说从9开始
9 > x >2 的记录

[jonas401]

各位老大还明白否？

[jonas401]

漏了一下字

我说的七天不是说距最新一条记录的七天之内
而是指，比如说
9 8 6 5 3 2 1
要有7个单位
而不说从9开始
9 > x >2 的记录

[pbsql]

那是这个意思了：
select * from t
where convert(varchar(10),v_time,120)
in (select top 7 convert(varchar(10),v_time,120) v_time
from t order by convert(varchar(10),v_time,120))

[pbsql]

少了点东东：
select * from t
where convert(varchar(10),v_time,120)
in (select distinct top 7 convert(varchar(10),v_time,120) v_time
from t order by convert(varchar(10),v_time,120) desc)

[pbsql]

或者：
select * from t
where convert(varchar(10),v_time,120)
in (select top 7 convert(varchar(10),v_time,120) v_time
from t group by convert(varchar(10),v_time,120)
order by convert(varchar(10),v_time,120) desc)

[jonas401]

差不多了，比我的好多了，可能就是这样了

[jonas401]

再加上个去掉重复值就行了

结帖