SQL咨询，怎么解决？

Question

这是一个家庭作业，但我不能一个人做。

1)列出代号、汽车销售金额、利润不超过5000美元的转售利润总额。

resale:
cod | name    | city      | state
--------+-----------------+------------+--------
01  | Paraiso | Sao Paulo | SP
02  | Alameda | Taubate   | SP
03  | Cabana  | Macae     | RJ
04  | Santana | Betim     | MG


Automotive:
cod | manufacturer | model      | year | country   | price
--------+------------+-----------------+------+-----------+----------
01  | 01           | Gol        | 2000 | Brasil    | 25000.00
02  | 01           | Golf       | 2005 | Argentina | 39000.00
03  | 04           | Ford Ka    | 1990 | Brasil    | 15000.00
04  | 03           | Corsa Seda | 1995 | Brasil    | 12500.00
05  | 04           | Fiesta     | 2003 | Argentina | 20000.00
06  | 03           | Corsa Seda | 1995 | Argentina | 10000.00
07  | 05           | Palio      | 2002 | Brasil    | 15000.00
08  | 05           | Siena      | 2006 | Brasil    | 26000.00

sale:
customer| resale | automotive | date       | value
---------+---------+-----------+------------+----------
02      | 01     | 03         | 2010-02-05 | 17500.00
04      | 02     | 01         | 2010-01-07 | 28000.00
01      | 03     | 08         | 2010-02-15 | 28000.00
02      | 03     | 02         | 2010-03-12 | 42000.00
03      | 04     | 06         | 2010-02-06 | 11500.00
03      | 02     | 05         | 2010-01-25 | 22100.00
01      | 01     | 04         | 2010-01-21 | 15500.00
03      | 01     | 08         | 2012-02-05 | 17500.00

我的SQL：

SELECT automotive.cod, resale.name, COUNT(sale.resale) AS ammount, SUM(sale.value - automotive.price) AS total FROM sale, automotive, resale 
WHERE sale.resale = resale.cod AND automotive.cod = sale.automotive 
GROUP BY sale.resale, automotive.cod, resale.name 
HAVING SUM(sale.value - automotive.price) <= 5000;

我一个人做不到，我的答案是错的。

Ps:我使用的是PostgresSQL。

Answer 1

你差一点就成功了：

SELECT r.cod, r.name
     , count(s.resale) AS amount
     , sum(s.value - a.price) AS total
FROM   sale s
JOIN   automotive a ON a.cod = s.automotive 
JOIN   resale r     ON r.cod = s.resale
GROUP  BY r.cod, r.name 
HAVING sum(s.value - a.price) <= 5000;

我用table aliases and explicit JOIN syntax重新格式化了您的查询，使其更具可读性。

基本上，您只需要用r.cod替换a.cod。看起来像是打字错误。这使得GROUP BY子句中的s.resale是多余的，所以我删除了它。