如何在MySQL中进行特定查询

XXIV 发表于 Dev

二十四

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，因此对我来说是非常新的。我真的很感谢您的帮助。

洛文·戴尔（Loveen Dyall）

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值上。然后，选择两列进行显示。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。