更新时间：2022-04-21 21:05:54
来源：网络转载

与MS Access数据库中的加入有关的问题

我正在MS Access数据库中使用以下查询:

I am using the following query in an MS Access database:

SELECT SD.RollNo, SD.Name , ED.ExamName, (
    SELECT count(*) 
    FROM (
        SELECT DISTINCT innerED.StudentId 
        FROM ExamDetails innerED 
        WHERE innerED.StudentId=SD.StudentId 
    )
) AS StudentId
FROM StudentDetails SD 
LEFT OUTER JOIN ExamDetails ED 
    ON SD.StudentId= ED.StudentId

无论何时执行此查询，都会弹出一个对话框，询问参数SD.StudentId的值.为什么会要求这样做，以及如何阻止它这样做?

Whenever I execute this query, a dialog box comes up and asks for the value of the parameter SD.StudentId. Why is it asking for this, and how do I stop it from doing so?


 1 条回答

网友1解答：

  • MS Access无法理解Count(*)聚合上的SELECT语句.访问SQL语句如下所示.

    MS Access does not understand the SELECT statement on the Count (*) Aggregate. To Access the SQL Statement looks like this.

     SELECT DISTINCT innerED.StudentId
 FROM ExamDetails innerED 
 WHERE innerED.StudentId=SD.StudentId

    因为别名AS STUDENTID在语句末尾出现，所以此Select语句无法识别它，因此它不知道什么是.StudendID，因此它假定它是一个参数.

    Because the alias AS STUDENTID comes after the end of the statement, this Select statement doesn't recognize it, so it has no idea what .StudendID is so it assumes it's a parameter.

    MS Access遇到查询本身未标识的参数时，将提示用户输入值.

    MS Access, when faced with a parameter that has not been identified in the query itself will prompt the user for a value.

    重写查询，以便此Select语句可以标识所有表源.

    Rewrite the query so that this Select statement can identify all the table sources.

sql
database
ms-access