php - MYSQL:一行中有两个结果

Question

我正在解决一个问题。我有两张 table :ORDERS - 定义客户订单

+-------------------+-------------+------+-----+-------------------+----------------+
| Field             | Type        | Null | Key | Default           | Extra          |
+-------------------+-------------+------+-----+-------------------+----------------+
| id                | int(11)     | NO   | PRI | NULL              | auto_increment |
| customer_id       | int(11)     | NO   | MUL | NULL              |                |
| from_date         | date        | YES  |     | NULL              |                |
| datum_dodani      | date        | NO   |     | NULL              |                |
| popis             | text        | NO   |     | NULL              |                |
| status            | tinyint(4)  | NO   |     | 1                 |                |
| cena              | varchar(8)  | NO   |     | NULL              |                |
| vytvoril          | tinyint(4)  | NO   | MUL | NULL              |                |
| vytvoreno         | timestamp   | NO   |     | CURRENT_TIMESTAMP |                |
| vyresil           | tinyint(4)  | YES  |     | NULL              |                |
| prirazena_skupina | tinyint(4)  | YES  |     | NULL              |                |
| narocnost         | smallint(6) | YES  |     | NULL              |                |
| cas               | varchar(6)  | YES  |     | NULL              |                |
| priorita          | tinyint(1)  | NO   |     | 0                 |                |
+-------------------+-------------+------+-----+-------------------+----------------+

和 POLOZKY_OBJEDNAVKY 定义了客户订单的每一行:

+-----------+------------+------+-----+---------+----------------+
| Field     | Type       | Null | Key | Default | Extra          |
+-----------+------------+------+-----+---------+----------------+
| id        | int(11)    | NO   | PRI | NULL    | auto_increment |
| order_id  | int(11)    | YES  | MUL | NULL    |                |
| servis_id | tinyint(4) | YES  | MUL | NULL    |                |
| cena      | int(11)    | YES  |     | NULL    |                |
| pocet     | tinyint(4) | YES  |     | NULL    |                |
| popis     | text       | YES  |     | NULL    |                |
| bike      | text       | YES  |     | NULL    |                |
+-----------+------------+------+-----+---------+----------------+

这两个表通过 polozky_objednavky.order_id ->orders.id 链接

问题是我无法创建返回如下内容的查询:

+-------------------+-------------+--------------+------------------------+
| orders.id         | customer_id | orders.status| polozky_objednavky.bike|
+-------------------+-------------+--------------+------------------------+
| 1                 | 23          |             2|              24, 25, 46|
| 2                 | 25          |             5|                      36|

这意味着在 polozky_objednavky.bike 列中，将有一个 select 从 POLOZKY_OBJEDNAVKY 返回 3 行，键为 POLOZKY_OBJEDNAVKY.ORDER_ID = ORDERS.ID

希望我解释得很好，并对愚蠢的问题表示抱歉，但我尝试使用 JOINS 选择它或从 select 中选择但没有成功:(

谢谢你，米。

Answer 1

您需要加入表格。试试这个:

SELECT o.id AS orders_id,
       o.customer_id,
       o.status,
       p.bike
  FROM ORDERS AS o
  LEFT OUTER JOIN POLOZKY_OBJEDNAVKY AS p
    ON p.order_id = o.id

如果您有特定条件，只需将其作为 WHERE 子句添加到末尾即可。

您可以在 w3school 阅读有关联接的信息。和 sitepoint