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

thiscolast 2010-07-06 11:48:33
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


...全文
222 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
zejyu 2010-07-10
  • 打赏
  • 举报
回复
按照要求修正輸出結果及格式


-- 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)
*/
zejyu 2010-07-10
  • 打赏
  • 举报
回复

-- 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)
*/
zejyu 2010-07-10
  • 打赏
  • 举报
回复

-- 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)
*/
htl258_Tony 2010-07-10
  • 打赏
  • 举报
回复
----------------------------------------------------------------------------------
-- 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 行受影响)
*/
llccbbllccbb 2010-07-10
  • 打赏
  • 举报
回复
select * from
(
select *,row_number() over(partition by userid order by login_time desc) r from [table]
) a where a.r=1
永生天地 2010-07-06
  • 打赏
  • 举报
回复


--建立测试环境
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 行受影响)


*/
nightmaple 2010-07-06
  • 打赏
  • 举报
回复
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)
nightmaple 2010-07-06
  • 打赏
  • 举报
回复
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)

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧