多表关联的查询与统计语句

Question

最好写一个存储过程来实现用sql语句也可以，不过太烦用group by加union等等可以出来的饿没时间写了

Answer 1

create table #customerclass(ccusccode varchar(10),ccuscname varchar(10),icuscgrade int,bcuscend int)
insert #customerclass values('01','华东区'    ,    1    ,     0)
insert #customerclass values('0101','上海'   ,       2   ,      1)
insert #customerclass values('0102','浙江'  ,        2    ,     1)
insert #customerclass values('02','华北区' ,       1       ,  0)
insert #customerclass values('0201','北京',          2      ,   1)
create table #customer(ccuscode varchar(10),ccusname varchar(100),ccusccode varchar(100))
insert #customer values('aaa','上海公司1','0101')
insert #customer values('bbb','上海公司2','0101')
insert #customer values('ccc','浙江公司1','0102')
insert #customer values('ddd','浙江公司2','0102')
insert #customer values('eee','北京公司1','0201')
create table #sale(ccuscode varchar(100),fsalemoney int)
insert #sale values('aaa',         100)
insert #sale values('aaa',         150)
insert #sale values('bbb',         210)
insert #sale values('ddd',         500)
insert #sale values('ccc',         52)
insert #sale values('ddd',         411)
insert #sale values('eee',         400)select SPACE(len(ccusccode)-2)+分类与名称 分类与名称,金额 from(
select ccusccode,ccuscname 分类与名称,(select sum(fsalemoney) from #sale where ccuscode in (select ccuscode from #customer where ccusccode like #customerclass.ccusccode+'%')) 金额 from #customerclass
union all
select a.ccusccode+'01',a.ccusname,b.fsalemoney  from #customer a left join (select ccuscode,sum(fsalemoney) fsalemoney from #sale group by ccuscode) b on a.ccuscode=b.ccuscode
) a order by ccusccode
godrop table #sale,#customer,#customerclass

Answer 2

select 分类与名称,金额 from
(Select a.ccuscname as 分类与名称,sum(c.fsalemoney) as 金额,a.ccusccode
from customerclass a 
join customer b on a.ccusccode = b.ccusccode
join sale c on b.ccuscode = c.ccuscode
group by a.ccuscname,a.ccusccode
union
Select b.ccusname,sum(c.fsalemoney),b.ccuscode 
from customer b
join sale c on b.ccuscode = c.ccuscode
group by b.ccusname,b.ccuscode
) temp
order by ccusccode

Answer 3

用 ROLLUP 汇总数据
在生成包含小计和合计的报表时，ROLLUP 运算符很有用。ROLLUP 运算符生成的结果集类似于 CUBE 运算符所生成的结果集。有关更多信息，请参见用 CUBE 汇总数据。 CUBE 和 ROLLUP 之间的区别在于： CUBE 生成的结果集显示了所选列中值的所有组合的聚合。
ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。 
例如，简单表 Inventory 中包含：Item                 Color                Quantity                   
-------------------- -------------------- -------------------------- 
Table                Blue                 124                        
Table                Red                  223                        
Chair                Blue                 101                        
Chair                Red                  210                        下列查询将生成小计报表：SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
            ELSE ISNULL(Item, 'UNKNOWN')
       END AS Item,
       CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
            ELSE ISNULL(Color, 'UNKNOWN')
       END AS Color,
       SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUPItem                 Color                QtySum                     
-------------------- -------------------- -------------------------- 
Chair                Blue                 101.00                     
Chair                Red                  210.00                     
Chair                ALL                  311.00                     
Table                Blue                 124.00                     
Table                Red                  223.00                     
Table                ALL                  347.00                     
ALL                  ALL                  658.00                     (7 row(s) affected)如果查询中的 ROLLUP 关键字更改为 CUBE，那么 CUBE 结果集与上述结果相同，只是在结果集的末尾还会返回下列两行：ALL                  Blue                 225.00                     
ALL                  Red                  433.00                     CUBE 操作为 Item 和 Color 中值的可能组合生成行。例如，CUBE 不仅报告与 Item 值 Chair 相组合的 Color 值的所有可能组合（Red、Blue 和 Red + Blue），而且报告与 Color 值 Red 相组合的 Item 值的所有可能组合（Chair、Table 和 Chair + Table）。对于 GROUP BY 子句中右边的列中的每个值，ROLLUP 操作并不报告左边一列（或左边各列）中值的所有可能组合。例如，ROLLUP 并不对每个 Color 值报告 Item 值的所有可能组合。ROLLUP 操作的结果集具有类似于 COMPUTE BY 所返回结果集的功能；然而，ROLLUP 具有下列优点： ROLLUP 返回单个结果集；COMPUTE BY 返回多个结果集，而多个结果集会增加应用程序代码的复杂性。
ROLLUP 可以在服务器游标中使用；COMPUTE BY 不可以。
有时，查询优化器为 ROLLUP 生成的执行计划比为 COMPUTE BY 生成的更为高效。

Answer 4

into #1 from customer a ,sale b
where a.ccuscode =b.ccuscode
group by a.ccuscode
go
select a.ccusccode ccusccode ,sum(a.amt) amt
into #2 from #1 a ,customer b
where a.ccuscode =b.ccuscode
group by a.ccusccode
go
select left(#2.ccusccode,2) ccusccode,sum(#2.amt) amt
into #3
from #2
group by left(#2.ccusccode,2)
go
select ccusccode ,amt 
from #1
union
select ccusccode ,amt 
from #2
union
select ccusccode ,amt 
from #3
go
结果是代码，你再把它换成名称。

Answer 5

用 ROLLUP 汇总数据
在生成包含小计和合计的报表时，ROLLUP 运算符很有用。ROLLUP 运算符生成的结果集类似于 CUBE 运算符所生成的结果集。有关更多信息，请参见用 CUBE 汇总数据。 CUBE 和 ROLLUP 之间的区别在于： CUBE 生成的结果集显示了所选列中值的所有组合的聚合。
ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。 
例如，简单表 Inventory 中包含：Item                 Color                Quantity                   
-------------------- -------------------- -------------------------- 
Table                Blue                 124                        
Table                Red                  223                        
Chair                Blue                 101                        
Chair                Red                  210                        下列查询将生成小计报表：SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
            ELSE ISNULL(Item, 'UNKNOWN')
       END AS Item,
       CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
            ELSE ISNULL(Color, 'UNKNOWN')
       END AS Color,
       SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUPItem                 Color                QtySum                     
-------------------- -------------------- -------------------------- 
Chair                Blue                 101.00                     
Chair                Red                  210.00                     
Chair                ALL                  311.00                     
Table                Blue                 124.00                     
Table                Red                  223.00                     
Table                ALL                  347.00                     
ALL                  ALL                  658.00                     (7 row(s) affected)如果查询中的 ROLLUP 关键字更改为 CUBE，那么 CUBE 结果集与上述结果相同，只是在结果集的末尾还会返回下列两行：ALL                  Blue                 225.00                     
ALL                  Red                  433.00                     CUBE 操作为 Item 和 Color 中值的可能组合生成行。例如，CUBE 不仅报告与 Item 值 Chair 相组合的 Color 值的所有可能组合（Red、Blue 和 Red + Blue），而且报告与 Color 值 Red 相组合的 Item 值的所有可能组合（Chair、Table 和 Chair + Table）。对于 GROUP BY 子句中右边的列中的每个值，ROLLUP 操作并不报告左边一列（或左边各列）中值的所有可能组合。例如，ROLLUP 并不对每个 Color 值报告 Item 值的所有可能组合。ROLLUP 操作的结果集具有类似于 COMPUTE BY 所返回结果集的功能；然而，ROLLUP 具有下列优点： ROLLUP 返回单个结果集；COMPUTE BY 返回多个结果集，而多个结果集会增加应用程序代码的复杂性。
ROLLUP 可以在服务器游标中使用；COMPUTE BY 不可以。
有时，查询优化器为 ROLLUP 生成的执行计划比为 COMPUTE BY 生成的更为高效。

Answer 6

create table #customerclass(ccusccode varchar(10),ccuscname varchar(10),icuscgrade int,bcuscend int)
insert #customerclass values('01','华东区'    ,    1    ,     0)
insert #customerclass values('0101','上海'   ,       2   ,      1)
insert #customerclass values('0102','浙江'  ,        2    ,     1)
insert #customerclass values('02','华北区' ,       1       ,  0)
insert #customerclass values('0201','北京',          2      ,   1)create table #customer(ccuscode varchar(10),ccusname varchar(100),ccusccode varchar(100))
insert #customer values('aaa','上海公司1','0101')
insert #customer values('bbb','上海公司2','0101')
insert #customer values('ccc','浙江公司1','0102')
insert #customer values('ddd','浙江公司2','0102')
insert #customer values('eee','北京公司1','0201')
create table #sale(ccuscode varchar(100),fsalemoney int)
insert #sale values('aaa',         100)
insert #sale values('aaa',         150)
insert #sale values('bbb',         210)
insert #sale values('ddd',         500)
insert #sale values('ccc',         52)
insert #sale values('ddd',         411)
insert #sale values('eee',         400)select 编号,SPACE(len(ccusccode)-2)+分类与名称 分类与名称,金额 from(
select ccusccode 编号,ccusccode,ccuscname 分类与名称,(select sum(fsalemoney) from #sale where ccuscode in (select ccuscode from #customer where ccusccode like #customerclass.ccusccode+'%')) 金额 from #customerclass
union all
select ccusccode,a.ccusccode+'01',a.ccusname,b.fsalemoney  from #customer a left join (select ccuscode,sum(fsalemoney) fsalemoney from #sale group by ccuscode) b on a.ccuscode=b.ccuscode
) a order by ccusccode
godrop table #sale,#customer,#customerclass

Answer 7

谢谢大力! 可扩展后我还是查不出来？(另：刚才的客户编号也变成分类编号了，也就是说，所有同一分类的客户编号都是该分类的编号，而不是真正的客户编号)表1：customerclass
     ccusccode  ccuscname  icuscgrade bcuscend
        01       华东区        1         0
       0101      上海          2         1
       0102      浙江          2         1
        02       华北区        1         0
       0201      北京          2         1
       ...表2：customer
       ccuscode    ccusname   ccusccode
         aaa       上海公司1     0101
         bbb       上海公司2     0101
         ccc       浙江公司1     0102
         ddd       浙江公司2     0102
         eee       北京公司1     0201  
        ...
表3：sale
        ccuscode   fsalemoney  fquantity
          aaa         100          10
          aaa         150          15
          bbb         210          21 
          ddd         500          50
          ccc         52           5.2
          ddd         411          41.1
          eee         400          40
          ...
表4：cost
        ccuscode fcostprice 
           aaa      9
           bbb      8.5
           ccc      8
           ddd      7
           eee      8.2
          ...
    
要求结果：
       分类与名称      销售金额    销售成本(fquantity*fcostprice)
      华东区            1423         
        上海            460
          上海公司1     250
          上海公司2     210
        浙江            963
          浙江公司1     52
          浙江公司2     911
      华北区            400
        北京            400
          北京公司1     400
      ......   

Answer 8

谢谢大力!可扩展后我还是查不出来？(另：刚才加了编号后发现客户名称前的编号全是该客户所属分类的分类编号，而不是该客户档案的编号)表1：customerclass
     ccusccode  ccuscname  icuscgrade bcuscend
        01       华东区        1         0
       0101      上海          2         1
       0102      浙江          2         1
        02       华北区        1         0
       0201      北京          2         1
       ...表2：customer
       ccuscode    ccusname   ccusccode
         aaa       上海公司1     0101
         bbb       上海公司2     0101
         ccc       浙江公司1     0102
         ddd       浙江公司2     0102
         eee       北京公司1     0201  
        ...
表3：sale
        ccuscode   fsalemoney   fquantity
          aaa         100          10
          aaa         150          15
          bbb         210          21 
          ddd         500          50
          ccc         52           5.2
          ddd         411          41.1
          eee         400          40
          ...
表4：cost
       ccuscode      fcostprice
          aaa           9
          bbb           8.5
          ccc           8
          ddd           7.7
         ...    
要求结果：
  编号     分类与名称        销售金额    销售成本(fquantity*fcostprice)
  01        华东区            1423
   0101       上海            460
     aaa         上海公司1     250          
     bbb         上海公司2     210          
   0102       浙江            963
     ccc         浙江公司1     52
     ddd         浙江公司2     911
   02      华北区            400
     0201     北京            400
       eee       北京公司1     400
             ......   

Answer 9

select 编号,SPACE(len(ccusccode)-2)+分类与名称 分类与名称,金额 from(
select ccusccode 编号,ccusccode,ccuscname 分类与名称,(select sum(fsalemoney) from #sale where ccuscode in (select ccuscode from #customer where ccusccode like #customerclass.ccusccode+'%')) 金额 from #customerclass
union all
select ccusccode,a.ccusccode+'01',SPACE(4)+a.ccusname,b.fsalemoney  from #customer a left join (select ccuscode,sum(fsalemoney) fsalemoney from #sale group by ccuscode) b on a.ccuscode=b.ccuscode
) a order by ccusccode
go

Answer 10

你改一下列名不就可以了？？create table #customerclass(ccusccode varchar(10),ccuscname varchar(10),icuscgrade int,bcuscend int)
insert #customerclass values('01','华东区'    ,    1    ,     0)
insert #customerclass values('0101','上海'   ,       2   ,      1)
insert #customerclass values('0102','浙江'  ,        2    ,     1)
insert #customerclass values('02','华北区' ,       1       ,  0)
insert #customerclass values('0201','北京',          2      ,   1)create table #customer(ccuscode varchar(10),ccusname varchar(100),ccusccode varchar(100))
insert #customer values('aaa','上海公司1','0101')
insert #customer values('bbb','上海公司2','0101')
insert #customer values('ccc','浙江公司1','0102')
insert #customer values('ddd','浙江公司2','0102')
insert #customer values('eee','北京公司1','0201')
create table #sale(ccuscode varchar(100),fsalemoney int)
insert #sale values('aaa',         100)
insert #sale values('aaa',         150)
insert #sale values('bbb',         210)
insert #sale values('ddd',         500)
insert #sale values('ccc',         52)
insert #sale values('ddd',         411)
insert #sale values('eee',         400)select SPACE(len(ccusccode)-2)+编号 编号,SPACE(len(ccusccode)-2)+分类与名称 分类与名称,金额 from(
select ccusccode 编号,ccusccode,ccuscname 分类与名称,(select sum(fsalemoney) from #sale where ccuscode in (select ccuscode from #customer where ccusccode like #customerclass.ccusccode+'%')) 金额 from #customerclass
union all
select a.ccuscode,a.ccusccode+'01',a.ccusname,b.fsalemoney  from #customer a left join (select ccuscode,sum(fsalemoney) fsalemoney from #sale group by ccuscode) b on a.ccuscode=b.ccuscode
) a order by ccusccode
godrop table #sale,#customer,#customerclass

Answer 11

你改一下列名不就可以了？？create table #customerclass(ccusccode varchar(10),ccuscname varchar(10),icuscgrade int,bcuscend int)
insert #customerclass values('01','华东区'    ,    1    ,     0)
insert #customerclass values('0101','上海'   ,       2   ,      1)
insert #customerclass values('0102','浙江'  ,        2    ,     1)
insert #customerclass values('02','华北区' ,       1       ,  0)
insert #customerclass values('0201','北京',          2      ,   1)create table #customer(ccuscode varchar(10),ccusname varchar(100),ccusccode varchar(100))
insert #customer values('aaa','上海公司1','0101')
insert #customer values('bbb','上海公司2','0101')
insert #customer values('ccc','浙江公司1','0102')
insert #customer values('ddd','浙江公司2','0102')
insert #customer values('eee','北京公司1','0201')
create table #sale(ccuscode varchar(100),fsalemoney int)
insert #sale values('aaa',         100)
insert #sale values('aaa',         150)
insert #sale values('bbb',         210)
insert #sale values('ddd',         500)
insert #sale values('ccc',         52)
insert #sale values('ddd',         411)
insert #sale values('eee',         400)select SPACE(len(ccusccode)-2)+编号 编号,SPACE(len(ccusccode)-2)+分类与名称 分类与名称,金额 from(
select ccusccode 编号,ccusccode,ccuscname 分类与名称,(select sum(fsalemoney) from #sale where ccuscode in (select ccuscode from #customer where ccusccode like #customerclass.ccusccode+'%')) 金额 from #customerclass
union all
select a.ccuscode,a.ccusccode+'01',a.ccusname,b.fsalemoney  from #customer a left join (select ccuscode,sum(fsalemoney) fsalemoney from #sale group by ccuscode) b on a.ccuscode=b.ccuscode
) a order by ccusccode
godrop table #sale,#customer,#customerclass

Answer 12

再问大力，下面语句能优化吗，特别是字段超过四个时查询速度奇慢(#sale只有5万条记录，在查询分析器中查询却要2分15秒。金额成成本能不能在一个子查询中得出？)select SPACE(len(ccusccode)-2)+编号 编号,SPACE(len(ccusccode)-2)+分类与名称 分类与名称,金额,成本 from(select ccusccode 编号,ccusccode,ccuscname 分类与名称,
(select sum(fsalemoney) from #sale where ccuscode in (select ccuscode from #customer where ccusccode like #customerclass.ccusccode+'%')) 金额,
(select sum(fquantity*fcostprice) from #sale,#cost where #sale.ccuscode=#cost.ccuscode and ccuscode in (select ccuscode from #customer where ccusccode like #customerclass.ccusccode+'%')) 成本
 from #customerclass
union all
select a.ccuscode,a.ccusccode+'01',a.ccusname,b.fsalemoney,b.fcostmoney  from #customer a left join (select ccuscode,sum(fsalemoney) fsalemoney,sum(fquantity*fcostprice) fcostmoney from #sale,#cost where #sale.ccuscode=#cost.ccuscode group by ccuscode) b on a.ccuscode=b.ccuscode
) a order by ccusccode

Answer 13

create table #customerclass(ccusccode varchar(10),ccuscname varchar(10),icuscgrade int,bcuscend int)
insert #customerclass values('01','华东区'    ,    1    ,     0)
insert #customerclass values('0101','上海'   ,       2   ,      1)
insert #customerclass values('0102','浙江'  ,        2    ,     1)
insert #customerclass values('02','华北区' ,       1       ,  0)
insert #customerclass values('0201','北京',          2      ,   1)create table #customer(ccuscode varchar(10),ccusname varchar(100),ccusccode varchar(100))
insert #customer values('aaa','上海公司1','0101')
insert #customer values('bbb','上海公司2','0101')
insert #customer values('ccc','浙江公司1','0102')
insert #customer values('ddd','浙江公司2','0102')
insert #customer values('eee','北京公司1','0201')
create table #sale(ccuscode varchar(100),fsalemoney int)
insert #sale values('aaa',         100)
insert #sale values('aaa',         150)
insert #sale values('bbb',         210)
insert #sale values('ddd',         500)
insert #sale values('ccc',         52)
insert #sale values('ddd',         411)
insert #sale values('eee',         400)create table #cost(ccuscode varchar(100),fcostprice numeric(10,2))
insert  #cost values('aaa',           9)
insert  #cost values('bbb',           8.5)
insert  #cost values('ccc',           8)
insert  #cost values('ddd',           7.7)select a.ccuscode,sum(a.fsalemoney) fsalemoney,sum(a.fsalemoney)*isnull(sum(b.fcostprice),1) 销售成本 into # from #sale a left join #cost b on a.ccuscode=b.ccuscode group by a.ccuscodeselect SPACE(len(ccusccode)-2)+编号 编号,SPACE(len(ccusccode)-2)+分类与名称 分类与名称,金额,销售成本 from(select aa.ccusccode 编号,aa.ccusccode,aa.ccuscname 分类与名称,sum(cc.fsalemoney) 金额,sum(cc.销售成本) 销售成本 from #customerclass aa join #customer bb on bb.ccusccode like aa.ccusccode+'%' join # cc on bb.ccuscode=cc.ccuscode group by aa.ccusccode,aa.ccuscname union all select a.ccuscode,a.ccusccode+'01',a.ccusname,b.fsalemoney,b.销售成本  from #customer a left join # b on a.ccuscode=b.ccuscode) a order by ccusccode
godrop table #sale,#customer,#customerclass,#cost,#