三表联查统计排序问题
create table tb(id int,time datetime,re_id varchar(10))  
insert into tb select 1 , '2008-5-1' , '1'  
insert into tb select 2 , '2008-5-1' , '2'  
insert into tb select 3 , '2008-5-1' , '3'  
insert into tb select 4 , '2008-5-1' , '1'  
insert into tb select 5 , '2008-5-1' , '1'  
insert into tb select 6 , '2008-5-1' , '1'  
insert into tb select 7 , '2008-5-1' , '3'  
insert into tb select 8 , '2008-5-1' , '4'  
insert into tb select 9 , '2008-5-1' , '4'  
insert into tb select 10, '2008-5-1' , '4'  
insert into tb select 11, '2008-5-2' , '4'  
insert into tb select 12, '2008-5-2' , '1'  
insert into tb select 13, '2008-5-2' , '1'  
insert into tb select 14, '2008-5-2' , '1'  
insert into tb select 15, '2008-5-2' , '1'  
insert into tb select 16, '2008-5-2' , '1'  
insert into tb select 17, '2008-5-2' , '1'  
insert into tb select 18, '2008-5-2' , '1'  
insert into tb select 19, '2008-5-2' , '1'  
insert into tb select 20, '2008-5-2' , '2'  


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'  


create table re_list(id int,UserID nvarchar(50))  
insert into re_list select 1 , '1'  
insert into re_list select 2 , '2'  
insert into re_list select 3 , '3'  
insert into re_list select 4 , '4'


其中表
tb.re_id=re_list.id
username.id=re_list.userid

要求选出 每个会员按表 tb 的时间段（天，周，月，年或指定时间段）进行统计tb中的记录条数，
要求按以下列表显示,需考虑有百万条数量下,算法速度.

----------------------------------------------
会员名 日期 记录条数(表tb中的)


------解决方案--------------------

SQL code

select a.username [会员名], convert(varchar(10),c.time,120) [日期_天], count(*) [记录条数(表tb中的)] from username a , re_list b , tb c where a.id = b.userid and b.id = c.re_id group by a.username , convert(varchar(10),c.time,120)
select a.username [会员名], datepart(week,c.time) [日期_周], count(*) [记录条数(表tb中的)] from username a , re_list b , tb c where a.id = b.userid and b.id = c.re_id group by a.username , datepart(week,c.time)
select a.username [会员名], convert(varchar(7),c.time,120) [日期_月], count(*) [记录条数(表tb中的)] from username a , re_list b , tb c where a.id = b.userid and b.id = c.re_id group by a.username , convert(varchar(7),c.time,120)
select a.username [会员名], convert(varchar(4),c.time,120) [日期_年], count(*) [记录条数(表tb中的)] from username a , re_list b , tb c where a.id = b.userid and b.id = c.re_id group by a.username , convert(varchar(4),c.time,120)

------解决方案--------------------

SQL code
select