select top 3 convert(varchar, addtime, 102) from info2 group by convert(varchar, addtime, 102) order by convert(varchar, addtime, 102) desc
---------------------------------------------------------------- -- Author :fredrickhu(小F 向高手学习) -- Date :2009-06-13 16:05:00 ---------------------------------------------------------------- --> 测试数据:@info2 declare @info2 table([addtime] datetime) insert @info2 select '2008-8-3' union all select '2008-8-1' union all select '2008-8-2' union all select '2008-8-1' union all select '2008-8-3' union all select '2008-8-2' union all select '2008-8-1' union all select '2008-8-4' --------------开始查询--------------------------SELECT TOP 3 convert(char(10),addtime,120) addtime FROM (SELECT DISTINCT * FROM @info2)AS T ORDER BY addtime DESC ----------------结果---------------------------- /*addtime 2008-08-04 2008-08-03 2008-08-02 */
DECLARE @TB table(date smalldatetime) insert @TB SELECT '2008-8-3' union all SELECT '2008-8-1' union all SELECT '2008-8-2' union all SELECT '2008-8-1' union all SELECT '2008-8-3' union all SELECT '2008-8-2' union all SELECT '2008-8-1' union all SELECT '2008-8-4' select top 3 date from @TB group by date order by date desc date ----------------------- 2008-08-04 00:00:00 2008-08-03 00:00:00 2008-08-02 00:00:00(3 行受影响)
DECLARE @TB table(date smalldatetime) insert @TB SELECT '2008-8-3 16:47:15 ' union all SELECT '2008-8-1 16:06:46 ' union all SELECT '2008-8-2 12:45:13 ' union all SELECT '2008-8-1 17:39:05 ' union all SELECT '2008-8-3 17:39:06 ' union all SELECT '2008-8-2 17:39:35 ' union all SELECT '2008-8-1 17:39:25 ' union all SELECT '2008-8-4 17:39:15 ' select top 3 date from @TB group by date order by date desc date ----------------------- 2008-08-04 17:39:00 2008-08-03 17:39:00 2008-08-03 16:47:00(3 行受影响)
select top 3 date from (SELECT date=convert(char(10),date,120) from @TB) A group by date order by date desc date ---------- 2008-08-04 2008-08-03 2008-08-02(3 行受影响)
addtime +3天 和当前系统时间比较
请问,我想求出最近三天的日期, 2009-6-12 16:47:15 2009-6-12 16:06:46 2009-6-12 12:45:13 2009-6-11 17:39:05 2009-6-9 19:27:47 2009-6-2 15:18:27 我想求出最近三天的日期 如 select top 3 addtime from info2 group by addtime order by addtime desc 这条语句把重复的日期也求出来了 select * from @tb where addtime>dateadd(dd,-3,getdate())
select * from @tb where addtime>=dateadd(dd,-3,getdate())
from info2
group by convert(varchar, addtime, 102)
order by convert(varchar, addtime, 102) desc
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-06-13 16:05:00
----------------------------------------------------------------
--> 测试数据:@info2
declare @info2 table([addtime] datetime)
insert @info2
select '2008-8-3' union all
select '2008-8-1' union all
select '2008-8-2' union all
select '2008-8-1' union all
select '2008-8-3' union all
select '2008-8-2' union all
select '2008-8-1' union all
select '2008-8-4'
--------------开始查询--------------------------SELECT TOP 3 convert(char(10),addtime,120) addtime FROM (SELECT DISTINCT * FROM @info2)AS T ORDER BY addtime DESC
----------------结果----------------------------
/*addtime
2008-08-04
2008-08-03
2008-08-02
*/
insert @TB
SELECT '2008-8-3' union all
SELECT '2008-8-1' union all
SELECT '2008-8-2' union all
SELECT '2008-8-1' union all
SELECT '2008-8-3' union all
SELECT '2008-8-2' union all
SELECT '2008-8-1' union all
SELECT '2008-8-4'
select top 3 date
from @TB
group by date
order by date desc date
-----------------------
2008-08-04 00:00:00
2008-08-03 00:00:00
2008-08-02 00:00:00(3 行受影响)
insert @TB
SELECT '2008-8-3 16:47:15 ' union all
SELECT '2008-8-1 16:06:46 ' union all
SELECT '2008-8-2 12:45:13 ' union all
SELECT '2008-8-1 17:39:05 ' union all
SELECT '2008-8-3 17:39:06 ' union all
SELECT '2008-8-2 17:39:35 ' union all
SELECT '2008-8-1 17:39:25 ' union all
SELECT '2008-8-4 17:39:15 '
select top 3 date
from @TB
group by date
order by date desc date
-----------------------
2008-08-04 17:39:00
2008-08-03 17:39:00
2008-08-03 16:47:00(3 行受影响)
from (SELECT date=convert(char(10),date,120) from @TB) A
group by date
order by date desc date
----------
2008-08-04
2008-08-03
2008-08-02(3 行受影响)
2009-6-12 16:47:15
2009-6-12 16:06:46
2009-6-12 12:45:13
2009-6-11 17:39:05
2009-6-9 19:27:47
2009-6-2 15:18:27 我想求出最近三天的日期
如
select top 3 addtime from info2 group by addtime order by addtime desc
这条语句把重复的日期也求出来了 select * from @tb where addtime>dateadd(dd,-3,getdate())
select * from @tb where addtime>=dateadd(dd,-3,getdate())