如何将此访问查询转换为mySQL查询? [英] How do I convert this Access Query to mySQL query?

问题描述

给数据库中的初学者一个复杂的查询，我该如何将访问sql转换成mysql? 这是MS ACCESS查询:

Given a complex query for a beginner in database, how can I convert this access sql to mysql? Here is the MS ACCESS query:

SELECT tblSections.[Course Code], tblSections.Section, tblSections.Day, tblSections.[Start Time], tblSections.[End Time], tblSections.Room, tblProfessors.[Last Name], tblProfessors.[First Name], Count(tblStudentsCoursesSections.[Student ID]) AS Enrolled, ([tblCourses].[Max])-[Enrolled] AS Slots
    FROM tblCourses INNER JOIN (tblStudentsCoursesSections RIGHT JOIN (tblProfessors INNER JOIN tblSections ON tblProfessors.[ID Number] = tblSections.[Professor ID]) ON (tblStudentsCoursesSections.[Course Code] = tblSections.[Course Code]) AND (tblStudentsCoursesSections.Section = tblSections.Section)) ON tblCourses.[Course Code] = tblSections.[Course Code]
    GROUP BY tblSections.[Course Code], tblSections.Section, tblSections.Day, tblSections.[Start Time], tblSections.[End Time], tblSections.Room, tblProfessors.[Last Name], tblProfessors.[First Name], tblStudentsCoursesSections.[Course Code], tblStudentsCoursesSections.Section, tblCourses.Max
    HAVING (((tblSections.[Course Code])="SOCTEC2"));

我尝试通过将[]替换为"来进行转换，但无效.有什么不对?这就是我在命令行中在mySQL中输入的内容.还会显示相应的错误.

I tried converting it by replacing [] by `` but it didn't work. Is there something wrong? This is what I inputted in the mySQL in the command line. The corresponding error is also displayed.

SELECT tblSections.`Course Code`, tblSections.Section, tblSections.Day, tblSections.`Start Time`, tblSections.`End Time`, tblSections.Room, tblProfessors.`Last Name`, tblProfessors.`First Name`, Count(tblStudentsCoursesSections.`Student ID`) AS Enrolled, (`tblCourses`.`Max`)-`Enrolled` AS Slots FROM tblCourses INNER JOIN (tblStudentsCoursesSections RIGHT JOIN (tblProfessors INNER JOIN tblSections ON tblProfessors.`ID Number` = tblSections.`Professor ID`) ON (tblStudentsCoursesSections.Section = tblSections.Section) AND (tblStudentsCoursesSections.`Course Code` = tblSections.`Course Code`)) ON tblCourses.`Course Code` = tblSections.`Course Code` GROUP BY tblSections.`Course Code`, tblSections.Section, tblSections.Day, tblSections.`Start Time`, tblSections.`End Time`, tblSections.Room, tblProfessors.`Last Name`, tblProfessors.`First Name`, tblStudentsCoursesSections.`Course Code`, tblStudentsCoursesSections.Section, tblCourses.Max HAVING (((tblSections.`Course Code`)="SOCTEC2"));

不是在注册字段中显示注册学生的人数?

Isn't it that the count of the enrolled student will be displayed along the enrolled field?

更新！

I did what you suggested but I reverted back to HAVING keyword since WHERE results to syntax error. 

SELECT 
    tblSections.`Course Code`,
    tblSections.`Section`,
    tblSections.`Day`,
    tblSections.`Start Time`,
    tblSections.`End Time`,
    tblSections.`Room`,
    tblProfessors.`Last Name`,
    tblProfessors.`First Name`,
    COUNT(tblStudentsCoursesSections.`Student ID`) AS `Enrolled`,
    /* Since Enrolled was just defined as an alias in this scope you cannot use it 
      in the SELECT yet, but you can do the aggregate COUNT again */
    (`tblCourses`.`Max` - COUNT(tblStudentsCoursesSections.`Student ID`)) AS `Slots`
FROM
    # Order your table joins according to the pairs used in ON clauses...
    tblCourses
    INNER JOIN tblSections ON tblCourses.`Course Code` = tblSections.`Course Code`
    RIGHT JOIN tblProfessors ON tblProfessors.`ID Number` = tblSections.`Professor ID`
    INNER JOIN tblStudentsCoursesSections 
        ON ((tblStudentsCoursesSections.Section = tblSections.Section)
        AND (tblStudentsCoursesSections.`Course Code` = tblSections.`Course Code`))
GROUP BY 
    tblSections.`Course Code`,
    tblSections.`Section`,
    tblSections.`Day`,
    tblSections.`Start Time`,
    tblSections.`End Time`,
    tblSections.`Room`,
    tblProfessors.`Last Name`,
    tblProfessors.`First Name`,
    /* These are not in your SELECT list and so should probably not be in the GROUP BY
    tblStudentsCoursesSections.`Course Code`,
    tblStudentsCoursesSections.`Section`,
     Might need to group on `Slots` instead of tblCourses.Max */
    tblCourses.Max
/* This should be a WHERE rather than HAVING since it does not operate on an aggregate */
HAVING
    /* Single quotes preferred for string literals */
    tblSections.`Course Code` = 'SOCTEC2';

不幸的是，未出现注册学生为零的部分.为什么会这样?

Unfortunately, sections with zero enrolled student does not show up. Why is that so?

推荐答案

MS Access有一个不寻常的要求，那就是将JOIN括在一组混乱的嵌套()中.您需要做的是通过检查各种ON条件，然后适当地排列它们，来阐明哪些表是成对连接的

MS Access has an unusual requirement that JOINs be enclosed in a messy set of nested (). What you need to do here is unravel which tables are joined in pairs by inspecting the various ON conditions then lining them up appropriately

我还将其余的列和别名包含在GROUP BY中您可能遗漏的反引号中.

I've also enclosed the rest of your columns and aliases in backticks inside the GROUP BY where you had some missing.

SELECT 
    tblSections.`Course Code`,
    tblSections.`Section`,
    tblSections.`Day`,
    tblSections.`Start Time`,
    tblSections.`End Time`,
    tblSections.`Room`,
    tblProfessors.`Last Name`,
    tblProfessors.`First Name`,
    COUNT(tblStudentsCoursesSections.`Student ID`) AS `Enrolled`,
    /* Since Enrolled was just defined as an alias in this scope you cannot use it 
      in the SELECT yet, but you can do the aggregate COUNT again */
    (`tblCourses`.`Max` - COUNT(tblStudentsCoursesSections.`Student ID`)) AS `Slots`
FROM
    # Order your table joins according to the pairs used in ON clauses...
    tblCourses
    INNER JOIN tblSections ON tblCourses.`Course Code` = tblSections.`Course Code`
    /* This RIGHT JOIN is assumed to be returning all records from tblProfessors regardless
       of match in tblSections. If that is the opposite of what was intended, change to LEFT JOIN */
    RIGHT JOIN tblProfessors ON tblProfessors.`ID Number` = tblSections.`Professor ID`
    INNER JOIN tblStudentsCoursesSections 
        ON (tblStudentsCoursesSections.Section = tblSections.Section)
        AND (tblStudentsCoursesSections.`Course Code` = tblSections.`Course Code`)
/* This should be a WHERE rather than HAVING since it does not operate on an aggregate */
WHERE 
    /* Single quotes preferred for string literals */
    tblSections.`Course Code` = 'SOCTEC2';
GROUP BY 
    tblSections.`Course Code`,
    tblSections.`Section`,
    tblSections.`Day`,
    tblSections.`Start Time`,
    tblSections.`End Time`,
    tblSections.`Room`,
    tblProfessors.`Last Name`,
    tblProfessors.`First Name`,
    /* These are not in your SELECT list and so should probably not be in the GROUP BY
    tblStudentsCoursesSections.`Course Code`,
    tblStudentsCoursesSections.`Section`,
     Might need to group on `Slots` instead of tblCourses.Max */
    `Slots`

附录

不幸的是，未出现注册学生为零的部分.为什么会这样?

Unfortunately, sections with zero enrolled student does not show up. Why is that so?

由于您的INNER JOIN在tblStudentsCoursesSections和tblSections之间，因此tblStudentsCoursesSections中没有记录会从结果中删除该部分.若要确保仍返回该部分，请使用LEFT JOIN tblStudentsCoursesSections代替INNER JOIN tblStudentsCoursesSections.由于tblSections位于联接的左侧"，因此即使没有匹配项，它也会返回.

Since you have an INNER JOIN between tblStudentsCoursesSections and tblSections, no records in tblStudentsCoursesSections will eliminate that section from the results. To ensure the section is returned anyway, use a LEFT JOIN tblStudentsCoursesSections in place of INNER JOIN tblStudentsCoursesSections. Since tblSections is on the "left side" of the join, it will return even if there's no match.

这篇关于如何将此访问查询转换为mySQL查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！