基于 SELECT 的 MySQL 条件语法

Question

我有这张表，名为 reward :

+----+------------+-----------+----------+-------------+
| id | product_id | sell_what | get_what | valid_until |
+----+------------+-----------+----------+-------------+
| 1  | 1001       | 10        | 10000    | 15 nov 2015 |
| 2  | 1002       | 7         | 12000    | 31 dec 2015 |
| 3  | 1003       | 12        | 100000   | 31 dec 2015 |
+----+------------+-----------+----------+-------------+

而且，我有这张表，名为 user_earned :

+----+---------+------------+-----------+------------------+
| id | user_id | product_id | sell_what | transaction_date |
+----+---------+------------+-----------+------------------+
| 1  | 609     | 1003       | 1         | 2 Nov 2015       |
| 2  | 220     | 1001       | 12        | 3 Nov 2015       |
| 3  | 609     | 1003       | 1         | 20 Nov 2015      |
| 4  | 910     | 1001       | 2         | 21 Nov 2015      |
| 5  | 910     | 1001       | 3         | 21 Nov 2015      |
| 6  | 910     | 1001       | 8         | 22 Nov 2015      |
+----+---------+------------+-----------+------------------+

我也有这个查询根据 user_id 求和 sell_what 数字:

SELECT user_id, product_id, 
SUM(sell_what) AS sold, 
MAX(transaction_date) AS transaction_date
FROM user_earned 
GROUP BY user_id, product_id

结果如下:

+---------+------------+-----------+------------------+
| user_id | product_id | sold      | transaction_date |
+---------+------------+-----------+------------------+
| 220     | 1001       | 12        | 3 Nov 2015       |
| 609     | 1003       | 2         | 20 Nov 2015      |
| 910     | 1001       | 13        | 22 Nov 2015      |
+---------+------------+-----------+------------------+

现在，问题是......如何将结果与表奖励结合起来，以便我得到这样的表:

+---------+------------+-----------+-------------------+
| user_id | product_id | sold      | remaining_balance |
+---------+------------+-----------+-------------------+
| 220     | 1001       | 12        | 2                 |
+---------+------------+-----------+-------------------+

用户609不可见，因为他只卖出了2个产品，而产品1003的最低奖励是12个。

用户910是不可见的，因为他的产品1001的最新交易日期是2015年11月22日，而奖励有效期至2015年11月15日。

如何将这个条件应用到一个 SQL 语法中？谢谢。

Answer 1

我想你想要这种请求？

select * ,
    (t.sold - r.sell_what) as remaining_balance
from reward as r
inner join (
    SELECT user_id, product_id, 
    SUM(sell_what) AS sold, 
    MAX(transaction_date) AS transaction_date
    FROM earned_incentive 
    GROUP BY user_id, product_id
    ) as t
    on r.product_id = t.product_id
WHERE 
    r.sell_what <= t.sold
    and t.transaction_date <= r.valid_until

检查 [SQL Fiddle