首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

三表联查统计排序有关问题

2012-02-03 
三表联查统计排序问题create table tb(id int,time datetime,re_id varchar(10))insert into tb select 1

三表联查统计排序问题
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 

热点排行