表格模板-多表统计分析 精品.xls

《表格模板-多表统计分析 精品.xls》由会员分享，可在线阅读，更多相关《表格模板-多表统计分析 精品.xls（65页珍藏版）》请在第一文库网上搜索。

1、问题：表A: IdNumber100151002610037 表B： IDMoney100110100712 我要的结果是： IDNumberMoney100151010026Null10037Null1007Null12解决方案：selectCOALESCE(a.id,b.id)asID,sum(a.number)number,sum(b.Money)MoneyfromAfulljoinBona.id=b.idgroupbyCOALESCE(a.id,b.id)问题：我要做一个多表统计。有这样四个表 USER(用户名，部门，职务) Table1(序号1，用户名，工作一) Table2(序号2

2、，用户名，工作二) Table3(序号3，用户名，工作三) 其中，User表为用户信息表，存放所有的用户基本信息，后三个表的用户名均为User表中的用户。我想作一个统计，统计每一个用户作“工作一”、“工作一”、“工作一”的次数。相当于将后三个表分别作统计，然后汇总成一张表。结果显示为如下形式： 用户名工作一次数工作二次数工作三次数 a579b0765c45544d900 其中，0表示在该表在没有该用户的记录。 请问各位高手，该如何做？ 解决方案1:select a.用户名, 工作一次数 = (select isnull(count(*),0) from table1 where 用户名=a.用

3、户名), 工作二次数 = (select isnull(count(*),0) from table2 where 用户名=a.用户名), 工作三次数 = (select isnull(count(*),0) from table3 where 用户名=a.用户名) fromUSERaorderby a.用户名解决方案2：select a.用户名,工作一次数=sum(case when b.工作一 is null then 0 else 1 ), 工作二次数=sum(case when c.工作二 is null then 0 else 1 ), 工作三次数=sum(case when d.工

4、作三 is null then 0 else 1 ), from USER a left join table1 b on a.用户名 = b.用户名 left join table2 c on a.用户名 = c.用户名 left join table3 d on a.用户名 = d.用户名 group by a.用户名问题：现有cpb和xsqkb两个表cpb数据如下产品号 产品型号 生产厂家 单价CP001 KF-25GW/11BP 海信 2300CP002 KF-25GW/22BP 海信 2300CP003 KFR-35GW/29BP 海信 3600CP004 KFR-25GW/99SZ

5、BP 海信 5300CP005 KFR-26GW/76ZBP 海信 3400CP006 KFR-23GW/77VZBP 海信 3000CP007 KFR-32GW/76ZBP 海信 3700CP008 KFR-32GW/77VZBP 海信 3600CP009 KFR-35GW/77ZBP 海信 4000CP010 KFR-40GW/77ZBP 海信 4600CP011 KFR-50GW/09BP 海信 5400CP012 KFR-32GW/29RBP 海信 3600CP013 KFR-32GW/11BP 海信 3400 xsqkb 表 数据 产品号 职工号 销售时间 销售数量 CP001ZG0

6、012006-10-0300:00:001CP001ZG0062006-11-1100:00:009CP002ZG0022006-09-2100:00:008CP002ZG0092006-05-3000:00:004CP003ZG0042005-02-2300:00:003CP003ZG0042006-03-1000:00:003CP003ZG0042006-05-0200:00:003CP003ZG0062006-02-1200:00:007CP004ZG0072005-06-1800:00:008现求销售销量排前三位的空调的型号,生产厂家和单价我给出的SQL语句为select distin

7、ct 产品型号 ,生产厂家,单价 from cpb inner join xsqkb on cpb.产品号=xsqkb.产品号where cpb.产品号 in (select top 3 with ties 产品号 as aa from xsqkb groupby(cph)orderbyaadesc)虽然能解决问题但自己感觉很别扭,请帮我改一下,看能不能多给几个比较好的解决办法.解决方案1：语句应该是这样:selectdistinct产品型号,生产厂家,单价from cpb inner join xsqkb on cpb.产品号=xsqkb.产品号 where cpb.产品号 in (sele

8、cttop3产品号,sum(销售量)asxslfromxsqkb group by (产品号) orderbyxsldesc)解决方案2：select产品型号,生产厂家,单价fromcpbwherecpb.产品号in(selecttop3产品号,sum(销售量)asxslfromxsqkb group by (产品号) orderbyxsldesc)问题：主从表结构: 主表结构及数据: 授课表1 id 老师姓名 所带学生档案号 团体/个人 2 王二 1 团体 3 王二 2 团体 4 王二 3 团体 5 王二 4 团体 6 王二 5 个人 7 王二 6 个人 8 王二 7 个人 9 王二 8 个

9、人 10 李四 12 个人 授课表2(从表) 对应表1id 所学课目 所需金额(元/小时) 2 数学 10 3 数学 10 4 数学 10 5 数学 10 6 英文 10 7 英文 10 8 中文 10 9 中文 10 10 中文 10 10 英文 10 10 数学 10 现要求作出如下表，不能使用临表， 可出sql server和oracle 下通用 不必一条语句完成 人数 所带课程(数量) 所需金额(元/小时) - 老师 小 团 个 小 英 数 中 小 英 数 中 姓名 计 体 人 计 文 学 文 计 文 学 文 - 王二 8 4 4 8 2 4 2 80 20 40 20 李四 1 1

10、3 1 1 1 30 10 10 10 - 合计 9 4 5 11 3 5 3 110 30 50 30 解决方案1：恐怕没那么容易写成通用的SQL语句，给你一个SQL Server的： - -生成测试数据 create table 授课表1(id int,老师姓名 varchar(20),所带学生档案号 int,团体/个人 varchar(20) insert into 授课表1 select 2 ,王二,1 ,团体 insert into 授课表1 select 3 ,王二,2 ,团体 insert into 授课表1 select 4 ,王二,3 ,团体 insert into 授课表1

11、 select 5 ,王二,4 ,团体 insert into 授课表1 select 6 ,王二,5 ,个人 insert into 授课表1 select 7 ,王二,6 ,个人 insert into 授课表1 select 8 ,王二,7 ,个人 insert into 授课表1 select 9 ,王二,8 ,个人 insert into 授课表1 select 10,李四,12,个人 create table 授课表2(授课表1id int,所学课目 varchar(20),所需金额 int) insert into 授课表2 select 2 ,数学,10 insert into

12、 授课表2 select 3 ,数学,10 insert into 授课表2 select 4 ,数学,10 insert into 授课表2 select 5 ,数学,10 insert into 授课表2 select 6 ,英文,10 insert into 授课表2 select 7 ,英文,10 insert into 授课表2 select 8 ,中文,10 insert into 授课表2 select 9 ,中文,10 insert into 授课表2 select 10,中文,10 insert into 授课表2 select 10,英文,10 insert into 授课

13、表2 select 10,数学,10 -执行交叉表查询 declaresvarchar(8000),str1varchar(4000),str2varchar(4000)setstr1=setstr2=select str1 = str1 + ,+所学课目+=sum(case 所学课目 when +所学课目+ then 1 else 0 end), str2 = str2 + ,+所学课目+=sum(case 所学课目 when +所学课目+ then 所需金额 else 0 end) from 授课表2 groupby 所学课目 set s = select a.老师姓名 +,小计=coun

14、t(distinct a.id) +,团体=count(distinct case a.团体/个人 when 团体 then a.id end) +,个人=count(distinct case a.团体/个人 when 个人 then a.id end) +,小计=sum(1)+str1+ +,小计=sum(b.所需金额)+str2+ + from 授课表1 a,授课表2 b where a.id=b.授课表1id group by 老师姓名 +union + select 合计 +,(select count(distinct id) from 授课表1) +,(select count(

15、distinct id) from 授课表1 where 团体/个人=团体) +,(select count(distinct id) from 授课表1 where 团体/个人=个人) +,小计=sum(1)+str1+ +,小计=sum(b.所需金额)+str2+ + from 授课表1 a,授课表2 b where a.id=b.授课表1id order by 老师姓名 desc exec(s) -输出结果 /* 老师姓名 小计 团体 个人 小计 英文 数学 中文 小计 英文 数学 中文 - 王二 8 4 4 8 2 4 2 80 20 40 20 李四 1 1 3 1 1 1 30 1

16、0 10 10 合计 9 4 5 11 3 5 3 110 30 50 30 */解决方案2： create table 授课表1 (idint, 老师姓名 varchar(10), 所带学生档案号 int, 团体/个人 varchar(10) ) create table 授课表2 ( 授课表1id int, 所学课目 varchar(10), 所需金额(元/小时) int ) insert 授课表1 select 2,王二,1,团体 union all select 3,王二,2,团体 union all select 4,王二,3,团体 union all select 5,王二,4,团体 union all select 6,王二,5,个人 union all select 7,王二,6,个人 union all select 8,王二,7,个人 union all select 9,王二,8,个人 union all select 10,李四,12,个人 insert 授课表2 select 2,数学,10 union all select 3,数学,10 union all