mysql - 在 mysql 中使用 select from where 命令时遇到问题

Question

这个问题已经有答案了:

Joining three tables using MySQL (11 个回答)

Explicit vs implicit SQL joins (12 个回答)

已关闭 4 年前。

大家好，大家对所有这些数据库东西都有点陌生，正在尝试让测试数据库为大学项目工作。

我有一个名为“customers”和“caravans”的表，还有另一个名为“customers_caravans”的表，它将两者联系在一起。

然后我有一个名为“以前的作品”的决赛 table 。这详细介绍了 customer_caravanID 之前的工作。

但是，似乎无法计算出仅提取特定客户之前工作的数据的 where 语句。

它似乎只是循环遍历整个客户列表？？？

对于某人来说，这将是一个简单的修复，要么是我的选择命令错误，要么是我的数据库的整个设计。

无论如何，这是数据库......

describe customers;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| customerID     | int(11)      | NO   | PRI | NULL    | auto_increment |
| name           | varchar(256) | NO   |     | NULL    |                |
| street_address | varchar(256) | YES  |     | NULL    |                |
| postcode       | varchar(256) | YES  |     | NULL    |                |
| telephone      | varchar(256) | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+


describe caravans;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| caravanID   | int(11)      | NO   | PRI | NULL    | auto_increment |
| make        | varchar(256) | NO   |     | NULL    |                |
| model       | varchar(256) | NO   |     | NULL    |                |
| vin_number  | varchar(256) | YES  |     | NULL    |                |
| axle_number | tinyint(4)   | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

describe customers_caravans;
+----------------------+---------+------+-----+---------+----------------+
| Field                | Type    | Null | Key | Default | Extra          |
+----------------------+---------+------+-----+---------+----------------+
| customers_caravansID | int(11) | NO   | PRI | NULL    | auto_increment |
| customerID           | int(11) | NO   | MUL | NULL    |                |
| caravanID            | int(11) | NO   | MUL | NULL    |                |
+----------------------+---------+------+-----+---------+----------------+

describe previous_works;
+----------------------+---------------+------+-----+---------+-------+
| Field                | Type          | Null | Key | Default | Extra |
+----------------------+---------------+------+-----+---------+-------+
| customers_caravansID | int(11)       | NO   | MUL | NULL    |       |
| work                 | varchar(256)  | YES  |     | NULL    |       |
| partID               | int(11)       | YES  | MUL | NULL    |       |
| date                 | date          | YES  |     | NULL    |       |
| cost                 | decimal(13,2) | YES  |     | NULL    |       |
+----------------------+---------------+------+-----+---------+-------+

以下数据...

INSERT INTO customers (name,street_address,postcode,telephone) VALUES 
('Megan Rose Hoobar', '41-43 Barrows Street', 'IP14 6HJ', '01449 77777'),
 ('Tim 
Smith', '17 Wynton Rise', 'JF77 2AB', '01564 673742');

INSERT INTO caravans (make,model,vin_number,axle_number) VALUES 
('Elddis','Buccaneer Commodore','1HGBH41JXMN1091866','2'), 
('Lunar','Venus 590/6','1HGBH41JXMN1091111','1');

INSERT INTO customers_caravans (customerID,caravanID) VALUES ('1','1'), ('2','2');

INSERT INTO previous_works (customers_caravansID,work,partID,date,cost) 
VALUES 
('1','Service','3','2015-04-15','150'),
('1','Service','3','2016-04-15','150'), 
('1','Service','5','2017-04-15','180'),
('2','Solar Panel Fitting','1','2018-02-18','380');

IE 这个选择，我只想获取customers_caravansID 匹配1 的客户的数据，即在这种情况下，只有我重复获得两个客户的服务...

select x.name
     , y.make
     , y.model
     , p.work 
  from caravans y
     , customers x
     , customers_caravans xy
     ,previous_works p
 where p.customers_caravansID = "1";
+----------------------+--------+---------------------+---------+
| name                 | make   | model               | work    |
+----------------------+--------+---------------------+---------+
| Megan Rose Hoobar  | Elddis | Buccaneer Commodore | Service |
| Megan Rose Hoobar  | Elddis | Buccaneer Commodore | Service |
| Megan Rose Hoobar  | Elddis | Buccaneer Commodore | Service |
| Megan Rose Hoobar  | Elddis | Buccaneer Commodore | Service |
| Megan Rose Hoobar  | Elddis | Buccaneer Commodore | Service |
| Megan Rose Hoobar  | Elddis | Buccaneer Commodore | Service |
| Megan Rose Hoobar  | Lunar  | Venus 590/6         | Service |
| Megan Rose Hoobar  | Lunar  | Venus 590/6         | Service |
| Megan Rose Hoobar  | Lunar  | Venus 590/6         | Service |
| Megan Rose Hoobar  | Lunar  | Venus 590/6         | Service |
| Megan Rose Hoobar  | Lunar  | Venus 590/6         | Service |
| Megan Rose Hoobar  | Lunar  | Venus 590/6         | Service |
| Tim Smith          | Elddis | Buccaneer Commodore | Service |
| Tim Smith          | Elddis | Buccaneer Commodore | Service |
| Tim Smith          | Elddis | Buccaneer Commodore | Service |
| Tim Smith          | Elddis | Buccaneer Commodore | Service |
| Tim Smith          | Elddis | Buccaneer Commodore | Service |
| Tim Smith          | Elddis | Buccaneer Commodore | Service |
| Tim Smith          | Lunar  | Venus 590/6         | Service |
| Tim Smith          | Lunar  | Venus 590/6         | Service |
| Tim Smith          | Lunar  | Venus 590/6         | Service |
| Tim Smith          | Lunar  | Venus 590/6         | Service |
| Tim Smith          | Lunar  | Venus 590/6         | Service |
| Tim Smith          | Lunar  | Venus 590/6         | Service |
+----------------------+--------+---------------------+---------+

Answer 1

这是使用连接多个表的最新形式的查询。请注意它如何关联每个表。

SELECT
    b.name,
    a.make,
    a.model,
    d.work
FROM caravans a
JOIN customers_caravans c
    ON a.caravamID = c.caravanID
JOIN customers b
    ON a.customerID = b.customerID
JOIN previous_works d
    ON c.customers_caravansID = d.customers_caravansID
WHERE d.customers_caravansID = "1";