mysql - 具有奇怪设置的多级 mysql 查询

Question

我有一个问题。我是一名编码新手，正在学习 php 和 mysql。我正在开发一个应用程序，遇到了一个我无法解决的问题。

我有三个表:

类别:

+-----------+----------+--------------------+| id        | name     | sort |   active    |+-----------+----------+--------------------+|         1 | apple    |    1 |      1      ||         2 | mango    |    2 |      1      ||         3 | blueberry|    3 |      0      |+-----------+----------+--------------------+

服务:

+-----------+----------+----------------------------------------+| id        | category_id | name   | sort |active| process_time |+-----------+------+------+--------+------+------+--------------+|         1 |     1       | slices |  1   |  1   |  2 hours     ||         2 |     1       |  half  |  2   |  1   |  6 hours     ||         3 |     2       | slices |  1   |  1   |  1   hour    ||         4 |     2       | whole  |  2   |  1   |  6  hours    ||         5 |     3       | bunch  |  1   |  0   |  12 hours    ||         6 |     3       | a lot  |  2   |  1   |  10 hours    |+-----------+------+---------+------+-------------+-------------+

用户价格:

+-----------+----------+------+-------+---------+---------+---------+---------+| id        | user_id  | 1    |   2   |   3     |    4    |    5    |    6    |    +-----------+----------+------+-------+---------+---------+---------+---------+|         1 | 20       |    1 |   1   |   5     |    4    |    6    |    6    ||         2 | 22       |    2 |   1   |   2     |    3    |    5    |    1    ||         3 | 32       |    3 |   2   |   7     |    4    |    1    |    3    |+-----------+----------+------+-------+---------+---------+---------+---------+

类别包含我在商店中销售的类别。
服务是我在商店中提供的服务，它们与类别相对应，并且仅针对该类别。
价格是我向客户收取的用户价格，每个用户都有不同的价格，价格表中的列名称对应于服务 ID。

这是一个 SQLFiddle 示例数据

现在问题是，在一次扫描中我想获取一行，其中包含 user_id 22 的所有三个表中的列(user_id 仅用于价格)

+---------------+----------------+-----------------+------------------+-----------+| service_id    | category_name  | service_name    |   process_time   |   price   |  +---------------+----------------+-----------------+------------------+-----------+|  3            |  mango         | slices          |   1 hour         |    2      |+---------------+----------------+-----------------+------------------+-----------+

有什么想法吗？？？

Answer 1

尝试

SELECT s.id service_id, 
       c.name category_name, 
       s.name service_name, 
       s.process_time, 
       p.`3` price
  FROM service s JOIN category c
    ON s.category_id = c.id JOIN user_price p
    ON p.user_id = 22
 WHERE s.id = 3

输出:

| SERVICE_ID | CATEGORY_NAME | SERVICE_NAME | PROCESS_TIME | PRICE |--------------------------------------------------------------------|          3 |         mango |       slices |       1 hour |     2 |

Here is SQLFiddle demo

---

Now while it's not too late please normalize your user_price table. That will save you a lot of headache in the long run.

Your renewed schema for this table might look like

CREATE TABLE IF NOT EXISTS user_price
(
    id int(11) NOT NULL AUTO_INCREMENT, 
    user_id int(11) NOT NULL, 
    service_id int(11) NOT NULL,
    price decimal(19,2) DEFAULT '0.00',
    PRIMARY KEY (id), 
    FOREIGN KEY (service_id) REFERENCES service (id),
    UNIQUE KEY  (user_id, service_id) 
);

以及使用此表的正确查询

SELECT s.id service_id, 
       c.name category_name, 
       s.name service_name, 
       s.process_time, 
       p.price
  FROM service s JOIN category c
    ON s.category_id = c.id JOIN user_price p
    ON s.id = p.service_id 
   AND p.user_id = 22 
 WHERE s.id = 3

这是一个 SQLFiddle演示。 在此示例中，表已命名为 user_price1 以并排显示两个查询。