mysql - 基于SELECT的MySQL条件句法

我有这张桌子，名字叫奖励：

+----+------------+-----------+----------+-------------+
| 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      |
+----+---------+------------+-----------+------------------+

我也有这个查询来总结基于sell_what的user_id数字：

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语法？谢谢。

最佳答案

我想您想要这种要求吗？

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

关于mysql - 基于SELECT的MySQL条件句法，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/33846719/