mysql - 关于 SQL View，我有些困惑

Question

我有这些表:

• BRANCH(Bno、街道、地区、城市、Pcode、电话号码、传真号码)
• STAFF(Sno、Fname、Lname、Address、Tel_No、Position、Sex、DOB、Salary、NIN、Bno)
• PROPERTY_FOR_RENT(Pno、街道、区域、城市、Pcode、类型、房间、租金、Ono、Sno、Bno)

将创建一个 View BranchStats(Bno、NumStaff、NumProps)，对于任何分支，它指示雇用的员工数量和属性句柄的数量.

CREATE VIEW BranchStats
AS 
   SELECT 
       branch.Bno Bno,
       Count (Distinct Staff.Bno) NumStaff,
       Count (Distinct property_for_rent.Sno) NumProps
   FROM 
       branch
   JOIN 
       Staff ON Staff.Bno = branch.Bno
   JOIN 
       property_for_rent ON property_for_rent.Sno = Staff.Sno
   GROUP BY 
       branch.Bno

错误

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Distinct staff.Bno) NumStaff, Count (Distinct property_for_rent.Sno) Num' at line 4

是不是我的SQL语句有问题？需要帮助

Answer 1

MySQL(默认情况下)不允许函数名和左括号之间有空格。因此，请尝试删除这些空格:

CREATE VIEW BranchStats AS 
   SELECT branch.Bno,
          Count(Distinct Staff.Bno) as NumStaff,
          Count(Distinct property_for_rent.Sno) as NumProps
   FROM branch JOIN 
        Staff
        ON Staff.Bno = branch.Bno JOIN 
        property_for_rent
        ON property_for_rent.Sno = Staff.Sno
   GROUP BY  branch.Bno;

这在 documentation 中有解释:

[ . . .] the parser uses the following rules by default to distinguish whether their names are being used as function calls or as identifiers in nonexpression context:

• To use the name as a function call in an expression, there must be no whitespace between the name and the following “(” parenthesis character.

• Conversely, to use the function name as an identifier, it must not be followed immediately by a parenthesis.

The requirement that function calls be written with no whitespace between the name and the parenthesis applies only to the built-in functions that have special considerations. COUNT is one such name.