| 有没有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) |
最佳回答 |
||||
|
0
|
|
|||
|
0
|
|
|||
|
0
|
|
|||
|
0
|
|
|||
|
0
|
|
|||
