访问97左连接选择

Question

我被一些access97 sql卡住了，

SELECT Master.Text, Sub.Cnt
FROM Master
INNER JOIN [
SELECT Number, COUNT(*) As Cnt FROM SubTable GROUP BY Number]. AS Sub
ON Master.Number=Sub.Number
WHERE Master.Number=1;

行为如我所料，然而，我需要方括号'SubTable'名称，我收到FROM子句中突出显示GROUP

的错误

SELECT Master.Text, Sub.Cnt
FROM Master
INNER JOIN [
SELECT Number, COUNT(*) As Cnt FROM [Sub Table] GROUP BY Number]. AS Sub
ON Master.Number=Sub.Number
WHERE Master.Number=1;

谁能给我指个正确的方向?

谢谢

Answer 1

查询中的方括号看起来很奇怪。尝试在子查询中使用正则括号:

SELECT Master.Text, Sub.Cnt
FROM Master INNER JOIN 
     (SELECT Number, COUNT(*) As Cnt
      FROM [Sub Table]
      GROUP BY Number
     ) AS Sub
     ON Master.Number = Sub.Number
WHERE Master.Number = 1;

编辑:

您可以将此查询重写为:

SELECT Master.Text, COUNT(*) as Cnt
FROM Master INNER JOIN 
     [Sub Table] as sub
     ON Master.Number = Sub.Number
WHERE Master.Number = 1
GROUP BY Number;