使用外部表子查询的SQL子查询查询
问题描述:
我想要检索一个队友根据DRIVERPOSITION
打败他的队友的次数,但是我总是收到invalid select-list in subselect
我想这是因为我使用b
和a
表中的子查询?使用外部表子查询的SQL子查询查询
样本数据
RACEID CONSTRUCTORID DRIVERID DRIVERPOSITION
970 4 826 3
970 4 807 7
960 4 826 4
960 4 807 7
970 3 820 10
970 3 810 12
960 3 820 13
960 3 810 11
期望的结果
RACEID CONSTRUCTORID DRIVERID WINS
970 4 826 2
970 4 807 0
960 3 820 1
960 3 810 1
我试过到目前为止
SELECT
(
SELECT COUNT(
CASE
WHEN b.DRIVERPOSITION > a.DRIVERPOSITION THEN 1
ELSE 0 END
)
FROM QUALIFYING b
WHERE RACEYEAR = to_char(NOW(), 'YYYY')
AND a.CONSTRUCTORID = b.CONSTRUCTORID
AND a.RACEID = b.RACEID
AND a.DRIVERID != b.DRIVERID
)
FROM QUALIFYING a
INNER JOIN RACES
ON a.RACEID = RACES.RACEID
INNER JOIN DRIVERS
ON a.DRIVERID = DRIVERS.DRIVERID
INNER JOIN CONSTRUCTORS
ON a.CONSTRUCTORID = CONSTRUCTORS.CONSTRUCTORID
WHERE RACEYEAR = to_char(NOW(), 'YYYY');
答
我觉得这做你想要的:
select raceid, constructorid, driverid,
sum(case when seqnum = 1 then 1 else 0 end) as numwins
from (select d.*,
row_number() over (partition by raceid, constructorid order by driverposition) as seqnum
from data d
) d
group by raceid, constructorid;
但是,我不知道如何适合您的查询。您的示例数据涉及一个表格。你的查询有多个表引用。
编辑你的问题并提供(1)样本数据; (2)期望的结果; (3)您正在使用的数据库的标签。你有足够高的声誉,你应该知道如何提出一个好问题。 –
更新了我的问题。 –