一天内有几条数据 怎么获取最新的那条 比如一共30天 怎么获取30天中所有对应一天中的最新的数据,新手求帮助
------解决方案--------------------
create table #temp
(
id int,
Creatdate datetime
)
insert into #temp
select 1,'2014-5-1 00:00:01'
union all
select 2,'2014-5-1 00:00:02'
union all
select 3,'2014-5-2 00:00:01'
union all
select 4,'2014-5-2 00:00:02'
select * from #temp
select * from
(
select ROW_NUMBER() over(partition by cast(Creatdate as varchar(10)) order by Creatdate) as rum ,
id,Creatdate from #temp
) t where t.rum=1
rum id Creatdate
-------------------- ----------- -----------------------
1 1 2014-05-01 00:00:01.000
1 3 2014-05-02 00:00:01.000
(2 行受影响)
------解决方案--------------------
select * from
(select *,
row_number() over(partition by convert(varchar,[日期时间字段],23)
order by [日期时间字段] desc) 'rn'
from [表名]) t
where t.rn=1
------解决方案--------------------
总之你得有个规律可以看出一天中哪些记录是先插入而哪些记录是后插入的，跟你的表的设计有关。。。
------解决方案--------------------
select * from tab a
where not exists(select * from tab where convert(varchar(10),sdate,111)= convert(varchar(10),a.sdate,111)
and sdate>a.sdate )
------解决方案--------------------
USE test
go
CREATE TABLE t (dt DATETIME,dd VARCHAR(100))
INSERT INTO t
SELECT '2014-05-11 21:02:49.733','ddd' UNION ALL
SELECT '2014-05-11 16:02:49.733','ddfdf' UNION ALL
SELECT '2014-05-11 05:02:49.733','ddeed' UNION ALL
SELECT '2014-05-02 14:02:49.733','ddsfasdf' UNION ALL
SELECT '2014-05-02 11:02:49.733','dfasdf' UNION ALL
SELECT '2014-05-02 19:02:49.733','ddsafew3d' UNION ALL
SELECT '2014-05-03 15:02:49.733','decxzc' UNION ALL
SELECT '2014-05-05 12:02:49.733','d343ysadu' UNION ALL
SELECT '2014-05-07 11:02:49.733','dsdaflr654aw' UNION ALL
SELECT '2014-05-01 10:02:49.733','d23t53ydasfd'
;WITH t1 AS
(SELECT ROW_NUMBER() OVER(PARTITION BY CONVERT(varchar(100), dt, 23) ORDER BY dt ) AS id ,CONVERT(varchar(100), dt, 23)AS dt1,* FROM t )
SELECT dt,dd FROM t1
WHERE NOT EXISTS (SELECT id FROM t1 AS a WHERE a.dt1=t1.dt1 AND a.id>t1.id)
DROP TABLE t
------解决方案--------------------
1. 用分析函数，楼上给的答案比较明确了。
2. 如果你的存入日期字段，没有具体的时间，也就是只有哪天，没有几点，可以根据表上的自增列来达到目的。如下代码
select * from mytable where id in (select max(id) from mytable group by fdate)