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=1rum 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)
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=1rum 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
2. 如果你的存入日期字段,没有具体的时间,也就是只有哪天,没有几点,可以根据表上的自增列来达到目的。如下代码select * from mytable where id in (select max(id) from mytable group by fdate)