mysql - 如何在子查询上也应用整个查询的 where 子句？

Question

我有以下 SQL 查询:

SELECT users.username, users.id, count(tahminler.tahmin)as tahmins_no, m.winnings
FROM users 
LEFT JOIN tahminler ON users.id = tahminler.user_id 
LEFT JOIN matches_of_comments ON tahminler.match_id = matches_of_comments.match_id
LEFT JOIN  (SELECT user_id ,count(result) as winnings from tahminler WHERE result = 1 group by user_id) as m ON m.user_id = users.id
WHERE (MONTH( STR_TO_DATE( matches_of_comments.match_date,  '%d.%m.%Y' ) ) =  01 AND YEAR( STR_TO_DATE( matches_of_comments.match_date,  '%d.%m.%Y' ) ) =  2014 AND flag=1)
GROUP BY users.id 
having count(tahminler.tahmin) > 0

Where 子句不适用于子查询 (m)。我不想在子查询中添加相同的子句，这会使查询变得复杂且无法优化。有没有办法在子查询上也应用这个条件而不在子查询中重复它

Answer 1

无需在查询中两次连接同一个表。您可以从以下查询中获得结果。

试试这个:

SELECT u.username, u.id, COUNT(t.tahmin) AS tahmins_no, 
       SUM(t.result = 1) AS winnings
FROM users u 
LEFT JOIN tahminler t ON u.id = t.user_id 
LEFT JOIN matches_of_comments mc ON t.match_id = mc.match_id
WHERE MONTH(STR_TO_DATE(mc.match_date, '%d.%m.%Y')) = 1 AND 
      YEAR(STR_TO_DATE(mc.match_date, '%d.%m.%Y')) = 2014 AND flag=1
GROUP BY u.id 
HAVING tahmins_no > 0