mysql - 如何使用内连接查询和 where like 子句从表 Produto_precos 获取其他两个值

Question

我有两个表，Product 和 Product_prices。对于每种产品，我都有三种类型的价格(ebook(0)、impress(1) 和 combo(2))。我想加入这两个表并获取该商品的三种类型的价格。当我执行子句 WHERE rates1_.value like '%1%'

表产品

------------------------------------------------id    description        pages  title------------------------------------------------1     Harry Potter        230    harry Potter2     Lord of The rings   950    Lord of the rings3     game of thrones     980    game of thrones

表产品价格

------------------------------------------------Product_id bookType  value------------------------------------------------1          0           20.40 1          1           28.001          2           40.002          0           15.002          1           25.502          2           42.003          0           21.003          1           30.503          2           47.00

这是对 hibernate 的查询:

    select     distinct     product0_.id as id1_0_,    product0_.description as descript3_0_,     product0_.pages as pages4_0_,     product0_.title as title6_0_,     prices1_.Product_id as Product_1_1_0__,     prices1_.bookType as bookType2_1_0__,     prices1_.value as value3_1_0__     from Produto produto0_     inner join Product_prices prices1_ on product0_.id=prices1_.Product_id where lower(product0_.title) like '%1%'     or prices1_.bookType = 0 and prices1_.value like '%1%'    or prices1_.bookType = 1 and prices1_.value like '%1%'    or prices1_.bookType = 2 and prices1_.value like '%1%'

我希望获得以下结果或仅在一行中包含每种产品的三个值:

------------------------------------------------id    description        pages  title Product_id bookType  value-----------------------------------------------------------------2     Lord of The rings   950 Lord of...  2         0       15.002     Lord of The rings   950 Lord of...  2         1       25.002     Lord of The rings   950 Lord of...  2         2       42.003     game of thrones     980 game of...  3         0       21.003     game of thrones     980 game of...  3         1       30.503     game of thrones     980 game of...  3         2       47.00

但我只有以下结果:

------------------------------------------------id    description        pages  title Product_id bookType  value-----------------------------------------------------------------2     Lord of The rings   950 Lord of...  2         0       15.003     game of thrones     980 game of...  3         0       21.00

Answer 1

提醒我，以下内容未能解决问题的哪一部分...

DROP TABLE IF EXISTS product;

CREATE TABLE product
(id SERIAL PRIMARY KEY
,title VARCHAR(30) NOT NULL
,pages INT NOT NULL
);

INSERT INTO product VALUES
(1,'Harry Potter',230),
(2,'Lord of the Rings',950),
(3,'Game of Thrones',980);

DROP TABLE IF EXISTS prices;

CREATE TABLE prices
(product_id INT NOT NULL
,book_type INT NOT NULL
,price DECIMAL(5,2) NOT NULL
,PRIMARY KEY(product_id,book_type)
);

INSERT INTO prices VALUES
(1,0,20.40),
(1,1,28.00),
(1,2,40.00),
(2,0,15.00),
(2,1,25.50),
(2,2,42.00),
(3,0,21.00),
(3,1,30.50),
(3,2,47.00);

SELECT c.*
     , a.*
  FROM prices a
  JOIN prices b
    ON b.product_id = a.product_id
  JOIN product c
    ON c.id = b.product_id
 WHERE b.price LIKE '%1%';
+----+-------------------+-------+------------+-----------+-------+
| id | title             | pages | product_id | book_type | price |
+----+-------------------+-------+------------+-----------+-------+
|  2 | Lord of the Rings |   950 |          2 |         0 | 15.00 |
|  2 | Lord of the Rings |   950 |          2 |         1 | 25.50 |
|  2 | Lord of the Rings |   950 |          2 |         2 | 42.00 |
|  3 | Game of Thrones   |   980 |          3 |         0 | 21.00 |
|  3 | Game of Thrones   |   980 |          3 |         1 | 30.50 |
|  3 | Game of Thrones   |   980 |          3 |         2 | 47.00 |
+----+-------------------+-------+------------+-----------+-------+