mysql - 根据条件再选择一张表，添加到条件中

Question

SELECT a.id,
       a.username,
       a.email,
       a.created,
       b.user_id,
       SUM(b.price) + 
       SUM(IF(d.beginner =0, 15, 0)) + CASE WHEN SUM(b.price) >499 THEN 100
                                            ELSE 0 END AS points,
       SUM(b.price) AS pris,
       b.created,
       c.user_id,
       c.referrer_id,
       c.created,
       d.id,
       d.beginner,
       d.winner_id
FROM users a,
     accounts b,
     referrals c,
     product d
WHERE a.created BETWEEN '2013-04-01' AND '2013-06-30'
  AND d.created BETWEEN '2013-06-01' AND '2013-06-30'
  AND b.created BETWEEN '2013-06-01' AND '2013-06-30'
  AND a.id = b.user_id
  AND a.id = c.user_id
  AND d.winner_id = a.id
GROUP BY c.referrer_id
ORDER BY `points` DESC LIMIT 0 , 3

在此查询中，我有四个表。对于product 表，我还希望根据created 选择它。但是，当我确实喜欢在查询中所做的事情时，它没有给我正确的结果。我也想用这个查询选择产品表，其中条件是我只需要选择从 6 月 1 日到 6 月 30 日的记录，并根据条件放置它们，如果在产品表中找到获胜者，它将添加 15 分。

如果你看到这个 SUM( IF( d.beginner =0, 15, 0 ) ) 条件，如果我不添加 AND d.created ，它就会起作用
'2013-06-01'之间
AND '2013-06-30' 并且如果在产品表中未找到用户，但在两个条件的其余部分中具有诸如 30 点之类的点，则此查询不会显示该用户。我不知道为什么？我希望在 6 月 1 日至 6 月 30 日期间选择产品和帐户表，并在 4 月 1 日至 6 月 30 日期间选择表用户

    SELECT a.id, a.username, a.email, a.created, b.user_id, SUM( b.price ) + 
CASE WHEN SUM( b.price ) >499
THEN 100 
ELSE 0 
END AS gr, SUM( b.price ) AS pris, b.created, c.user_id, c.referrer_id, c.created
FROM users a, accounts b, referrals c
WHERE a.created
BETWEEN  '2013-04-01'
AND  '2013-06-30'
AND b.created
BETWEEN  '2013-06-01'
AND  '2013-06-30'
AND a.id = b.user_id
AND a.id = c.user_id
GROUP BY c.referrer_id
ORDER BY  `gr` DESC 
LIMIT 0 , 3

我现在已经从中删除了产品表条件。所以现在很容易理解了。我想添加表products，其中仅选择从6月1日到6月30日的行，第二个条件是如果用户在winner_id列中的产品表中找到则添加15分。

Answer 1

您的评论表明在该范围内的产品表中没有找到记录。如果您仍然希望在这种情况下返回一行，请使用 LEFT OUTER JOIN。类似如下:-

SELECT a.id,
       a.username,
       a.email,
       a.created,
       b.user_id,
       SUM(b.price) + 
       SUM(CASE WHEN d.beginner IS NULL THEN 0 WHEN d.beginner = 0 THEN 15 ELSE 0 END) + CASE WHEN SUM(b.price) >499 THEN 100
                                            ELSE 0 END AS points,
       SUM(b.price) AS pris,
       b.created,
       c.user_id,
       c.referrer_id,
       c.created,
       d.id,
       d.beginner,
       d.winner_id
FROM users a
INNER JOIN accounts b ON a.id = b.user_id
INNER JOIN referrals c ON a.id = c.user_id
LEFT OUTER JOIN product d ON d.winner_id = a.id AND d.created BETWEEN '2013-06-01' AND '2013-06-30'
WHERE a.created BETWEEN '2013-04-01' AND '2013-06-30'
  AND b.created BETWEEN '2013-06-01' AND '2013-06-30'
GROUP BY c.referrer_id
ORDER BY `points` DESC 
LIMIT 0 , 3