查询新增人员数量

hn_xiayanping 2014-01-14 05:26:47
做统计的,查询每天的新增的人员
表结构
userid datetime
1 2014-01-13
2 2014-01-13
3 2014-01-13
1 2014-01-14
4 2014-01-14

结果
2014-01-13 为三个人
2014-01-14 为一个

2014-01-14 编号为1的在13号登录过所以不是新增的。4在14之前没有登录过所以是新增的
一句话,每天的登录记录中,之前没有登录过的算是新增的
...全文
125 点赞 收藏 8
写回复
8 条回复
阳泉酒家小当家 2014年01月20日
引用 6 楼 hn_xiayanping 的回复:
还要添加一列,就是计算当日的总人数呢 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(*) '人数',
       (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
*/
回复 点赞
發糞塗牆 2014年01月20日
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

*/
回复 点赞
hn_xiayanping 2014年01月20日
还要添加一列,就是计算当日的总人数呢 datetime 人数 总人数 2014-01-13 00:00:00.000 3 3 2014-01-14 00:00:00.000 1 2
回复 点赞
阳泉酒家小当家 2014年01月14日
引用 2 楼 hn_xiayanping 的回复:
[quote=引用 1 楼 yupeigu 的回复:] 是这样吗:

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
*/
能不用ROW_NUMBER()吗[/quote] 适合2000的:
--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
*/
回复 点赞
lzw_0736 2014年01月14日

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
回复 点赞
阳泉酒家小当家 2014年01月14日
引用 2 楼 hn_xiayanping 的回复:
[quote=引用 1 楼 yupeigu 的回复:] 是这样吗:

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
*/
能不用ROW_NUMBER()吗[/quote] 你的是2000是吧
回复 点赞
hn_xiayanping 2014年01月14日
引用 1 楼 yupeigu 的回复:
是这样吗:

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
*/
能不用ROW_NUMBER()吗
回复 点赞
阳泉酒家小当家 2014年01月14日
是这样吗:

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
*/
回复 点赞
发动态
发帖子
应用实例
创建于2007-09-28

1.1w+

社区成员

6.8w+

社区内容

MS-SQL Server 应用实例
社区公告
暂无公告