联接查询问题-联接附近的语法错误

Question

   TRADERS                                            INCENTIVE
   __________________________________________      _____________
   TRDID  NAME  SUPERVISOR  LOCATION   PAY         TRDID  INCENTIVE
   ------------------------------------------      -------------
  66  Chad                 NY         110000        17    5000
  17  Yena          66     TN          75000        21    2000
   5  Karam         66     TN          80000         66    5000
  21  Rose           5     HI         100000      ...


 --group by highest pay for location and traderid                                               

                   select e.trdid trdid, e.location, max (e.pay+ coalesce(b.incentive, 0))  maxtotal  from traders e
                        join incentive b on e.trdid = b.trdid
                            group by e.location, trdid
                   join (                       
                   (select e.trdid trdid, max (e.pay+ coalesce(b.incentive, 0))  maxtotal  from traders e
                join incentive b on e.trdid = b.trdid
               group by e.location, e.trdid)) using (trdid)

当我试图加入这个表和它的子查询时，我会得到一个错误。我在PostgreSQL上试着做这个

我试图只得到最高收入的交易员在每个地点，根据排名的薪酬和奖励加起来，作为总薪酬。我想打印商人的名字，薪酬，奖励，和总薪酬(薪酬加奖励)。

请你告诉我的问题出了什么问题吗？我收到一个错误，说明联接附近的语法错误。

Answer 1

这是您的查询，格式大致如下：

select e.trdid trdid, e.location, max (e.pay+ coalesce(b.incentive, 0))  maxtotal
from traders e join incentive
     b
     on e.trdid = b.trdid
group by e.location, trdid join
     ((select e.trdid trdid, max (e.pay+ coalesce(b.incentive, 0)) as maxtotal
       from traders e join
            incentive b
            on e.trdid = b.trdid
       group by e.location, e.trdid
     ))
     using (trdid)

您似乎误解了SQL语法。JOIN是只在FROM子句中理解的运算符。GROUP BY是一个单独的子句。它位于FROM子句之后。我想你打算：

select e.trdid trdid, e.location, max(e.pay + coalesce(b.incentive, 0))  maxtotal
from traders e join
     incentive b
     on e.trdid = b.trdid join
     (select e.trdid trdid, max(e.pay+ coalesce(b.incentive, 0)) as maxtotal
      from traders e join
           incentive b
           on e.trdid = b.trdid
      group by e.location, e.trdid
     ) b
     using (trdid)
group by e.location, trdid;

您可能会注意到，我设置了查询的格式，以便SQL子句在左边对齐。这就是为什么FROM和GROUP BY在左边，但JOIN不是。

但是，我认为编写查询的一个更简单的方法是：

select distinct on (e.trdid) e.trdid as trdid, e.location,
       max(e.pay + coalesce(b.incentive, 0)) as maxtotal
from traders e join
     incentive b
     on e.trdid = b.trdid
group by e.trdid, e.location
order by e.trdid, maxtotal desc

Answer 2

你不能这样做：

select .... from ....
 join ... on ....
group by ....
 join ....

如果要连接两个聚合子查询，请使用公共表表达式(with子句)。更好的做法是尝试进行设置操作，然后在最后进行聚合。