使用select语句时获取重复数据

Question

当尝试使用选择语句时，我得到重复的数据，我有9个房间，但我在试图显示它们时得到大约50-70个房间。帮助吗?

我正在尝试插入数据并使用select语句显示它。

create table gym (
GymName VARCHAR(200) primary key,
openTime time not null,
closeTime time not null,
Price decimal not null,
);
create table Spa (
spaName VARCHAR(200) primary key,
openTime  time not null,
closeTime time not null,
Price decimal not null,
);
create table customer (
CustomerID int primary key,
Firstname varchar(200) not null,
LastName varchar(200) not null,
DOB date not null check (DATEDIFF(year,DOB,getdate ()) > 18) ,
Gender char(4) not null check(Gender ='M' or Gender = 'F'),
Address varchar(200) not null default 'Jordan',
spaName VARCHAR(200)  foreign key references Spa(spaName),
GymName VARCHAR(200)  foreign key references gym(GymName),
);
Create table CustomerPhoNo (
CustomerID int foreign key references customer(CustomerID),
PhoneNo bigint not null,
);
create table Room (
roomNo int primary key,
Availability char(4) not null,
NoOfBeds int not null,
Rate int not null,
CheckIn date,
CheckOut date,
Price Decimal not null,
Breakfast char(4),
CustomerID int foreign key references customer(CustomerID),
);
create table LocationOfRoom (
roomNo int foreign key references Room(roomNo),
seaview char(4),
Location varchar(20) not null,
);
create table RoomType (
roomNo int foreign key references Room(roomNo),
familyRoom char(4),
doubleRoom char(4),
singleRoom char(4),
);
create table Gservice (
GymName VARCHAR(200) foreign key references gym(GymName),
Service VARCHAR(500) not null,
MachineType VARCHAR(500) not null,
);
create table PaymentCard (
CardID int primary key,
issueDate date not null,
Expirydate date not null,
CustomerID int foreign key references customer(CustomerID),
);
insert into customer values (325,'Mohammad','Alasharan','06-04-1984','M','Amman', 'BeautySpa', 'StrongBody')
insert into customer values (348,'John','Shelby','10-18-1998','M','Birmingham', 'LushLife', 'SilverGym')
insert into customer values (495,'Thomas','Hoffman','04-26-1968','M','Johannesburg', 'RelaxationTherapy', 'SilverGym')
insert into customer values (194,'Anne','Frank','07-22-2001','F','Frankfurt', 'BeautySpa', 'StrongBody')
insert into customer values (628,'Katie','Swan','02-10-1997','F','New South Wales', 'LushLife', 'FitnessHeroes')
insert into customer values (246,'Mahmoud','Alkutaifan','04-21-1994','M','Amman', 'BeautySpa', 'FitnessHeroes')
insert into customer values (864,'Karl-Heinz','Rummenigge','09-25-1955','M','Lippstadt', 'RelaxationTherapy', 'FitnessHeroes')
insert into customer values (824,'Dennis','Law','09-21-1979','M','london', 'RelaxationTherapy', 'FitnessHeroes')
insert into customer values (463,'Carles','Puyol','06-17-1973','M','madrid', 'LushLife', 'FitnessHeroes')
insert into Room values (124,'yes','1','4',null,null,'30','yes',null)
insert into Room values (135,'no','2','5','05-06-2022','05-09-2022','55','yes',495)
insert into Room values (121,'yes','1','3',null,null,'40','yes',null)
insert into Room values (139,'no','3','4','05-10-2022','05-14-2022','110','no',194)
insert into Room values (131,'no','3','3','05-18-2022','05-22-2022','130','yes',348)
insert into Room values (136,'no','4','4','04-14-2022','04-24-2022','120','yes',194)
insert into Room values (179,'yes','4','5',null,null,'95','no',null)
insert into Room values (138,'no','3','3','04-02-2022','04-06-2022','75','no',246)
insert into Room values (146,'no','3','5','05-10-2022','05-14-2022','80','yes',864)
insert into LocationOfRoom values (124,'no','south')
insert into LocationOfRoom values (135,'yes','north')
insert into LocationOfRoom values (121,'yes','south')
insert into LocationOfRoom values (139,'no','north')
insert into LocationOfRoom values (131,'no','East')
insert into LocationOfRoom values (136,'yes','west')
insert into LocationOfRoom values (179,'no','south')
insert into LocationOfRoom values (138,'no','west')
insert into LocationOfRoom values (146,'yes','north')
insert into RoomType values (124,'no','no','yes')
insert into RoomType values (135,'no','yes','no')
insert into RoomType values (121,'yes','no','no')
insert into RoomType values (139,'no','no','yes')
insert into RoomType values (131,'no','no','yes')
insert into RoomType values (136,'no','no','yes')
insert into RoomType values (179,'no','no','yes')
insert into RoomType values (138,'no','yes','no')
insert into RoomType values (146,'no','no','yes')
-- display Total number of customers who booked a single room with sea view option
select count(Firstname)
from LocationOfRoom, customer, RoomType, Room
where seaview='yes' and singleRoom='yes'

任何帮助将非常感激，提前感谢你!

Answer 1

FROM子句缺少每个表的连接条件。换句话说，你得到的是表格的笛卡尔积(每一行的组合)。即使是distinct也无济于事(它会得到错误的答案)。使用连接条件将每个表的键相互链接。我将把这个作为练习留给你去尝试，但是这些信息应该足够帮助你了。

Answer 2

我相信你的问题的解决方案是你需要使用DISTINCT:

SELECT COUNT(DISTINCT(Firstname))
FROM LocationOfRoom, customer, RoomType, Room
WHERE seaview='yes' AND singleRoom='yes'

我已经测试过了，它检索了9个房间。