create table tb(id int,time datetime,name varchar(10)) insert into tb select 1 , '2008-5-1' , 'a' insert into tb select 2 , '2008-5-1' , 'd' insert into tb select 3 , '2008-5-1' , 'd' insert into tb select 4 , '2008-5-1' , 'a' insert into tb select 5 , '2008-5-1' , 'a' insert into tb select 6 , '2008-5-1' , 'b' insert into tb select 7 , '2008-5-1' , 'a' insert into tb select 8 , '2008-5-1' , 'c' insert into tb select 9 , '2008-5-1' , 'a' insert into tb select 10, '2008-5-1' , 'c' insert into tb select 11, '2008-5-2' , 'a' insert into tb select 12, '2008-5-2' , 'd' insert into tb select 13, '2008-5-2' , 'd' insert into tb select 14, '2008-5-2' , 'a' insert into tb select 15, '2008-5-2' , 'a' insert into tb select 16, '2008-5-2' , 'b' insert into tb select 17, '2008-5-2' , 'a' insert into tb select 18, '2008-5-2' , 'c' insert into tb select 19, '2008-5-2' , 'a' insert into tb select 20, '2008-5-2' , 'c' create table username(id int,username nvarchar(50)) insert into username select 1 , 'a' insert into username select 2 , 'b' insert into username select 3 , 'c' insert into username select 4 , 'd' select a.username,b.time,count(*) from username a,tb b where a.username=b.name group by a.username,b.time order by count(*) desc drop table tb drop table username
-->每人每天 select name,convert(varchar(10),time,120),count(1)cn from tb group by name,convert(varchar(10),time,120) order by cn desc-->时间段 select name,count(1)cn from tb where time>='20080502' and time < '2009' group by name order by cn desc
create table tb(id int,time datetime,name varchar(10)) insert into tb select 1 , '2008-5-1' , 'a' insert into tb select 2 , '2008-5-1' , 'd' insert into tb select 3 , '2008-5-1' , 'd' insert into tb select 4 , '2008-5-1' , 'a' insert into tb select 5 , '2008-5-1' , 'a' insert into tb select 6 , '2008-5-1' , 'b' insert into tb select 7 , '2008-5-1' , 'a' insert into tb select 8 , '2008-5-1' , 'c' insert into tb select 9 , '2008-5-1' , 'a' insert into tb select 10, '2008-5-1' , 'c' insert into tb select 11, '2008-5-2' , 'a' insert into tb select 12, '2008-5-2' , 'd' insert into tb select 13, '2008-5-2' , 'd' insert into tb select 14, '2008-5-2' , 'a' insert into tb select 15, '2008-5-2' , 'a' insert into tb select 16, '2008-5-2' , 'b' insert into tb select 17, '2008-5-2' , 'a' insert into tb select 18, '2008-5-2' , 'c' insert into tb select 19, '2008-5-2' , 'a' insert into tb select 20, '2008-5-2' , 'c' -->每人每天 select name,convert(varchar(10),time,120)date,count(1)cn from tb group by name,convert(varchar(10),time,120) order by cn desc /* ---------- ---------- ----------- a 2008-05-01 5 a 2008-05-02 5 c 2008-05-02 2 d 2008-05-02 2 c 2008-05-01 2 d 2008-05-01 2 b 2008-05-01 1 b 2008-05-02 1 */-->时间段 select name,count(1)cn from tb where time>='2007' and time < '2009' group by name order by cn desc /* name cn ---------- ----------- a 10 c 4 d 4 b 2 */
time是带时间的,你要学会揣摩提问者的心思。
select a.ID,a.UserName,b.time,count(b.ID) as 记录 from username a left join tb b on a.username=b.name group by a.ID,a.UserName,b.time order by count(b.ID) desc /* ID UserName time 记录 ----------- -------------------------------------------------- ------------------------------------------------------ ----------- 1 a 2008-05-01 00:00:00.000 5 1 a 2008-05-02 00:00:00.000 5 3 c 2008-05-01 00:00:00.000 2 3 c 2008-05-02 00:00:00.000 2 4 d 2008-05-01 00:00:00.000 2 4 d 2008-05-02 00:00:00.000 2 2 b 2008-05-01 00:00:00.000 1 2 b 2008-05-02 00:00:00.000 1(所影响的行数为 8 行) */
〉〉time是带时间的,你要学会揣摩提问者的心思。 ...
用left join 在tb表不存时显示为0
要求两表联查,表tb中 name 和 username 表中的username 是关联的,username中会员的相关资料,所以要求联查取出表username中ID等其它字段的信息. 你的只是统计了表tb的数据. 麻烦您再看下.
select a.username,isnull(b.num,0) 记录条数 from username a left join (select name,count(*) num from tb where datetime between 指定的开始时间 and 指定的结束时间 group by name) b on a.username=b.name order by 2 desc
insert into tb select 1 , '2008-5-1' , 'a'
insert into tb select 2 , '2008-5-1' , 'd'
insert into tb select 3 , '2008-5-1' , 'd'
insert into tb select 4 , '2008-5-1' , 'a'
insert into tb select 5 , '2008-5-1' , 'a'
insert into tb select 6 , '2008-5-1' , 'b'
insert into tb select 7 , '2008-5-1' , 'a'
insert into tb select 8 , '2008-5-1' , 'c'
insert into tb select 9 , '2008-5-1' , 'a'
insert into tb select 10, '2008-5-1' , 'c'
insert into tb select 11, '2008-5-2' , 'a'
insert into tb select 12, '2008-5-2' , 'd'
insert into tb select 13, '2008-5-2' , 'd'
insert into tb select 14, '2008-5-2' , 'a'
insert into tb select 15, '2008-5-2' , 'a'
insert into tb select 16, '2008-5-2' , 'b'
insert into tb select 17, '2008-5-2' , 'a'
insert into tb select 18, '2008-5-2' , 'c'
insert into tb select 19, '2008-5-2' , 'a'
insert into tb select 20, '2008-5-2' , 'c'
create table username(id int,username nvarchar(50))
insert into username select 1 , 'a'
insert into username select 2 , 'b'
insert into username select 3 , 'c'
insert into username select 4 , 'd'
select a.username,b.time,count(*)
from username a,tb b where a.username=b.name
group by a.username,b.time
order by count(*) desc
drop table tb
drop table username
select name,convert(varchar(10),time,120),count(1)cn from tb group by name,convert(varchar(10),time,120) order by cn desc-->时间段
select name,count(1)cn from tb where time>='20080502' and time < '2009' group by name order by cn desc
a,2008-05-01,5
a,2008-05-02,5
c,2008-05-02,2
d,2008-05-02,2
c,2008-05-01,2
d,2008-05-01,2
b,2008-05-01,1
b,2008-05-02,1
insert into tb select 1 , '2008-5-1' , 'a'
insert into tb select 2 , '2008-5-1' , 'd'
insert into tb select 3 , '2008-5-1' , 'd'
insert into tb select 4 , '2008-5-1' , 'a'
insert into tb select 5 , '2008-5-1' , 'a'
insert into tb select 6 , '2008-5-1' , 'b'
insert into tb select 7 , '2008-5-1' , 'a'
insert into tb select 8 , '2008-5-1' , 'c'
insert into tb select 9 , '2008-5-1' , 'a'
insert into tb select 10, '2008-5-1' , 'c'
insert into tb select 11, '2008-5-2' , 'a'
insert into tb select 12, '2008-5-2' , 'd'
insert into tb select 13, '2008-5-2' , 'd'
insert into tb select 14, '2008-5-2' , 'a'
insert into tb select 15, '2008-5-2' , 'a'
insert into tb select 16, '2008-5-2' , 'b'
insert into tb select 17, '2008-5-2' , 'a'
insert into tb select 18, '2008-5-2' , 'c'
insert into tb select 19, '2008-5-2' , 'a'
insert into tb select 20, '2008-5-2' , 'c' -->每人每天
select name,convert(varchar(10),time,120)date,count(1)cn from tb group by name,convert(varchar(10),time,120) order by cn desc
/*
---------- ---------- -----------
a 2008-05-01 5
a 2008-05-02 5
c 2008-05-02 2
d 2008-05-02 2
c 2008-05-01 2
d 2008-05-01 2
b 2008-05-01 1
b 2008-05-02 1
*/-->时间段
select name,count(1)cn from tb where time>='2007' and time < '2009' group by name order by cn desc
/*
name cn
---------- -----------
a 10
c 4
d 4
b 2
*/
time是带时间的,你要学会揣摩提问者的心思。
select
a.ID,a.UserName,b.time,count(b.ID) as 记录
from
username a
left join
tb b on a.username=b.name
group by a.ID,a.UserName,b.time
order by count(b.ID) desc
/*
ID UserName time 记录
----------- -------------------------------------------------- ------------------------------------------------------ -----------
1 a 2008-05-01 00:00:00.000 5
1 a 2008-05-02 00:00:00.000 5
3 c 2008-05-01 00:00:00.000 2
3 c 2008-05-02 00:00:00.000 2
4 d 2008-05-01 00:00:00.000 2
4 d 2008-05-02 00:00:00.000 2
2 b 2008-05-01 00:00:00.000 1
2 b 2008-05-02 00:00:00.000 1(所影响的行数为 8 行)
*/
from username a left join
(select name,count(*) num from tb where datetime between 指定的开始时间 and 指定的结束时间 group by name) b
on a.username=b.name
order by 2 desc