mysql - 您如何进行有限制的嵌套选择(WHERE)

Question

我有一个小型日历应用程序的数据库，其中人员存储在 clients 表中，日期存储在表 calendarDate 中，由于关系是多对多的，所以有一个名为 client_date 的连接表，其中包含他们的两个 ID .

我想进行嵌套选择以获取特定人员的所有日期，比如 id = 2。我想出了这个，但它会打印所有日期并将它们分配给具有该 ID 的人，而不是只打印他分配给的唯一日期:

SELECT c.username
     , c.country
     , d.day
     , d.month
     , d.year
     , d.dayOfWeek
     , d.weekOfYear
     , d.emotionId
     , d.id 
  from clients as c 
  join calendarDate as d 
    on d.id in (SELECT dateId 
                  from client_date 
                 WHERE clientId in (SELECT id 
                                      from clients )
               ) 
 where c.id = 2;

是我做错了什么，还是有另一种方法来制作嵌套的 select 语句？

我的数据库和数据:

DROP DATABASE IF EXISTS calendar;
CREATE DATABASE calendar;
USE calendar;

CREATE TABLE clients(
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(100) NOT NULL UNIQUE,
joinedOnDate DATE NOT NULL,
country VARCHAR(100) NOT NULL
);

CREATE TABLE emotions(
id INT NOT NULL PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE,
value DOUBLE 
);

CREATE TABLE calendarDate(
id INT AUTO_INCREMENT PRIMARY KEY,
day INT NOT NULL,
month INT NOT NULL,
year INT NOT NULL,
dayOfWeek VARCHAR(20) NOT NULL,
weekOfYear int NOT NULL,
emotionId INT NOT NULL, 
CONSTRAINT FOREIGN KEY (emotionId)
    REFERENCES emotions( id )
);

CREATE TABLE client_date(
dateId INT NOT NULL,
clientId INT NOT NULL,
CONSTRAINT FOREIGN KEY ( dateId )
    REFERENCES calendarDate( id ) ,
CONSTRAINT FOREIGN KEY ( clientId )
    REFERENCES clients( id ) ,
UNIQUE KEY( dateId, clientId )  
);

USE calendar;

INSERT INTO emotions (id, name, value) VALUES
(0, 'None', 1),
(1, 'Excited', 2.0),
(2, 'Happy', 2.0),
(3, 'Positive', 1.5),
(4, 'Average', 1.0),
(5, 'Mixed', 1),
(6, 'Negative', 0.5),
(7, 'Sad', 0);

INSERT INTO clients (username, joinedOnDate, country) VALUES
('Malazzar', DATE(NOW()), 'Bulgaria'),
('Preslava981', DATE(NOW()), 'Bulgaria'),
('Thusnake', DATE(NOW()), 'United Kingdom');

INSERT INTO calendarDate (day, month, year, dayOfWeek, weekOfYear, emotionId) VALUES
(1, 1, 2019, 'Tuesday', 1, 0),
(2, 1, 2019, 'Wednesday', 1, 0),
(3, 1, 2019, 'Thursday', 1, 0),
(4, 1, 2019, 'Friday', 1, 0),
(5, 1, 2019, 'Saturday', 1, 0),
(6, 1, 2019, 'Sunday', 1, 0),
(7, 1, 2019, 'Monday', 2, 0),
(8, 1, 2019, 'Tuesday', 2, 0),
(9, 1, 2019, 'Wednesday', 2, 0),
(10, 1, 2019, 'Thursday', 2, 0),
(11, 1, 2019, 'Friday', 2, 0),
(12, 1, 2019, 'Saturday', 2, 0),
(13, 1, 2019, 'Sunday', 2, 0),
(14, 1, 2019, 'Monday', 3, 0);

INSERT INTO client_date (clientId, dateId) VALUES
(1, 1),
(1, 2),
(1, 3),
(1, 4),
(1, 5),
(1, 6),
(1, 7),
(2, 8),
(2, 9),
(2, 10),
(2, 11),
(2, 12),
(2, 13),
(2, 14);

我为特定客户获得的输出:

Preslava981 Bulgaria    1   1   2019    Tuesday     1   0   1
Preslava981 Bulgaria    2   1   2019    Wednesday   1   0   2
Preslava981 Bulgaria    3   1   2019    Thursday    1   0   3
Preslava981 Bulgaria    4   1   2019    Friday      1   0   4
Preslava981 Bulgaria    5   1   2019    Saturday    1   0   5
Preslava981 Bulgaria    6   1   2019    Sunday      1   0   6
Preslava981 Bulgaria    7   1   2019    Monday      2   0   7
Preslava981 Bulgaria    8   1   2019    Tuesday     2   0   8
Preslava981 Bulgaria    9   1   2019    Wednesday   2   0   9
Preslava981 Bulgaria    10  1   2019    Thursday    2   0   10
Preslava981 Bulgaria    11  1   2019    Friday      2   0   11
Preslava981 Bulgaria    12  1   2019    Saturday    2   0   12
Preslava981 Bulgaria    13  1   2019    Sunday      2   0   13
Preslava981 Bulgaria    14  1   2019    Monday      3   0   14

Answer 1

你的错误在这里:

WHERE clientId in (SELECT id from clients)

每个 客户端 ID 都存在于客户端表中。你想要这个:

WHERE clientId = c.id

您也可以改用非相关子查询，这样更易​​于阅读:

on (c.id, d.id) in (select clientid, dateid from client_date)

但是正如其他人所提到的，仅仅加入 client_date 就可以完成同样的工作。