加入查询问题 - 加入附近的语法错误
问题描述:
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上尝试这个加入查询问题 - 加入附近的语法错误
我想在每个位置只能得到最高收入的交易者,这是基于薪酬和奖励排名作为总额的排名。我想打印 交易员姓名,工资,奖励和总工资(工资加奖励)。
请你能指教我的查询有什么问题吗?我得到一个错误,指出语法错误附近加入
答
这是您的查询,排序的格式:
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
答
你不能真正做到:
select .... from ....
join ... on ....
group by ....
join ....
如果你想连接两个聚合子查询,使用公用表表达式(with
条款)。更好的是尝试做你的设定操作,然后聚集在最后。
我不是在PostgreSQL的专家,但我会说你从第错过 – Jens