xdmy/doc/baobiao/pro.sql

SELECT psum,eqcodes,eqClassName,eqClassName typeName,
ifnull(round(psum/eqcodes,2),0.00) avgsum,pastureName,departmentName
 FROM(
SELECT
  ROUND(IFNULL(SUM( pu.`sumPrice` - pu.`quitNumber` * pu.`price`),0),2) psum,
   COUNT(DISTINCT IF(bpa.`RUCode`='',NULL,bpa.`RUCode`)) eqcodes,
   COUNT(DISTINCT pu.`bigid`) eqcodes2,
 IFNULL( (SELECT typeName FROM  eqclass WHERE id = e.`eqClassId`),'无类别')eqClassName,
	p.`name` pastureName,
	dp.`name` departmentName
	
FROM
  partuse pu
  INNER JOIN bigpartuse bpu
    ON pu.`bigId` = bpu.`id`
		left join pasture p 
		on p.id =bpu.pastureId
		left join department dp
		on dp.id = bpu.departmentId
   LEFT JOIN `bigpartapply` bpa ON bpa.`applyCode` = bpu.`oddCode`
   LEFT JOIN equipment e
   ON (e.`eqCode` = pu.`eqCode` or e.id=pu.eqId)
WHERE bpu.`useType` <> 3 AND 
date_format(bpu.receiveTime,'%Y-%m') >=concat_ws('-',?,?)
  AND (
      pu.`useTypeV` IN ('维修','保养')
  ) 
  GROUP BY e.`eqClassId`,bpu.`pastureId`,bpu.`departmentId`
	)tem
WHERE tem.eqClassName  = ?  OR ? = ''
order by tem.psum desc,typeName