关于查询时过滤重复的数据，只显示最新的一条

Question

userid 和 name 是对应的, 
想达到的效果是, 以天为单位，过滤掉当天userid重复的数据，只显示login_time为最新的一条数据，
并统计该userid当天的数量。
表和数据：
 userid    name      reg_time         login_time
--------  -------  ------------  --------------------
  10001     aaa     2009-01-01    2010-07-06 10:46:3
  10002     bbb     2008-01-01    2010-07-06 10:46:27
  10005     ccc     2007-01-01    2010-07-06 10:18:09
  10001     aaa     2009-01-01    2010-07-06 10:01:35
  10005     ccc     2007-01-01    2010-07-06 04:57:02
  10002     bbb     2008-01-01    2010-07-05 01:19:4
  10001     aaa     2009-01-01    2010-07-05 01:19:48想要得到的结果为：  userid    name         login_time      today_stat
-------  ------------  --------------   -------------
  10001     aaa         2010-07-06       2
  10002     bbb         2010-07-06       1
  10005     ccc         2010-07-06       2
  10002     bbb         2010-07-05       1
  10001     aaa         2010-07-05       1

Answer 1

select userid,name,convert(varchar(20),login_time,102),count(*) as today_stat
from tablename 
group by userid,name,convert(varchar(20),login_time,102)

Answer 2

select userid,name,convert(varchar(20),login_time,102) as login_time,count(*) as today_stat
from tablename 
group by userid,name,convert(varchar(20),login_time,102)

Answer 3

--建立测试环境
declare @tb TABLE 
(
userid varchar(10), 
name varchar(4),
reg_time datetime,
login_time datetime
)INSERT @tb
 select '10001','aaa','2009-01-01','2010-07-06 10:46:3' union all 
 select '10002','bbb','2008-01-01','2010-07-06 10:46:27' union all 
 select '10005','ccc','2007-01-01','2010-07-06 10:18:09' union all 
 select '10001','aaa','2009-01-01','2010-07-06 10:01:35' union all 
 select '10005','ccc','2007-01-01','2010-07-06 04:57:02' union all 
 select '10002','bbb','2008-01-01','2010-07-05 01:19:4' union all 
 select '10001','aaa','2009-01-01','2010-07-05 01:19:48'
--查询
select userid,name,max(login_time)login_time, count(1)today_stat
from @tb
group by userid,name,convert(varchar(10),login_time,120)
--结果
/*(7 行受影响)
userid     name                         
---------- ---- ----------------------- -----------
10001      aaa  2010-07-05 01:19:48.000 1
10001      aaa  2010-07-06 10:46:03.000 2
10002      bbb  2010-07-05 01:19:04.000 1
10002      bbb  2010-07-06 10:46:27.000 1
10005      ccc  2010-07-06 10:18:09.000 2(5 行受影响)
*/

Answer 4

select * from
(
  select *,row_number() over(partition by userid order by login_time desc) r from [table]
) a where a.r=1

Answer 5

----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date   : 2010-07-10 08:07:59
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--          Jul  9 2008 14:43:34 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
-- Blog   : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([userid] [int],[name] [nvarchar](10),[reg_time] [datetime],[login_time] [datetime])
INSERT INTO [tb]
SELECT '10001','aaa','2009-01-01','2010-07-06 10:46:3' UNION ALL
SELECT '10002','bbb','2008-01-01','2010-07-06 10:46:27' UNION ALL
SELECT '10005','ccc','2007-01-01','2010-07-06 10:18:09' UNION ALL
SELECT '10001','aaa','2009-01-01','2010-07-06 10:01:35' UNION ALL
SELECT '10005','ccc','2007-01-01','2010-07-06 04:57:02' UNION ALL
SELECT '10002','bbb','2008-01-01','2010-07-05 01:19:4' UNION ALL
SELECT '10001','aaa','2009-01-01','2010-07-05 01:19:48'--SELECT * FROM [tb]-->SQL查询如下:
SELECT userid,name,CONVERT(CHAR,login_time,23) AS login_time,COUNT(1) AS today_stat
FROM tb 
GROUP BY userid,name,CONVERT(CHAR,login_time,23)
ORDER BY 3 DESC,1
/*
userid      name       login_time                     today_stat
----------- ---------- ------------------------------ -----------
10001       aaa        2010-07-06                     2
10002       bbb        2010-07-06                     1
10005       ccc        2010-07-06                     2
10001       aaa        2010-07-05                     1
10002       bbb        2010-07-05                     1(5 行受影响)
*/

Answer 6

-- 1、準備測試數據
declare @T table(
[userid] int,
[name] nvarchar(10),
[reg_time] datetime,
[login_time] datetime
)INSERT INTO @T
SELECT '10001','aaa','2009-01-01','2010-07-06 10:46:3' 
UNION ALL
SELECT '10002','bbb','2008-01-01','2010-07-06 10:46:27' 
UNION ALL
SELECT '10005','ccc','2007-01-01','2010-07-06 10:18:09' 
UNION ALL
SELECT '10001','aaa','2009-01-01','2010-07-06 10:01:35' 
UNION ALL
SELECT '10005','ccc','2007-01-01','2010-07-06 04:57:02' 
UNION ALL
SELECT '10002','bbb','2008-01-01','2010-07-05 01:19:4' 
UNION ALL
SELECT '10001','aaa','2009-01-01','2010-07-05 01:19:48'
-- 2、列出每人每天第一次登錄的時間
select [userid],
max([name]) as [name],
max([reg_time]) as [reg_time],
min([login_time]) as [login_time]
  from @T 
group by [userid],datediff(day,getdate(),[login_time])----------------------------------------------------------------------
/*
(7 row(s) affected)
userid      name       reg_time                login_time
----------- ---------- ----------------------- -----------------------
10001       aaa        2009-01-01 00:00:00.000 2010-07-05 01:19:48.000
10002       bbb        2008-01-01 00:00:00.000 2010-07-05 01:19:04.000
10001       aaa        2009-01-01 00:00:00.000 2010-07-06 10:01:35.000
10002       bbb        2008-01-01 00:00:00.000 2010-07-06 10:46:27.000
10005       ccc        2007-01-01 00:00:00.000 2010-07-06 04:57:02.000(5 row(s) affected)
*/

Answer 7

-- 1、準備測試數據
declare @T table(
[userid] int,
[name] nvarchar(10),
[reg_time] datetime,
[login_time] datetime
)INSERT INTO @T
SELECT '10001','aaa','2009-01-01','2010-07-06 10:46:3' 
UNION ALL
SELECT '10002','bbb','2008-01-01','2010-07-06 10:46:27' 
UNION ALL
SELECT '10005','ccc','2007-01-01','2010-07-06 10:18:09' 
UNION ALL
SELECT '10001','aaa','2009-01-01','2010-07-06 10:01:35' 
UNION ALL
SELECT '10005','ccc','2007-01-01','2010-07-06 04:57:02' 
UNION ALL
SELECT '10002','bbb','2008-01-01','2010-07-05 01:19:4' 
UNION ALL
SELECT '10001','aaa','2009-01-01','2010-07-05 01:19:48'
-- 2、列出每人每天第一次登錄的時間
select [userid],
max([name]) as [name],
max([reg_time]) as [reg_time],
min([login_time]) as [login_time]
  from @T 
group by [userid],datediff(day,getdate(),[login_time])----------------------------------------------------------------------
/*
(7 row(s) affected)
userid name reg_time login_time
----------- ---------- ----------------------- -----------------------
10001 aaa 2009-01-01 00:00:00.000 2010-07-05 01:19:48.000
10002 bbb 2008-01-01 00:00:00.000 2010-07-05 01:19:04.000
10001 aaa 2009-01-01 00:00:00.000 2010-07-06 10:01:35.000
10002 bbb 2008-01-01 00:00:00.000 2010-07-06 10:46:27.000
10005 ccc 2007-01-01 00:00:00.000 2010-07-06 04:57:02.000(5 row(s) affected)
*/

Answer 8

按照要求修正輸出結果及格式
-- 1、準備測試數據
declare @T table(
[userid] int,
[name] nvarchar(10),
[reg_time] datetime,
[login_time] datetime
)INSERT INTO @T
SELECT '10001','aaa','2009-01-01','2010-07-06 10:46:3' 
UNION ALL
SELECT '10002','bbb','2008-01-01','2010-07-06 10:46:27' 
UNION ALL
SELECT '10005','ccc','2007-01-01','2010-07-06 10:18:09' 
UNION ALL
SELECT '10001','aaa','2009-01-01','2010-07-06 10:01:35' 
UNION ALL
SELECT '10005','ccc','2007-01-01','2010-07-06 04:57:02' 
UNION ALL
SELECT '10002','bbb','2008-01-01','2010-07-05 01:19:4' 
UNION ALL
SELECT '10001','aaa','2009-01-01','2010-07-05 01:19:48'
-- 2、列出每人每天第一次登錄的時間
select [userid],
    max([name]) as [name],
    convert(nvarchar,min([login_time]),23) as [login_time],
    count(*) as today_state
  from @T 
group by [userid],datediff(day,getdate(),[login_time])
order by 3 desc,1------------------------------------------------------------------
/*
(7 row(s) affected)
userid      name       login_time                     today_state
----------- ---------- ------------------------------ -----------
10001       aaa        2010-07-06                     2
10002       bbb        2010-07-06                     1
10005       ccc        2010-07-06                     2
10001       aaa        2010-07-05                     1
10002       bbb        2010-07-05                     1(5 row(s) affected)
*/