有没有sql的大神？这还能优化吗？
--1.
select CONVERT(varchar(12),OrderAcceptedDateTime,23) as '受理日期',
sum(case DrawingType when 1 then 1 else 0 end) as '图纸CAD',
sum(case DrawingType when 0 then 1 else 0 end) as '图纸2020',
sum(case DrawingType when 2 then 1 else 0 end) as '其他',
COUNT(OrderNo) as '总量',
'已受理订单' as '分类'
from dbo.C_Order
where OrderAcceptedDateTime is not null --受理时间不为空
and CONVERT(varchar(12),OrderAcceptedDateTime,23)='${datetime}'
${if(len(ordertype)=0,""," and OrderType in ("+ordertype+")")}
group by CONVERT(varchar(12),OrderAcceptedDateTime,23)
-------------------
--2.
union all
select CONVERT(varchar(12),DrawingApprovedDateTime,23) as '审图日期',
sum(case DrawingType when 1 then 1 else 0 end) as '图纸CAD',
sum(case DrawingType when 0 then 1 else 0 end) as '图纸2020',
sum(case DrawingType when 2 then 1 else 0 end) as '其他',
COUNT(OrderNo) as '总量',
'已审图订单' as '分类'
from dbo.C_Order
where DrawingApprovedDateTime is not null --审图时间不为空
and CONVERT(varchar(12),DrawingApprovedDateTime,23)='${datetime}'
${if(len(ordertype)=0,""," and OrderType in ("+ordertype+")")}
group by CONVERT(varchar(12),DrawingApprovedDateTime,23)
-------------------
--3.
union all
select CONVERT(varchar(12),BOMApprovedDateTime,23) as '拆单日期',
sum(case DrawingType when 1 then 1 else 0 end) as '图纸CAD',
sum(case DrawingType when 0 then 1 else 0 end) as '图纸2020',
sum(case DrawingType when 2 then 1 else 0 end) as '其他',
COUNT(Id) as '总量',
'已拆单订单' as '分类'
from dbo.C_Order
where BOMApprovedDateTime is not null --拆单时间不为空
and CONVERT(varchar(12),BOMApprovedDateTime,23)='${datetime}'
${if(len(ordertype)=0,""," and OrderType in ("+ordertype+")")}
group by CONVERT(varchar(12),BOMApprovedDateTime,23)
---------------------
--4.
union all
select CONVERT(varchar(12),MOrderApprovedDateTime,23) as '料单审核日期',
sum(case DrawingType when 1 then 1 else 0 end) as '图纸CAD',
sum(case DrawingType when 0 then 1 else 0 end) as '图纸2020',
sum(case DrawingType when 2 then 1 else 0 end) as '其他',
COUNT(OrderNo) as '总量',
'已料单审核' as '分类'
from dbo.C_Order
where MOrderApprovedDateTime is not null --料单审核时间不为空
and CONVERT(varchar(12),MOrderApprovedDateTime,23)='${datetime}'
${if(len(ordertype)=0,""," and OrderType in ("+ordertype+")")}
group by CONVERT(varchar(12),MOrderApprovedDateTime,23)
-------------------------
--5.
union all
select CONVERT(varchar(12),HoleApprovedDateTime,23) as '孔位审核日期',
sum(case DrawingType when 1 then 1 else 0 end) as '图纸CAD',
sum(case DrawingType when 0 then 1 else 0 end) as '图纸2020',
sum(case DrawingType when 2 then 1 else 0 end) as '其他',
COUNT(OrderNo) as '总量',
'已孔位审核' as '分类'
from dbo.C_Order
where HoleApprovedDateTime is not null --孔位审核时间不为空
and CONVERT(varchar(12),HoleApprovedDateTime,23)='${datetime}'
${if(len(ordertype)=0,""," and OrderType in ("+ordertype+")")}
group by CONVERT(varchar(12),HoleApprovedDateTime,23)
