请教高手怎么写这样一条sql查询语句(在线等)

jhobo 2007-11-27 02:23:28
现在有一张表记录的是登录系统的用户名和登录时间 ,数据格式如下
ID UserName LoginDate
----------- -------------------------------------------------- ------------------------------------------------------
1 rechargetest\administrator 2007-08-23 00:00:00
2 rechargetest\administrator 2007-08-23 00:00:00
3 rechargetest\administrator 2007-08-23 00:00:00
5 paims-johnny\johnny yuan 2007-08-23 00:00:00
6 rechargetest\andyj 2007-08-27 00:00:00
7 rechargetest\administrator 2007-08-27 00:00:00
8 rechargetest\ella 2007-08-27 00:00:00
9 rechargetest\andyj 2007-08-27 00:00:00
10 rechargetest\administrator 2007-08-27 00:00:00
11 rechargetest\ella 2007-08-27 00:00:00
12 rechargetest\andyj 2007-08-27 00:00:00
13 rechargetest\administrator 2007-08-27 00:00:00
14 rechargetest\ella 2007-08-27 00:00:00
15 rechargetest\administrator 2007-08-28 00:00:00
16 rechargetest\administrator 2007-08-30 00:00:00
17 rechargetest\administrator 2007-08-31 00:00:00
18 rechargetest\administrator 2007-09-03 00:00:00
19 rechargetest\administrator 2007-09-03 00:00:00
20 rechargetest\administrator 2007-09-04 00:00:00
21 rechargetest\administrator 2007-09-05 00:00:00
22 rechargetest\administrator 2007-09-06 00:00:00
23 rechargetest\administrator 2007-09-07 00:00:00
24 rechargetest\administrator 2007-09-10 00:00:00
25 rechargetest\administrator 2007-09-11 00:00:00
26 rechargetest\administrator 2007-09-12 00:00:00
27 rechargetest\andy 2007-09-12 00:00:00
28 rechargetest\andyj 2007-09-12 00:00:00
29 rechargetest\administrator 2007-09-13 00:00:00
30 rechargetest\andyj 2007-09-13 00:00:00
31 rechargetest\administrator 2007-09-14 00:00:00
32 rechargetest\administrator 2007-09-17 00:00:00
33 rechargetest\andy 2007-09-17 00:00:00
34 rechargetest\andyj 2007-09-17 00:00:00
35 rechargetest\administrator 2007-09-18 00:00:00
36 rechargetest\andyj 2007-09-18 00:00:00
37 rechargetest\administrator 2007-09-19 00:00:00
38 rechargetest\andyj 2007-09-19 00:00:00
39 rechargetest\andy 2007-09-19 00:00:00
40 rechargetest\administrator 2007-09-20 00:00:00
41 rechargetest\administrator 2007-09-21 00:00:00
42 rechargetest\administrator 2007-09-24 00:00:00
43 rechargetest\administrator 2007-09-25 00:00:00
44 wsssvr\administrator 2007-09-25 00:00:00
45 rechargetest\administrator 2007-09-26 00:00:00
46 rechargetest\administrator 2007-09-27 00:00:00
47 rechargetest\administrator 2007-09-28 00:00:00
48 rechargetest\administrator 2007-09-29 00:00:00
49 rechargetest\administrator 2007-09-30 00:00:00
50 rechargetest\administrator 2007-09-30 00:00:00
51 wsssvr\administrator 2007-10-08 00:00:00
52 rechargetest\administrator 2007-10-08 00:00:00
53 wsssvr\administrator 2007-10-08 00:00:00
54 rechargetest\administrator 2007-10-08 00:00:00
55 rechargetest\administrator 2007-10-09 00:00:00
56 rechargetest\administrator 2007-10-09 00:00:00
57 rechargetest\administrator 2007-10-10 00:00:00
58 rechargetest\administrator 2007-10-11 00:00:00
59 rechargetest\administrator 2007-10-11 00:00:00
60 rechargetest\administrator 2007-10-12 00:00:00
61 rechargetest\administrator 2007-10-12 00:00:00
62 rechargetest\administrator 2007-10-13 00:00:00
63 rechargetest\administrator 2007-10-15 00:00:00
64 rechargetest\administrator 2007-10-16 00:00:00
65 rechargetest\administrator 2007-10-17 00:00:00
66 rechargetest\administrator 2007-10-18 00:00:00
67 rechargetest\administrator 2007-10-19 00:00:00
68 rechargetest\administrator 2007-10-22 00:00:00
69 rechargetest\123 2007-10-22 00:00:00
70 rechargetest\administrator 2007-10-23 00:00:00
71 rechargetest\skylee 2007-10-23 00:00:00
72 rechargetest\skylee 2007-10-24 00:00:00
73 rechargetest\administrator 2007-10-24 00:00:00
74 rechargetest\andy 2007-10-24 00:00:00
75 rechargetest\andyj 2007-10-24 00:00:00
76 rechargetest\administrator 2007-10-25 00:00:00
77 rechargetest\andy 2007-10-25 00:00:00
78 rechargetest\andyj 2007-10-25 00:00:00
79 rechargetest\administrator 2007-10-26 00:00:00
80 rechargetest\andy 2007-10-26 00:00:00
81 rechargetest\andyj 2007-10-26 00:00:00
82 rechargetest\skylee 2007-10-28 00:00:00
83 rechargetest\administrator 2007-10-28 00:00:00
84 rechargetest\administrator 2007-10-29 00:00:00
85 rechargetest\andy 2007-10-29 00:00:00
86 rechargetest\andyj 2007-10-29 00:00:00
87 rechargetest\wsssvr$ 2007-10-29 00:00:00
88 rechargetest\administrator 2007-10-29 00:00:00
89 rechargetest\andy 2007-10-29 00:00:00
90 rechargetest\andyj 2007-10-29 00:00:00
91 rechargetest\wsssvr$ 2007-10-29 00:00:00
现在要用这张表做一个web统计图,统计每天登录系统的人数
比如2007-08-23 有2人登录
请高手指点此sql语句怎么写,谢谢!!!!!!~~~~~
...全文
113 13 打赏 收藏 转发到动态 举报
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
fxmabcd 2007-11-27
  • 打赏
  • 举报
回复
根据楼主提供的信息,12楼的就可以解决问题,但我认为11楼的适应性更好,如5楼说的,如果登陆时间还存在时-分-秒-毫秒,就需要处理成只有年-月-日
我个人认为11楼的分组convert(varchar(10),logindate,120)中的120应该采用103
elvis_gao 2007-11-27
  • 打赏
  • 举报
回复

select logindate as '日期', count(distinct UserName) as '人数'
from 表名
group by LoginDate
ojuju10 2007-11-27
  • 打赏
  • 举报
回复

select convert(varchar(10),logindate,120) as logindate,count(1) as 人次
from tablename
group by convert(varchar(10),logindate,120)
-狙击手- 2007-11-27
  • 打赏
  • 举报
回复
select convert(varchar(10), LoginDate ,120) as 日期,count(distinct UserName) as 人次
from 表名
group by convert(varchar(10), LoginDate ,120)
dawugui 2007-11-27
  • 打赏
  • 举报
回复
select convert(varchar(10),LoginDate,120) LoginDate , 登录人次 = count(*) from tb group by convert(varchar(10),LoginDate,120)
晓风残月0110 2007-11-27
  • 打赏
  • 举报
回复
jf
jhobo 2007-11-27
  • 打赏
  • 举报
回复
谢谢你们。嘎嘎
kuangdp 2007-11-27
  • 打赏
  • 举报
回复
select logindate ,count(distinct UserName) 登录人数
from tb_name
group by logindate

8好意思,应该是按日期分组
OracleRoob 2007-11-27
  • 打赏
  • 举报
回复
如果logindate带有时分秒,需要先转换为不带时分秒的日期
OracleRoob 2007-11-27
  • 打赏
  • 举报
回复
create table 表名(ID int,UserName varchar(100), LoginDate  datetime)


insert into 表名 select 1, 'rechargetest\administrator' ,'2007-08-23 00:00:00'
insert into 表名 select 2, 'rechargetest\administrator' ,'2007-08-23 00:00:00'
insert into 表名 select 3, 'rechargetest\administrator' ,'2007-08-23 00:00:00'
insert into 表名 select 5, 'paims-johnny\johnny yuan' ,'2007-08-23 00:00:00'

select convert(varchar(10), LoginDate ,120) as 日期,count(distinct UserName) as 人次
from 表名
group by convert(varchar(10), LoginDate ,120)

drop table 表名
kuangdp 2007-11-27
  • 打赏
  • 举报
回复
select username,logindate,count(1) 登录人数
from tb_name
group by username,logindate
playwarcraft 2007-11-27
  • 打赏
  • 举报
回复
select LoginDate ,count(distinct UserName)
from T
group by LoginDate
OracleRoob 2007-11-27
  • 打赏
  • 举报
回复
--这样?

select count(distinct UserName)
from 表名
group by convert(varchar(10), LoginDate ,120)

22,206

社区成员

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

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