mysql - 如何参数化 JOIN？

Question

给出下表

mysql> describe customers;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| customer_id | int(11)     | NO   | PRI | NULL    | auto_increment |
| login       | varchar(16) | NO   |     | NULL    |                |
| password    | varchar(40) | NO   |     | NULL    |                |
| name        | varchar(32) | NO   | UNI | NULL    |                |
| address     | varchar(64) | NO   |     | NULL    |                |
| contact     | varchar(32) | NO   |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
6 rows in set (0.02 sec)  

mysql> describe orders;
+-----------------+-------------+------+-----+-------------------+----------------+
| Field           | Type        | Null | Key | Default           | Extra          |
+-----------------+-------------+------+-----+-------------------+----------------+
| customer_id     | int(11)     | NO   | MUL | NULL              |                |
| order_id        | int(11)     | NO   | PRI | NULL              | auto_increment |
| order_timeStamp | timestamp   | NO   |     | CURRENT_TIMESTAMP |                |
| item            | varchar(16) | NO   |     | NULL              |                |
| price           | double      | NO   |     | NULL              |                |
+-----------------+-------------+------+-----+-------------------+----------------+
5 rows in set (0.04 sec)

我想要获取与参数化查询匹配的所有订单行(我的代码将传入 customer_id)，以及客户表中的客户名称。

我该怎么做？

Answer 1

JOIN两个表，并将条件放在查询末尾的 WHERE 子句中:

SELECT
  o.order_id,  
  o.order_timestamp,
  o.item,
  o.price,
  c.name,
  c.address,
  c.contact
FROM Orders AS o
INNER JOIN Customers AS c ON o.customer_id = c.customer_id
WHERE o.customer_id = ?