mysql - 使用连接时如何区分 sum()

Question

我有两张 table 。一个保存合格零件的数据，另一个保存不合格零件的数据。

我喜欢接收以下格式的数据:

| YYYY-MM | SUM(ok.qty) | SUM(reject.qty) | reject % |

1) 是否可以使用JOIN，然后使用某种DISTINCT sum(ok.qty)？

SELECT 
    DATE_FORMAT(date, '%Y-%m'), 
    SUM(ok.qty), 
    SUM(reject.qty), 
    SUM(reject.qty) / SUM(reject.qty + ok.qty) as reject_percent
FROM ok
JOIN reject ON ok.id = reject.ok_id
GROUP BY DATE_FORMAT(date, '%Y-%m')

2)如果不是如何消除重复的子查询？

SELECT 
    DATE_FORMAT(date, '%Y-%m'), 
    SUM(ok.qty), 
    (SELECT qty FROM reject WHERE ok_id = ok.id), 
    (SELECT qty FROM reject WHERE ok_id = ok.id) / 
        ( (SELECT qty FROM reject WHERE ok_id = ok.id) + SUM(ok.qty) ) 
        as reject_percent
FROM ok
GROUP BY DATE_FORMAT(date, '%Y-%m')

现在必须将一个查询写入 3 次。

数据库设计:

ok.id 可以有多个拒绝。

Answer 1

你可以这样尝试..

SELECT 
    DATE_FORMAT(date, '%Y-%m'), 
    SUM(ok.qty), 
    SUM(reject.qty), 
    ((SUM(reject.qty) / SUM(reject.qty + ok.qty))*100)as reject_percent
FROM ok
inner JOIN (Select Ok_Id,Sum(qty) as qty from Reject group by OK_id) as reject ON ok.id = reject.ok_id
GROUP BY DATE_FORMAT(date, '%Y-%m')