select
v1.module Module,
v1.supplier Supplier,
v1.pline PLine,
v1.station Station,
v1.fail beanValNeed,
v2.total beanValTotal
from
(
SELECT
module,
supplier,
pline,
station ,
count(result) fail
FROM `sqm`.`sqm_station_performance_view`
where result='1'
group by supplier,module,pline,station
)
v1
left join
(
SELECT
supplier,
module,
pline,
station ,
count(result) total
FROM `sqm`.`sqm_station_performance_view`
group by supplier,module,pline,station
)
v2
on
v1.supplier = v2.supplier
and
v1.module = v2.module
and
v1.pline = v2.pline
and
v1.station = v2.station
***************************************************
select
v1.module Module,
v1.supplier Supplier,
v1.pline PLine,
v1.station Station,
v1.fail beanValNeed,
v2.total beanValTotal
from
(
SELECT
module,
supplier,
pline,
station ,
count(result) fail
FROM `sqm`.`sqm_station_performance_view`
where result='1'
<iftest="module != null">
and module=#{module}
</if><iftest="supplier != null">
and supplier=#{supplier}
</if>
group by supplier,module,pline,station
)
v1
left join
(
SELECT
supplier,
module,
pline,
station ,
count(result) total
FROM `sqm`.`sqm_station_performance_view`
group by supplier,module,pline,station
)
v2
on
v1.supplier = v2.supplier
and
v1.module = v2.module
and
v1.pline = v2.pline
and
v1.station = v2.station
******************************************************************************
SELECT count(guid) ,station FROM `sqm`.`sqm_repair_record` group by station order by count(guid) DESC Limit 2