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

餐桌产品

------------------------------------------------
ID说明页面标题
------------------------------------------------
1哈利·波特230哈利·波特
2指环王950指环王
3宝座游戏980宝座游戏


表Product_prices

------------------------------------------------
product_id bookType值
------------------------------------------------
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


这对休眠的查询:


    选择
    不同
    product0_.id为id1_0_,
    product0_.description为descript3_0_,
    product0_.pages作为pages4_0_,
    product0_.title作为title6_0_,
    将price1_.Product_id设为Product_1_1_0__,
    price1_.bookType作为bookType2_1_0__,
    将prices1_.value作为value3_1_0__
    来自Produto produto0_
    内部连接product0_.id = prices1_.Product_id上的product_pricesprice1_,其中较低的(product0_.title)如'%1%'
    或prices1_.bookType = 0,并且prices1_.value类似于“%1%”
    或prices1_.bookType = 1,并且prices1_.value类似于“%1%”
    或prices1_.bookType = 2,并且prices1_.value类似于“%1%”



我想获得以下结果,或者只将每一产品的三个值都包含一行:

------------------------------------------------
id描述页面标题Product_id bookType值
-------------------------------------------------- ---------------
2指环王950指环王2 0 15.00
2指环王950指环王2 1 25.00
2指环王950指环王2 2 42.00
3宝座游戏980宝座游戏3 0 21.00
3宝座游戏980宝座游戏3 1 30.50
3宝座游戏980宝座游戏3 2 47.00


但我只有以下结果:

------------------------------------------------
id描述页面标题Product_id bookType值
-------------------------------------------------- ---------------
2指环王950指环王2 0 15.00
3宝座游戏980宝座游戏3 0 21.00

最佳答案

提醒我,以下问题不能解决问题的哪一部分...

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 |
+----+-------------------+-------+------------+-----------+-------+

关于mysql - 如何使用内部联接查询和where like子句从表Produto_precos中获取其他两个值,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/54271041/

10-10 16:00