mysql - 头脑 NumPy 的 SQL 疯狂

Question

此查询在 invoices 上运行帮助我决定我需要付钱给谁的表格

这是基本表:

用户表

+---------+--------+
| user_id |  name  |
+---------+--------+
|       1 | Peter  |
|       2 | Lois   |
|       3 | Stewie |
+---------+--------+

发票表:

+------------+---------+----------+--------+---------------+---------+
| invoice_id | user_id | currency | amount |  description  | is_paid |
+------------+---------+----------+--------+---------------+---------+
|          1 |       1 | usd      |    140 | Cow hoof      |       0 |
|          2 |       1 | usd      |     45 | Cow tail      |       0 |
|          3 |       1 | gbp      |      1 | Cow nostril   |       0 |
|          4 |       2 | gbp      |   1500 | Cow nose hair |       0 |
|          5 |       2 | cad      |      1 | eyelash       |       1 |
+------------+---------+----------+--------+---------------+---------+

我想要一个如下所示的结果表:

+---------+-------+----------+-------------+
| user_id | name  | currency | SUM(amount) |
+---------+-------+----------+-------------+
|       1 | Peter | usd      |         185 |
|       2 | Lois  | gbp      |        1500 |
+---------+-------+----------+-------------+

条件是:

• 只考虑尚未支付的发票，所以 is_paid = 0
• 按 user_id 将它们分组, 通过 currency
• 如果SUM(amount) < $100对于 user_id，货币对则不必费心显示结果，因为我们不支付低于 100 美元(或等值，基于固定汇率)的发票。

这是我到目前为止所得到的(没有工作——我猜这是因为我正在按 GROUP 参数过滤):

SELECT 
  users.user_id, users.name,
  invoices.currency, SUM(invoices.amount)
FROM 
  mydb.users, 
  mydb.invoices 
WHERE 
  users.user_id = invoices.user_id AND 
  invoices.is_paid != true AND
  SUM(invoices.amount) >=
    CASE 
      WHEN invoices.currency = 'usd' THEN 100
      WHEN invoices.currency = 'gbp' THEN 155
      WHEN invoices.currency = 'cad' THEN 117
    END
GROUP BY 
  invoices.currency, users.user_id
ORDER BY 
  users.name, invoices.currency;

帮忙吗？

Answer 1

您不能在WHERE 中使用SUM。请改用 HAVING。