22,206
社区成员
发帖
与我相关
我的任务
分享
表和数据:
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
-- 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)
*/
-- 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)
*/
----------------------------------------------------------------------------------
-- 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 行受影响)
*/
--建立测试环境
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 行受影响)
*/
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)
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)