mysql - 如何在MySQL中进行特定查询

Question

mysql> select * from  Orders;
+------+------+------+------------+------------+
| ONO  | CNO  | ENO  | RECEIVED   | SHIPPED    |
+------+------+------+------------+------------+
| 1020 | 1111 | 1000 | 1994-12-10 | 1994-12-12 |
| 1021 | 1111 | 1000 | 1995-01-12 | 1995-01-15 |
| 1022 | 2222 | 1001 | 1995-02-13 | 1995-02-20 |
| 1023 | 3333 | 1000 | 2003-02-15 | NULL       |
| 1024 | 4444 | 1000 | 2003-02-15 | 2003-02-16 |
| 1025 | 5555 | 1000 | 2003-02-15 | 2003-02-16 |
+------+------+------+------------+------------+

mysql> select * from Employees;
+------+--------+-------+------------+
| ENO  | ENAME  | ZIP   | HDATE      |
+------+--------+-------+------------+
| 1000 | Jones  | 67226 | 1995-12-12 |
| 1001 | Smith  | 60606 | 1992-01-01 |
| 1002 | Brown  | 50302 | 1994-09-01 |
| 1003 | Green  | 28411 | 2002-09-01 |
| 1004 | Purple | 28411 | 2003-01-01 |
+------+--------+-------+------------+

mysql> select * from Customers;
+------+---------+------------------+-------+--------------+
| CNO  | CNAME   | STREET           | ZIP   | PHONE        |
+------+---------+------------------+-------+--------------+
| 1111 | Charles | 123 Main St.     | 67226 | 316-636-5555 |
| 2222 | Bertram | 237 Ash Avenue   | 67226 | 316-689-5555 |
| 3333 | Barbara | 111 Inwood St.   | 60606 | 316-111-1234 |
| 4444 | Will    | 111 Kenwood St.  | 54444 | 416-111-1234 |
| 5555 | Bill    | 211 Marlwood St. | 28408 | 416-111-1235 |
| 6666 | Keely   | 211 Pinewood St. | 28411 | 416-111-1235 |
| 7777 | Maera   | 211 Marlwood St. | 28408 | 416-111-1235 |
+------+---------+------------------+-------+--------------+

我需要:获取 cname 和 ename 对，以便具有名称的客户cname 已通过名为 ename 的员工下了订单。

我确信这对大多数人来说似乎很容易，但我一直无法弄清楚这个出来了。我已经尝试过:

select distinct Customers.CNAME, Employees.ENAME
from   Customers, Employees
where  Customers.CNO in (
    select Customers.CNO
    from   Customers, Orders
    where  Customers.CNO = Orders.CNO
) and
where  Employees.ENO in (
    select Employees.ENO
    from   Orders, Employees
    where  Employees.ENO = Orders.ENO
);

有多个版本，但我似乎无法让它工作。这是我第一次使用 SQL，所以它对我来说非常陌生。我真的很感激任何帮助。

Answer 1

select customers.CNAME, employees.ENAME from customers 
inner join orders on customers.CNO = orders.CNO
inner join employees on orders.ENO = employees.ENO;

使用 join 语句可以更轻松地读取您尝试执行的 SQL。这会将订单表与 CNO 值上的客户以及订单表与 ENO 值上的员工连接起来。然后它选择两列进行显示。