如何创建带有限制（WHERE）的嵌套选择？

huangapple
go评论71阅读模式
英文:

How do you make a nested select with a restriction (WHERE)

问题

Here is the translation of the provided content:

我有一个小型日历应用的数据库，其中人员存储在clients表中，日期存储在calendarDate表中，由于关系是多对多的，所以有一个名为client_date的连接表，它保存了他们两者的id。

我想要做一个嵌套的选择来获取特定人员的所有日期，比如说id = 2。
我想出了以下方法，但它会打印出所有日期并将它们分配给具有该id的人员，而不仅仅打印出他所分配的日期：

```sql
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;

我是不是做错了什么，还是有其他方法可以创建嵌套的选择语句？

我的数据库和数据：

删除数据库 IF EXISTS calendar;
创建数据库 calendar;
使用 calendar;

创建表 clients(
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(100) NOT NULL UNIQUE,
joinedOnDate DATE NOT NULL,
country VARCHAR(100) NOT NULL
);

创建表 emotions(
id INT NOT NULL PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE,
value DOUBLE 
);

创建表 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)
);

创建表 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)	
);

使用 calendar;

向情感表插入数据 (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);

向客户表插入数据 (username, joinedOnDate, country) VALUES
('Malazzar', DATE(NOW()), 'Bulgaria'),
('Preslava981', DATE(NOW()), 'Bulgaria'),
('Thusnake', DATE(NOW()), 'United Kingdom');

向日期表插入数据 (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);

向客户_日期表插入数据 (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


<details>
<summary>英文:</summary>
I have a database for a small calendar app in which people are stored in the clients table, dates are stored in the table calendarDate and since the relations are many to many there is a connecting table called client_date which holds both of their ids.
I want to make a nested select to get all the dates for a particular person lets say with id = 2.
I came up with this, but it prints all of the dates and asigns them to the person with that id, instead of just printing the only ones he is asigned to:

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;

Is there something I am doing wrong or is there another way to make a nested select statement ?
My database and data:

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);

The output I get for the particular client:

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


</details>
# 答案1
**得分**: 1
为什么不只使用 `join`？
从 clients c  join
client_date cd
on cd.clientId = c.id join
calendarDate as d 
on d.id cd.dateId 
where c.id = 2;
<details>
<summary>英文:</summary>
Why not just use `join`s?
select c.username, c.country, d.*
from clients c  join
client_date cd
on cd.clientId = c.id join
calendarDate as d 
on d.id cd.dateId 
where c.id = 2;
</details>
# 答案2
**得分**: 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` 进行连接也可以完成同样的工作。
<details>
<summary>英文:</summary>
Your mistake is here:
WHERE clientId in (SELECT id from clients)
*Every* client ID exists in the clients table. You want this instead:
WHERE clientId = c.id
You could also use a non-corelated subquery instead, which is easier to read:
on (c.id, d.id) in (select clientid, dateid from client_date)
But as has been mentioned by others, a mere join to `client_date` would do the same job.
</details>
# 答案3
**得分**: 1

我想要创建一个嵌套的选择语句，以获取特定人员的所有日期，假设其id = 2。

可以使用以下方式实现：

select *
from calendarDate
where day in (
  select dateId
  from client_date
  where clientId = 2
)

等效的JOIN查询如下：

select d.*
from calendarDate d
join client_date cd
  on cd.dateId = d.day
where cd.clientId = 2

在DB Fiddle上查看

注意：这两个查询只在client_date表中没有重复项的情况下才是等效的。

如果您想从多个表中选择数据，那么您实际上需要使用JOIN。在这种情况下，询问如何在没有使用JOIN的情况下进行操作是没有意义的。


<details>
<summary>英文:</summary>
&gt; I want to make a nested select to get all the dates for a particular
&gt; person lets say with id = 2
This can be done with:
select *
from calendarDate
where day in (
select dateId
from client_date
where clientId = 2
) 
An equivalent JOIN query would be:
select d.*
from calendarDate d
join client_date cd
on cd.dateId = d.day
where cd.clientId = 2
[View on DB Fiddle](https://www.db-fiddle.com/f/eVUdtqRZiV4hjvEm6tuV6N/0)
Note: The two queries are only equivalent, if there are no duplicates in the `client_date` table.
If you want to select data from more than one table, then you actualy **need** to use a JOIN. In that case asking for how to do it without joins, wouldn&#39;t make sense.
</details>

huangapple
  • 本文由 发表于 2020年1月3日 21:52:45
  • 转载请务必保留本文链接：https://go.coder-hub.com/59579773.html
  • database
  • mysql
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定