多边统计查询

Question

表1：店面表（Shop）
字段：ShopID(店面编号),ShopName(店面名称)
数据：1                  测试1店
      2                  测试2店表2：商品销售表（GoodsOper）
字段：Money(现金收费),Card(会员卡收费),ShopID(店面编号),EmpID(操作员ID)
数据： 10              20                  1                1
       10              10                  1                2
       20              10                  2                1表3：IC卡操作表(ICOper)
字段：OperType(操作类型：1为挂失，2为解挂，3为充值),Money(现金收费),Send(赠送),ShopID(店面编号),EmpID(操作员ID)
备注：只有充值产生费用
数据： 3                                               10              0            1               1
       1                                                0              0            1               1
       1                                                0              0            1               2
       1                                                0              0            2               2表4 操作员表（employees）
    EmpID(操作员ID),Name(姓名)
    1               张三
    2               李四
需要的查询结果
店面    现金收费（销售） 会员卡（销售） 现金收费（充值）  赠送（充值） 挂失 解挂 
测试1店  20                30             10                0           2    0      
测试2店  20                20             0                 0           1    0谢谢大家～

Answer 1

select ... from ICOper group by ShopID

Answer 2

select
   a.ShopName,b.Money,b.Card,sum(c.Money),sum(c.Send),
   max(case OperType when 1 then Money else 0 end) as 挂失,
   max(case OperType when 2 then Money else 0 end) as 解挂
from
   Shop a,GoodsOper b,ICOper c,employees d
where 
    a.ShopID=b.ShopID
and 
    a.ShopID=c.ShopID
and
    b.EmpID=d.EmpID
and  
    c.EmpID=d.EmpID
group by
    a.ShopName,b.Money,b.Card
   

Answer 3

select a.shopname,a.Money,a.Card, b.money,b.send,b.挂失,b.解挂
from
(
select s.shopname,s.shopid,sum(g.money)money,sum(g.card)card from shop s left join goodsoper g on s.shopid = g.shopid
 group by s.shopid,s.shopname
) a left join 
(
select sum(money)money,sum(send)send,shopId,sum(挂失) 挂失,sum(解挂) 解挂 
from
(
select sum(money)money,sum(send)send,shopId,empID,sum(case when opertype = 1 then 1 end)挂失,
isnull(sum(case when opertype = 2 then 1 end),0)解挂 from icoper group by shopid ,empid
) t group by shopId
) b
 on a.shopid = b.shopid
/*
shopname,Money,Card,money,send,挂失,解挂
测试1店,20,30,10,0,2,0
测试2店,20,10,0,0,1,0
警告: 聚合或其他 SET 操作消除了空值。(2 行受影响)有点乱

Answer 4

select isnull(m1.店面,m2.店面) 店面 , m1.[现金收费（销售）],m1.[会员卡（销售）],m2.那些字段，自己补上 from
(
select t1.ShopName 店面,
       sum(t2.Money) [现金收费（销售）],
       sum(t2.Card) [会员卡（销售）],
from shop t1 , GoodsOper t2 where t1.ShopID = t2.ShopID group by t1.ShopID
) m1
full join
(
select t1.ShopName 店面,
       sum(case when t2.opertype = 3 then t2.Money else 0 end) [现金收费（充值）],
       sum(case when t2.opertype = 3 then t2.Send else 0 end) [赠送（充值）],
       sum(case when t2.opertype = 1 then t2.Money else 0 end) [现金收费（挂失）],
       sum(case when t2.opertype = 1 then t2.Send else 0 end) [赠送（挂失）],
       sum(case when t2.opertype = 2 then t2.Money else 0 end) [现金收费（解挂）],
       sum(case when t2.opertype = 2 then t2.Send else 0 end) [赠送（解挂）]
from shop t1 , ICOper t2 where t1.ShopID = t2.ShopID group by t1.ShopID
) m2
on m1.店面 = m2.店面