公司要取个表报,求帮助

好奇都是要学的 2016-03-21 12:53:10
有7个店,他们有会员卡。 想取往来账目,就是A店的会员卡 去其他几个店花了多钱,其他几个店的会员在A店花了多钱。谁能帮帮我写个简单的。我写的太麻烦
--本店在其他店商品消费
select d.deptname, b.deptname deptnameM,sum(a.TotalAmt) Price into #a from dbo.CusOrderM a
left join basdept b on a.deptno=b.deptno
inner join Customer c on a.CustomerNo =c.CustomerNo
left join basdept d on c.deptno =d.deptno
where a.deptno<>'010101' and c.deptno='010101' and a.paytype=0 and a.IfFree=0 and a.docno
not in(select orderno from OrderReservation where a.docno=orderno) and a.DocDate>='2016-1-1' and a.DocDate<'2016-5-1'
group by d.deptname, b.deptname
--本店在其他店服务消费
select d.deptname, b.deptname deptnameM,sum(a.Price) Price into #b from dbo.OrderReservation a
left join basdept b on a.deptno=b.deptno
inner join Customer c on a.CustomerNo =c.CustomerNo
left join basdept d on c.deptno =d.deptno
where a.deptno<>'010101' and c.deptno='010101' and a.paytype=0 and a.IfFree=0 and a.PlaceTime>='2016-1-1' and a.PlaceTime<'2016-5-1'
group by d.deptname, b.deptname
--本店在其他店续卡
select d.deptname, b.deptname deptnameM,-sum(a.RechAmount) Price into #c from RechargeRecord a
left join basdept b on a.deptno=b.deptno
inner join Customer c on a.CustomerNo =c.CustomerNo
left join basdept d on c.deptno =d.deptno
where a.deptno<>'010101' and c.deptno='010101' and a.Rechtime>='2016-1-1' and a.Rechtime<'2016-5-1'
group by d.deptname, b.deptname
--其他店在本店商品消费
select d.deptname, b.deptname deptnameM,sum(a.TotalAmt) Price into #d from dbo.CusOrderM a
left join basdept b on a.deptno=b.deptno
inner join Customer c on a.CustomerNo =c.CustomerNo
left join basdept d on c.deptno =d.deptno
where a.deptno='010101' and c.deptno<>'010101' and a.paytype=0 and a.IfFree=0
and a.docno not in(select orderno from OrderReservation where a.docno=orderno) and a.DocDate>='2016-1-1' and a.DocDate<'2016-5-1'
group by d.deptname, b.deptname
--其他店在本店服务消费
select d.deptname, b.deptname deptnameM,sum(a.Price) Price into #e from dbo.OrderReservation a
left join basdept b on a.deptno=b.deptno
inner join Customer c on a.CustomerNo =c.CustomerNo
left join basdept d on c.deptno =d.deptno
where a.deptno='010101' and c.deptno<>'010101' and a.paytype=0 and a.IfFree=0 and a.PlaceTime>='2016-1-1' and a.PlaceTime<'2016-5-1'
group by d.deptname, b.deptname
--其他店在本店续卡
select d.deptname, b.deptname deptnameM,-sum(a.RechAmount) Price into #f from RechargeRecord a
left join basdept b on a.deptno=b.deptno
inner join Customer c on a.CustomerNo =c.CustomerNo
left join basdept d on c.deptno =d.deptno
where a.deptno='010101' and c.deptno<>'010101' and a.Rechtime>='2016-1-1' and a.Rechtime<'2016-5-1'
group by d.deptname, b.deptname
--取本店和其他店来往帐
select * into #dept from (
select #a.deptname,#a.deptnameM from #a
union
select #b.deptname,#b.deptnameM from #b
union
select #c.deptname,#c.deptnameM from #c
union
select #d.deptnameM,#d.deptname from #d
union
select #e.deptnameM,#e.deptname from #e
union
select #f.deptnameM,#f.deptname from #f) a
--取报表
select #dept.deptname '门店', #dept.deptnameM '其他门店',#a.Price '本店会员在其他门店消费产品',
#b.Price '本店会员在其他门店消费服务',#c.Price '本店会员在其他门店消费续卡',#a.Price+#b.Price+#c.Price '支出合计',
#d.Price '其他店会员在本店消费产品',#e.Price '其他店会员在本店消费服务',#f.Price '其他店会员在本店消费服务续卡',#d.Price+#e.Price+#f.Price '总收入'
from #dept
left join #a on #dept.deptname=#a.deptname and #dept.deptnameM=#a.deptnameM
left join #b on #dept.deptname=#b.deptname and #dept.deptnameM=#b.deptnameM
left join #c on #dept.deptname=#c.deptname and #dept.deptnameM=#c.deptnameM
left join #d on #dept.deptname=#d.deptnameM and #dept.deptnameM=#d.deptname
left join #e on #dept.deptname=#e.deptnameM and #dept.deptnameM=#e.deptname
left join #f on #dept.deptname=#f.deptnameM and #dept.deptnameM=#f.deptname

drop table #a,#b,#c,#d,#e,#f,#dept
...全文
156 3 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
38363909 2016-03-23
  • 打赏
  • 举报
回复
业务系统中有相应的报表数据,你直接查询以及导出数据。
Ginnnnnnnn 2016-03-21
  • 打赏
  • 举报
回复
你做汇总的时候注意Null值,如果涉及到运算,需要用isnull(col,0) ,否则一做加减运算就Null
  • 打赏
  • 举报
回复
这是我查出来的

34,837

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧