语法错误数据库SQL [英] Syntax Error Database SQL

问题描述

按名称列出客人，并在每家酒店预订客房的次数。按照从最常见到最不常见的客人的顺序排列清单。



以下是我们正在处理的表格。



HOTEL（HotelNo，HotelName，City）



ROOM_TYPE（RoomType，Descr，RoomRate）



ROOM（HotelNo，RoomNo，RoomType，PhoneExt）

FK1：外键HotelNo引用HOTEL

FK2：外键RoomType引用ROOM_TYPE

GUEST（GuestNo，FirstName，LastName，Address，City，State，ZipCode）

GUEST_PHONE（PhoneNumber，GuestNo，PhoneType）

FK：外键GuestNo引用GUEST



预订（ResNum，HotelNo，RoomNo，GuestNo，ArrivalDate，DepartureDate，NumPersons）

FK1：外键（HotelNo，RoomNo）参考房间

FK2：外键GuestNo引用GUEST



这是第三个用于检索每个人姓名的SQL语句，以及已在此处预订的GuestNo酒店。我不知道在没有语法错误的情况下添加count函数的位置。

  SELECT  GUEST.FirstName，COUNT（RESERVATION.GuestNo）
  FROM  GUEST  INNER   JOIN 保留 ON  GUEST.GuestNo = RESERVATION.GuestNo 
  ORDER   BY  GUEST.FirstName 
  GROUP   BY  GUEST.GuestNo 

这在我的查询表达式GUEST.FirstName中给出了语法错误，GROUP BY GUEST.GuestNo。它正在检索已经预订的名字。

解决方案

解决方案2只能更改组

  SELECT  GUEST.FirstName，Count（RESERVATION.GuestNo） AS  CountOfGuestNo 
  FROM  GUEST  INNER   JOIN 预订 ON  GUEST.GuestNo = RESERVATION.GuestNo 
  GROUP   BY  GUEST.FirstName 
  ORDER   BY  GUEST.FirstName 

SELECT COUNT（RESERVATION.GuestNo），GUEST.FirstName，RESERVATION.GuestNo



来自客人内部联系预订在GUEST.GuestNo = RESERVATION.GuestNo



GROUP BY GUEST.FirstName



我也试过这个组合但是Access告诉我，你试图执行一个不包含指定表达式'GuestNo'的查询作为聚合函数的一部分

试试这个：

  SELECT  GUEST.FirstName，COUNT（保留） .GuestNo）
  FROM  GUEST  INNER   JOIN 保留 ON  GUEST.GuestNo = RESERVATION.GuestNo 
  GROUP   BY  GUEST.GuestNo 
  ORDER   BY  GUEST.FirstName ; 

List the guests by name and the number of times each has reserved a room at one of our hotels. Arrange the list in order from most-frequent to least-frequent guest.

Here are the tables we are working on.

HOTEL (HotelNo, HotelName, City)

ROOM_TYPE (RoomType, Descr, RoomRate)

ROOM (HotelNo, RoomNo, RoomType, PhoneExt)
FK1: Foreign key HotelNo references HOTEL
FK2: Foreign key RoomType references ROOM_TYPE
GUEST (GuestNo, FirstName, LastName, Address, City, State, ZipCode)
GUEST_PHONE (PhoneNumber, GuestNo, PhoneType)
FK: Foreign key GuestNo references GUEST

RESERVATION (ResNum, HotelNo, RoomNo, GuestNo, ArrivalDate, DepartureDate, NumPersons)
FK1: Foreign Key (HotelNo, RoomNo) references ROOM
FK2: Foreign Key GuestNo references GUEST

This is third SQL statement that retrieves everyone's name and the GuestNo who have made a reservation at the hotel. I don't know where to add the count function without getting a Syntax Error.

SELECT GUEST.FirstName,  COUNT (RESERVATION.GuestNo)
FROM GUEST INNER JOIN RESERVATION ON GUEST.GuestNo =RESERVATION.GuestNo
ORDER BY GUEST.FirstName
GROUP BY GUEST.GuestNo

This is giving me a Syntax error in my query expression GUEST.FirstName, GROUP BY GUEST.GuestNo. It was retrieving the names who have made reservations.

解决方案

solution 2 only to change group by

SELECT GUEST.FirstName, Count(RESERVATION.GuestNo) AS CountOfGuestNo
FROM GUEST INNER JOIN RESERVATION ON GUEST.GuestNo = RESERVATION.GuestNo
GROUP BY GUEST.FirstName
ORDER BY GUEST.FirstName

SELECT COUNT(RESERVATION.GuestNo), GUEST.FirstName, RESERVATION.GuestNo

FROM GUEST INNER JOIN RESERVATION on GUEST.GuestNo = RESERVATION.GuestNo

GROUP BY GUEST.FirstName

I've tried this combination as well but Access tells me, You tried to execute a query that does not include the specified expression 'GuestNo' as part of an aggregate functio

Try this:

SELECT GUEST.FirstName,  COUNT (RESERVATION.GuestNo)
FROM GUEST INNER JOIN RESERVATION ON GUEST.GuestNo =RESERVATION.GuestNo
GROUP BY GUEST.GuestNo
ORDER BY GUEST.FirstName;

这篇关于语法错误数据库SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！