27,579
社区成员
发帖
与我相关
我的任务
分享
--drop table test
create table test(userid int, datetime datetime)
insert into test
select 1 ,'2014-01-13' union all
select 2 ,'2014-01-13' union all
select 3 ,'2014-01-13' union all
select 1 ,'2014-01-14' union all
select 4 ,'2014-01-14'
go
select datetime,COUNT(*) '人数',
(select COUNT(*) from test t2 where t2.datetime = t.datetime) as 总人数
from
(
select *,
(select COUNT(*) from test t2
where t1.userid = t2.userid and t1.datetime>=t2.datetime) rownum
from test t1
)t
where rownum = 1
group by datetime
/*
datetime 人数 总人数
2014-01-13 00:00:00.000 3 3
2014-01-14 00:00:00.000 1 2
*/
create table test(userid int, datetime datetime)
insert into test
select 1 ,'2014-01-13' union all
select 2 ,'2014-01-13' union all
select 3 ,'2014-01-13' union all
select 1 ,'2014-01-14' union all
select 4 ,'2014-01-14'
go
IF object_id('tempdb..#t','U')IS NOT NULL
DROP TABLE #t
select *,
(select COUNT(*) from test t2
where t1.userid = t2.userid and t1.datetime>=t2.datetime) rownum INTO #t
from test t1
select datetime,COUNT(*) '人数',(SELECT COUNT(1) FROM #t b WHERE a.datetime=b.datetime)[总人数]
from #t a
where rownum = 1
group by datetime
/*
datetime 人数 总人数
----------------------- ----------- -----------
2014-01-13 00:00:00.000 3 3
2014-01-14 00:00:00.000 1 2
*/
--drop table test
create table test(userid int, datetime datetime)
insert into test
select 1 ,'2014-01-13' union all
select 2 ,'2014-01-13' union all
select 3 ,'2014-01-13' union all
select 1 ,'2014-01-14' union all
select 4 ,'2014-01-14'
go
select datetime,COUNT(*) '人数'
from
(
select *,
(select COUNT(*) from test t2
where t1.userid = t2.userid and t1.datetime>=t2.datetime) rownum
from test t1
)t
where rownum = 1
group by datetime
/*
datetime 人数
2014-01-13 00:00:00.000 3
2014-01-14 00:00:00.000 1
*/
create table #test(userid int, datetime datetime)
insert into #test
select 1 ,'2014-01-13' union all
select 2 ,'2014-01-13' union all
select 3 ,'2014-01-13' union all
select 1 ,'2014-01-14' union all
select 4 ,'2014-01-14'
go
SELECT datetime,COUNT(*)
FROM
(
SELECT DISTINCT userid,datetime
FROM #test a
WHERE NOT EXISTS(SELECT * FROM #test b WHERE a.userid=b.userid AND a.datetime>b.datetime)
) a
GROUP BY datetime
create table test(userid int, datetime datetime)
insert into test
select 1 ,'2014-01-13' union all
select 2 ,'2014-01-13' union all
select 3 ,'2014-01-13' union all
select 1 ,'2014-01-14' union all
select 4 ,'2014-01-14'
go
select datetime,COUNT(*) '人数'
from
(
select *,
ROW_NUMBER() over(partition by userid order by datetime) rownum
from test
)t
where rownum = 1
group by datetime
/*
datetime 人数
2014-01-13 00:00:00.000 3
2014-01-14 00:00:00.000 1
*/