求存储过程，多表关联

Question

现要求实现：律所,省律协，市律协听课周排行（名次，律所/省律协/市律协名称，听课率）
听课率=律所听课人数/律所总人数已知有律协表、律所表、律师表、用户表、听课记录表
律协表(lvxie)：lxid,lxname,proid,cityid（编号，律协名称，省，市）
数据如下：
---------------------------------------------------------------------
lxid     lxname   proid  cityid
1    北京市律协    1     1
2    山东身律协    2     0
3    青岛市律协    2     21
4    济南市律协    2     22
5    河北省律协    3     0

----------------------------------------------------------------------
     注:通过proid,cityid区分是省律协还是市律协。省律协：cityid=0;市律协:cityid<>0律所表(lvsuo)：lsid,lsname,proid,cityid(编号，律所名称，省，市)
数据如下：
---------------------------------------------------------------------
lsid     lsname   proid  cityid
1    青岛市aa律所  2     21
2    青岛市bb律所  2     22
3    北京市cc律所  1     1
4    河北省dd律所  3     0
5    山东省ee律所  2     0

----------------------------------------------------------------------
     注：通过proid,cityid确定律所所属的律协。律所的proid,cityid对应律协proid,cityid律师表(lvshi):lvshiid,lvshiname,lsid,userid(编号，律师名称，律所编号，用户编号)
数据如下：
---------------------------------------------------------------------
lvshiid lvshiname lsid userid
1         张三     3     2
2         李四     3     1
3         王五     5     3
4         赵六     5     4
5         朱七     5     5

----------------------------------------------------------------------
     注：lvshi表的lsid关联lvsuo的lsid，用来确定所属律所
                        lvshi表的userid关联users表userid,用来得到users表的登录名(memberid)用户表(users):userid,memberid,pwd(编号，登录名，密码)
数据如下：
---------------------------------------------------------------------
userid    memberid        pwd
1         lisi           123456    
2         zhangsan       123456    
3         wangwu         123456    
4         zhaoliu        123456    
5         zhuqi          123456    

----------------------------------------------------------------------
     注：users表的memberid关联stutime表的memberid听课记录表(stutime):stid,memberid,starttime,endtime,totaltime(编号，登录名，听课开始时间，听课结束时间，听课时长)
数据如下：
---------------------------------------------------------------------
stid    memberid            starttime          endtime               totaltime
1         lisi          2009-1-1 21:51:20    2009-1-1 23:09:11          78    
2         lisi          2009-1-2 21:50:20    2009-1-2 23:00:11          70   
3         lisi          2010-1-2 21:50:20    2009-1-2 21:50:20          70    
4         zhangsan      2010-1-2 21:50:20    2009-1-2 21:50:20          70
5         zhangsan      2010-1-2 21:50:20    2009-1-2 21:50:20          70  

----------------------------------------------------------------------
     注：计算听课时间用endtime

Answer 1

与之前发帖相同
http://topic.csdn.net/u/20101231/16/2055abdc-b29e-4559-b1b7-504c264e2a2b.html

Answer 2

declare @table table 
(
stid int,
memberid varchar(8),
starttime datetime,
endtime datetime,
totaltime int
)
insert into @table
select 1,'lisi','2009-1-1 21:51:20','2009-1-1 23:09:11',78 union all
select 2,'lisi','2009-1-2 21:50:20','2009-1-2 23:00:11',70 union all
select 3,'lisi','2010-1-2 21:50:20','2009-1-2 21:50:20',70 union all
select 4,'zhangsan','2010-1-2 21:50:20','2009-1-2 21:50:20',70 union all
select 5,'zhangsan','2010-1-2 21:50:20','2009-1-2 21:50:20',70select row_number() over (order by sum(totaltime) desc) as num,
memberid,sum(totaltime) as total
from @table group by memberid/*
num                  memberid total
-------------------- -------- -----------
1                    lisi     218
2                    zhangsan 140
*/
然后和之前的数据左连接即可。

Answer 3

declare @律协表 table (lxid int,lxname varchar(10),proid int,cityid int)
insert into @律协表
select 1,'北京市律协',1,1 union all
select 2,'山东身律协',2,0 union all
select 3,'青岛市律协',2,21 union all
select 4,'济南市律协',2,22 union all
select 5,'河北省律协',3,0declare @律所表 table (lsid int,lsname varchar(12),proid int,cityid int)
insert into @律所表
select 1,'青岛市aa律所',2,21 union all
select 2,'青岛市bb律所',2,22 union all
select 3,'北京市cc律所',1,1 union all
select 4,'河北省dd律所',3,0 union all
select 5,'山东省ee律所',2,0declare @律师表 table (lvshiid int,lvshiname varchar(4),lsid int,userid int)
insert into @律师表
select 1,'张三',3,2 union all
select 2,'李四',3,1 union all
select 3,'王五',5,3 union all
select 4,'赵六',5,4 union all
select 5,'朱七',5,5declare @用户表 table (userid int,memberid varchar(8),pwd int)
insert into @用户表
select 1,'lisi',123456 union all
select 2,'zhangsan',123456 union all
select 3,'wangwu',123456 union all
select 4,'zhaoliu',123456 union all
select 5,'zhuqi',123456declare @听课记录表 table (stid int,memberid varchar(8),starttime datetime,endtime datetime,totaltime int)
insert into @听课记录表
select 1,'lisi','2009-1-1 21:51:20','2009-1-1 23:09:11',78 union all
select 2,'lisi','2009-1-2 21:50:20','2009-1-2 23:00:11',70 union all
select 3,'lisi','2010-1-2 21:50:20','2009-1-2 21:50:20',70 union all
select 4,'zhangsan','2010-1-2 21:50:20','2009-1-2 21:50:20',70 union all
select 5,'zhangsan','2010-1-2 21:50:20','2009-1-2 21:50:20',70 union all
select 6,'zhuqi','2010-1-2 21:50:20','2009-1-2 21:50:20',70/*
select lsid,lxname,lsname,lvshiname,totaltime from (
select a.lsid,b.lxname,a.lsname,c.lvshiname,e.totaltime from @律所表 a
left join @律协表 b on a.proid=b.proid and a.cityid=b.cityid
left join @律师表 c on a.lsid=c.lsid
left join @用户表 d on c.userid=d.userid
left join @听课记录表 e on d.memberid=e.memberid)
aa 
*/
select row_number() over (order by 听课率 desc) as 名次,
lxname,lsname,听课率
 from 
(
select lsid,lxname,lsname,sum(mycount) as 总人数,count(mytotal) as 听课人数,
cast(count(mytotal) as float)/(case when isnull(sum(mycount),0)=0 then 1 else sum(mycount) end) as 听课率
 from (
select lsid,lxname,lsname,count(distinct lvshiname) as mycount,memberid,sum(totaltime)as mytotal from (
select a.lsid,b.lxname,a.lsname,d.memberid,c.lvshiid,c.lvshiname,e.stid,e.totaltime from @律所表 a
left join @律协表 b on a.proid=b.proid and a.cityid=b.cityid
left join @律师表 c on a.lsid=c.lsid
left join @用户表 d on c.userid=d.userid
left join @听课记录表 e on d.memberid=e.memberid)
aa group by lsid,lxname,lsname,memberid
) bb group by lsid,lxname,lsname
) cc
/*
名次                   lxname     lsname       听课率
-------------------- ---------- ------------ ----------------------
1                    北京市律协      北京市cc律所      1
2                    山东身律协      山东省ee律所      0.333333333333333
3                    河北省律协      河北省dd律所      0
4                    青岛市律协      青岛市aa律所      0
5                    济南市律协      青岛市bb律所      0
*/

Answer 4

谢谢maco_wang
我把你的代码运行了一下，可以执行，虽然与我想要的有点不同。但是执行时间用的很长。因为数据量很大。
我的思路是要把律所，省、市律协分开查。而且还要根据他们上周一到上周日的时间来查。