我有一个使用Access 2007的项目,但正在努力编写简单的子查询。我正在尝试从记录一定比率后的下6个事件的表中获得结果。
我希望以下代码在访问中有效:
SELECT [runner name], [race ID], [race date], [placing], [class]
FROM runners
WHERE [runner name] IN
(SELECT [runner name] from 6F Full Form WHERE [ratio] >9
ORDER BY [runner name], [race ID], [date], [placing], [class];
我在下面收到错误消息。NB 6F Full Form
是我写的查询,我正在尝试在子查询中使用它。
查询表达式中的语法错误'[runner name] in(从6f完整表单中选择[runner name],其中[比率]> 9
您当前的SQL代码的问题是,必须用方括号包含包含空格或保留单词的字段/表/查询名称 - 因此,
from 6F Full Form WHERE
应该写:
from [6F Full Form] WHERE
您在order by
子句中也有潜在的错字:
SELECT ..., [race date], ...
...
ORDER BY ..., [date], ...;
^--------------- Should this be [race date]?
值得补充的是,您也可以使用加入,例如:
来实现此目标。select
r.[runner name],
r.[race id],
r.[race date],
r.[placing],
r.[class]
from
runners r inner join
(select distinct f.[runner name] from [6f full form] f where f.[ratio] > 9) q
on r.[runner name] = q.[runner name]
order by
r.[runner name],
r.[race id],
r.[race date],
r.[placing],
r.[class]
尝试以下:
(SELECT [runner name]
FROM [6F Full Form]
WHERE [ratio] >9
ORDER BY [runner name], [race ID], [date], [placing], [class]);