不懂求科普，我在做报表，我同事老让我用linq to sql 来写，能完成复杂的报表么。

[好奇都是要学的]

我现在的表报 基本都是好多表关了 在加上临时表组合成表报呈现在页面上，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

[吉普赛的歌]

linq to sql 做报表就是SB

[好奇都是要学的]

就没有个科普下知识的么？球指导，球教会啊

[好奇都是要学的]

用linq 能方便 简单多少那！

[唐诗三百首]

复杂报表建议用存储过程完成, 用linq to sql不合适.

[好奇都是要学的]

引用 2 楼 ap0405140 的回复:

复杂报表建议用存储过程完成, 用linq to sql不合适.

我开始想用存储过程了但是没用，因为我20多个报表传的参数都个数不同，我得写对应的db.AddInParameter(cmd, "chrNo", DbType.String, strNo); 感觉好麻烦，我就直接用的SQL语句。 但是写完表报后发现自己笨了，我可以用一个XML把参数传到SQL，在用SQL的变量接受这些参数 就可以用存储过程了，但是我都写完报表了，懒得改了

[程爱动漫]

[专注or全面]

引用 5 楼 yenange 的回复:

linq to sql 做报表就是SB

而且是SB中的极品SB

[好奇都是要学的]

引用 7 楼 x_wy46 的回复:

[quote=引用 5 楼 yenange 的回复:] linq to sql 做报表就是SB

而且是SB中的极品SB[/quote] LINQ 在上面时候查询好用啊。