22,209
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL
DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[userid] INT
,[login_time] datetime
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'10001',N'2018-12-13 14:39:56.300')
INSERT INTO dbo.[t] VALUES(N'10001',N'2018-12-13 14:59:56.100')
INSERT INTO dbo.[t] VALUES(N'10002',N'2018-12-13 16:23:21.200')
INSERT INTO dbo.[t] VALUES(N'10003',N'2018-12-13 23:58:31.500')
INSERT INTO dbo.[t] VALUES(N'10001',N'2018-12-14 11:48:51.200')
INSERT INTO dbo.[t] VALUES(N'10003',N'2018-12-15 13:03:43.400')
INSERT INTO dbo.[t] VALUES(N'10004',N'2018-12-15 15:27:28.100')
INSERT INTO dbo.[t] VALUES(N'10004',N'2018-12-15 23:59:59.100')
SELECT userid
,COUNT(DISTINCT CONVERT(CHAR(10),login_time,120)) AS daynum
FROM t GROUP BY userid
/*
userid daynum
----------- -----------
10001 2
10002 1
10003 2
10004 1
*/
with list as(
select userid='10001',logintime='2018-12-13 14:39:56.300' union all
select userid='10001',logintime='2018-12-13 14:59:56.100' union all
select userid='10002',logintime='2018-12-13 16:23:21.200' union all
select userid='10003',logintime='2018-12-13 23:58:31.500' union all
select userid='10001',logintime='2018-12-14 11:48:51.200' union all
select userid='10003',logintime='2018-12-15 13:03:43.400' union all
select userid='10004',logintime='2018-12-15 15:27:28.100' union all
select userid='10004',logintime='2018-12-15 23:59:59.100'
)
select userid,logintime=count(distinct cast(logintime as date))
from list
group by userid
userid logintime
------ -----------
10001 2
10002 1
10003 2
10004 1