我现在的表报 基本都是好多表关了 在加上临时表组合成表报呈现在页面上，linq能很好的完成么比如下面的。我放的都是简单的表，还有跨表更多的。更麻烦的。
日营业统计报表
select CONVERT(varchar(100), a.DocDate, 23) Date,b.deptname , sum(a.Totalamt) SumPrice into #a from CusOrderM a
left join basdept b on a.deptno=b.deptno
where (a.paytype='1' or a.paytype='3') and iffree=0 and a.deptno='010101' and a.DocDate>='2016-04-16' and a.DocDate<'2016/4/17 0:00:00'
group by CONVERT(varchar(100), a.DocDate, 23),b.deptname
select CONVERT(varchar(100), a.PlaceTime, 23) Date,b.deptname , sum(a.Price-isnull(c.TotalAmt,0)) SumPrice into #b from dbo.OrderReservation a
left join basdept b on a.deptno=b.deptno
left join CusOrderM c on a.OrderNo=c.DocNo
where (a.paytype='2' or a.paytype='4') and a.iffree=0 and a.deptno='010101' and a.PlaceTime>='2016-04-16' and a.PlaceTime<'2016/4/17 0:00:00'
group by CONVERT(varchar(100), a.PlaceTime, 23),b.deptname
select CONVERT(varchar(100), a.DocDate, 23) Date,b.deptname , sum(a.Totalamt) SumPrice into #c from CusOrderM a
left join basdept b on a.deptno=b.deptno
where a.paytype='0' and iffree=0 and a.deptno='010101' and a.DocDate>='2016-04-16' and a.DocDate<'2016/4/17 0:00:00'
group by CONVERT(varchar(100), a.DocDate, 23),b.deptname
select CONVERT(varchar(100), a.PlaceTime, 23) Date,b.deptname , sum(a.Price-isnull(c.TotalAmt,0)) SumPrice into #d from dbo.OrderReservation a
left join basdept b on a.deptno=b.deptno
left join CusOrderM c on a.OrderNo=c.DocNo
where a.paytype='0' and a.iffree=0 and a.deptno='010101' and a.PlaceTime>='2016-04-16' and a.PlaceTime<'2016/4/17 0:00:00'
group by CONVERT(varchar(100), a.PlaceTime, 23),b.deptname
select CONVERT(varchar(100), a.Rechtime, 23) Date,b.deptname , sum(a.RechAmount) SumPrice into #e from RechargeRecord a
left join basdept b on a.deptno=b.deptno
where a.viptype='0' and a.deptno='010101' and a.Rechtime>='2016-04-16' and a.Rechtime<'2016/4/17 0:00:00'
group by CONVERT(varchar(100), a.Rechtime, 23),b.deptname
select CONVERT(varchar(100), a.PlaceTime, 23) Date,b.deptname,sum(Price) SumPrice into #f from OrderReservation a
left join basdept b on a.deptno=b.deptno
where a.iffree='1' and a.deptno='010101' and a.PlaceTime>='2016-04-16' and a.PlaceTime<'2016/4/17 0:00:00'
group by CONVERT(varchar(100), a.PlaceTime, 23),b.deptname
select CONVERT(varchar(100), a.DocDate, 23) Date,b.deptname , sum(a.Credit) SumPrice into #g from CusOrderM a
left join basdept b on a.deptno=b.deptno
where a.paytype='2' and iffree=0 and a.deptno='010101' and a.DocDate>='2016-04-16' and a.DocDate<'2016/4/17 0:00:00'
group by CONVERT(varchar(100), a.DocDate, 23),b.deptname
select CONVERT(varchar(100), a.Rechtime, 23) Date,b.deptname , sum(a.RechAmount) SumPrice into #h from RechargeRecord a
left join basdept b on a.deptno=b.deptno
where a.viptype='1' and a.deptno='010101' and a.Rechtime>='2016-04-16' and a.Rechtime<'2016/4/17 0:00:00'
group by CONVERT(varchar(100), a.Rechtime, 23),b.deptname
select * into #date from (
select Date,deptname from #a
union
select Date,deptname from #b
union
select Date,deptname from #c
union
select Date,deptname from #d
union
select Date,deptname from #e
union
select Date,deptname from #f
union
select Date,deptname from #g
union
select Date,deptname from #h) date select a.Date '日期',a.deptname '门店',isnull(#a.SumPrice,0) '产品销售现金',isnull(#b.SumPrice,0)+isnull(#h.SumPrice,0) '服务销售现金',isnull(#c.SumPrice,0) '卡耗产品',isnull(#d.SumPrice,0) '卡耗服务',
isnull(#a.SumPrice,0)+isnull(#b.SumPrice,0)+isnull(#h.SumPrice,0)+isnull(#c.SumPrice,0)+isnull(#d.SumPrice,0) '营业合计',
#e.SumPrice '会员卡储值',
isnull(#a.SumPrice,0)+isnull(#b.SumPrice,0)+isnull(#e.SumPrice,0)+isnull(#h.SumPrice,0) '现金销售合计',
isnull(#c.SumPrice,0)+isnull(#d.SumPrice,0) '会员卡消耗合计',
#f.SumPrice '免单金额',#g.SumPrice '积分消费' from #date a
left join #a on a.date=#a.date
left join #b on a.date=#b.date
left join #c on a.date=#c.date
left join #d on a.date=#d.date
left join #e on a.date=#e.date
left join #f on a.date=#f.date
left join #g on a.date=#g.date
left join #h on a.date=#h.date
drop table #a
drop table #b
drop table #c
drop table #d
drop table #e
drop table #f
drop table #g
drop table #h
drop table #date
销售对账单
#NAME?
select * into #a from (
select StaffName,DocDate,deptname,sum(Price) Price from (
select a.CashierName StaffName,CONVERT(varchar(100),a.PlaceTime,23) DocDate, b.deptname ,sum(Price) Price from OrderReservation a
left join basdept b on a.deptno=b.deptno
where a.PayType='2' and a.PlaceTime>'2016-1-1' and a.PlaceTime<='2016-10-1' and iffree=0 and a.deptno='010101'
group by a.CashierName,CONVERT(varchar(100),a.PlaceTime,23),b.deptname
union all
select isnull(c.StaffName,'') StaffName, CONVERT(varchar(100),a.DocDate,23) DocDate,b.deptname ,sum(TotalAmt) Price from dbo.CusOrderM a
left join basdept b on a.deptno=b.deptno
left join basstaff c on a.staffno=c.staffno
where a.PayType='3' and a.DocDate>'2016-1-1' and a.DocDate<='2016-10-1' and iffree=0 and a.deptno='010101'
and a.docno not in(select orderno from OrderReservation where a.docno=orderno)
group by c.StaffName,CONVERT(varchar(100),a.DocDate,23),b.deptname
union all
select isnull(c.StaffName,'') StaffName, CONVERT(varchar(100),a.Rechtime,23) DocDate,b.deptname ,sum(RechAmount) Price from dbo.RechargeRecord a
left join basdept b on a.deptno=b.deptno
left join basstaff c on a.staffno=c.staffno
where a.Rechtime>'2016-1-1' and a.Rechtime<='2016-10-1' and a.deptno='010101'
group by c.StaffName,CONVERT(varchar(100),a.Rechtime,23),b.deptname) a
group by StaffName,CONVERT(varchar(100),DocDate,23),deptname) a
#NAME?
select * into #b from (
select StaffName,DocDate,deptname,sum(Price) Price from (
select isnull(a.CashierName,'') StaffName,CONVERT(varchar(100),a.PlaceTime,23) DocDate, b.deptname ,sum(Price) Price from OrderReservation a
left join basdept b on a.deptno=b.deptno
where a.PayType='4' and a.PlaceTime>'2016-1-1' and a.PlaceTime<='2016-10-1' and iffree=0 and a.deptno='010101'
group by a.CashierName,CONVERT(varchar(100),a.PlaceTime,23),b.deptname
union all
select c.StaffName StaffName, CONVERT(varchar(100),a.DocDate,23) DocDate,b.deptname ,sum(TotalAmt) Price from dbo.CusOrderM a
left join basdept b on a.deptno=b.deptno
left join basstaff c on a.staffno=c.staffno
where a.PayType='1' and a.DocDate>'2016-1-1' and a.DocDate<='2016-10-1' and iffree=0 and a.deptno='010101'
and a.docno not in(select orderno from OrderReservation where a.docno=orderno)
group by c.StaffName,CONVERT(varchar(100),a.DocDate,23),b.deptname
union all
select isnull(c.StaffName,'') StaffName, CONVERT(varchar(100),a.Rechtime,23) DocDate,b.deptname ,sum(RechAmount) Price from dbo.RechargeRecord a
left join basdept b on a.deptno=b.deptno
left join basstaff c on a.staffno=c.staffno
where a.Rechtime>'2016-4-6' and a.Rechtime<='2016-4-7' and a.deptno='010101' and a.PayType='网银'
group by c.StaffName,CONVERT(varchar(100),a.Rechtime,23),b.deptname)a
group by StaffName,CONVERT(varchar(100),DocDate,23),deptname) a
#NAME?
select * into #c from (
select a.CashierName StaffName,CONVERT(varchar(100),a.PlaceTime,23)DocDate, b.deptname ,sum(Price) Price from OrderReservation a
left join basdept b on a.deptno=b.deptno
where a.PlaceTime>'2016-1-1' and a.PlaceTime<='2016-10-1' and iffree=1 and a.deptno='010101'
group by a.CashierName,CONVERT(varchar(100),a.PlaceTime,23),b.deptname) a
#NAME?
select * into #d from (
select c.StaffName, CONVERT(varchar(100),a.DocDate,23) DocDate,b.deptname ,sum(TotalAmt) price from dbo.CusOrderM a
left join basdept b on a.deptno=b.deptno
left join basstaff c on a.staffno=c.staffno
where a.PayType='2' and a.DocDate>'2016-1-1' and a.DocDate<='2016-10-1' and a.deptno='010101'
group by c.StaffName,CONVERT(varchar(100),a.DocDate,23),b.deptname) a
#NAME?
select * into #e from (
select StaffName,DocDate,deptname,sum(Price) Price from (
select a.CashierName StaffName,CONVERT(varchar(100),a.PlaceTime,23) DocDate, b.deptname ,sum(Price) Price from OrderReservation a
left join basdept b on a.deptno=b.deptno
where a.PayType='0' and a.PlaceTime>'2016-1-1' and a.PlaceTime<='2016-10-1' and iffree=0 and a.deptno='010101'
group by a.CashierName,CONVERT(varchar(100),a.PlaceTime,23),b.deptname
union all
select isnull(c.StaffName,'') StaffName, CONVERT(varchar(100),a.DocDate,23) DocDate,b.deptname ,sum(TotalAmt) Price from dbo.CusOrderM a
left join basdept b on a.deptno=b.deptno
left join basstaff c on a.staffno=c.staffno
where a.PayType='0' and a.DocDate>'2016-1-1' and a.DocDate<='2016-10-1' and iffree=0 and a.deptno='010101'
and a.docno not in(select orderno from OrderReservation where a.docno=orderno)
group by c.StaffName,CONVERT(varchar(100),a.DocDate,23),b.deptname) a
group by StaffName,CONVERT(varchar(100),DocDate,23),deptname) a
select * into #dept from (
select StaffName,DocDate,deptname from #a
union
select StaffName,DocDate,deptname from #b
union
select StaffName,DocDate,deptname from #c
union
select StaffName,DocDate,deptname from #d
union
select StaffName,DocDate,deptname from #e) a
select #dept.StaffName '收款员',#dept.DocDate '日期',#dept.deptname '收款门店' ,sum(#a.price) '现金',sum(#b.price) '银联卡',
sum(isnull(#a.price,0))+sum(isnull(#b.price,0)) '合计',sum(#e.Price) '会员卡消耗',sum(#c.Price) '免单',sum(#d.Price) '积分消费'
from #dept
left join #a on #dept.StaffName=#a.StaffName and #dept.DocDate=#a.DocDate and #dept.deptname=#a.deptname
left join #b on #dept.StaffName=#b.StaffName and #dept.DocDate=#b.DocDate and #dept.deptname=#b.deptname
left join #e on #dept.StaffName=#e.StaffName and #dept.DocDate=#e.DocDate and #dept.deptname=#e.deptname
left join #c on #dept.StaffName=#c.StaffName and #dept.DocDate=#c.DocDate and #dept.deptname=#c.deptname
left join #d on #dept.StaffName=#d.StaffName and #dept.DocDate=#d.DocDate and #dept.deptname=#d.deptname
group by #dept.StaffName,#dept.DocDate,#dept.deptname
drop table #a,#b,#c,#d, #e,#dept
