mysql - 在以下模式中加入多个表后如何提取结果？

Question

我使用了以下命令来创建表。

CREATE TABLE Customer(
custid int NOT NULL AUTO_INCREMENT,
fname varchar(30) NOT NULL,
lname varchar(30) NOT NULL,
mno varchar(10),
password varchar(30),
PRIMARY KEY (custid)
);

CREATE TABLE Employee(
empid int NOT NULL,
fname varchar(30) NOT NULL,
lname varchar(30) NOT NULL,
mno varchar(10),
password varchar(30),
PRIMARY KEY (empid)
);

CREATE TABLE Address(
id int NOT NULL,
street varchar(30),
doorno varchar(30),
city varchar(30),
statee varchar(30),
zip varchar(5),
county varchar(30) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (id) REFERENCES Customer (custid)
);

CREATE TABLE EmpAddress(
id int NOT NULL,
street varchar(30),
doorno varchar(30),
city varchar(30),
statee varchar(30),
zip varchar(5),
county varchar(30) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (id) REFERENCES Employee (empid)
);

CREATE TABLE Service(
serviceid int NOT NULL,
serviceType varchar(30),
amount int,
PRIMARY KEY (serviceid)
);

CREATE TABLE Booking(
bookingid int NOT NULL AUTO_INCREMENT,
empid int NOT NULL,
custid int NOT NULL,
serviceid int NOT NULL,
PRIMARY KEY (bookingid),
FOREIGN KEY (empid) REFERENCES Employee (empid),
FOREIGN KEY (custid) REFERENCES Customer (custid),
FOREIGN KEY (serviceid) REFERENCES Service (serviceid)
);

CREATE TABLE Availability(
empid int NOT NULL,
datee date NOT NULL,
startTime int NOT NULL,
PRIMARY KEY (empid, datee, startTime),
FOREIGN KEY (empid) REFERENCES Employee (empid)
);

CREATE TABLE Transactions(
bookingid int NOT NULL,
paymentMethod varchar(20),
serviceid int,
amount int,
PRIMARY KEY (bookingid),
FOREIGN KEY (bookingid) REFERENCES Booking (bookingid),
FOREIGN KEY (serviceid) REFERENCES Service (serviceid)
);

我正在尝试编写一个返回 bookingid、firstName、lastName、街道、门号、邮政编码、serviceType、日期和开始时间 GIVEN empid 的查询。

它本质上是 5 个表的连接，我将查询实现为:

SELECT B.bookingid, C.fname, C.lname, AD.street,
AD.doorno, AD.zip,S.serviceType, A.datee, A.startTime
FROM booking as B, availability as A, customer as C, 
address as AD, Service as   S 
WHERE
B.empid=501 AND B.custid=C.custid 
AND C.custid=AD.id 
AND  B.serviceid=S.serviceid AND B.empid=A.empid;

要求的结果应该是:

BookingID  fname  lname  street  doorno  zipcode  serviceType  date          starttime
   1        X       Y     ABC      33      5335      Clean    2015-05-20    9
   2        P       Q     NMO      55      8294      Shift    2015-06-11    11

但结果给了我表格的叉积:

BookingID  fname  lname  street  doorno  zipcode  serviceType  date      starttime
   1        X       Y     ABC      33      5335      Clean    2015-05-20    9
   1        X       Y     ABC      33      5335      Shift    2015-06-11    11
   2        P       Q     NMO      55      8294      Clean    2015-05-20    9 
   2        P       Q     NMO      55      8294      Shift    2015-06-11    11

请让我知道我的查询有什么问题。

Answer 1

Availability 的主键当前是 (empid, datee, startTime)，但 JOIN 仅在 Booking.empid 上完成 - 可能还有更多每个员工不止一个可用性行。

我相信您需要向 Booking 添加时间戳，并在加入 Availability 期间插入日期:

CREATE TABLE Booking(
   -- ...
   bookingDate datetime NOT NULL,
   -- ...
);

我还建议您向可用性添加和结束日期时间范围，并将日期和时间存储为一个(否则您将需要不断添加时间):

CREATE TABLE Availability(
   -- ...
  startDateTime datetime NOT NULL,
  endDateTime datetime NOT NULL,
  PRIMARY KEY (empid, startDateTime), -- Put some rules to prevent overlap
);

查询然后将预订插入日期和时间的可用性:

SELECT B.bookingid, C.fname, C.lname, AD.street, AD.doorno, AD.zip,
       S.serviceType, A.startDateTime
FROM 
   booking as B
   INNER JOIN customer as C
     ON B.custid=C.custid 
   INNER JOIN availability as A
     ON B.empid=A.empid AND b.bookingDate BETWEEN A.startDateTime AND A.endDateTime
   INNER JOIN address as AD
     ON C.custid=AD.id 
   INNER JOIN Service as S
     ON B.serviceid=S.serviceid
WHERE
  B.empid=501;

我还调整了连接以使用 JOIN ON 而不是在 WHERE 中进行连接。

这将解决重复的可用性数据。但是，我看不出同一预订的 ServiceType 有何不同。