主页 / 问题 / 880362
Accepted
Joshua
Asked:2020-09-12 17:05:02 +0000 UTC2020-09-12 17:05:02 +0000 UTC

如何编写具有多个连接的 sql 查询？

  • 772

您需要从表中选择记录productwhere product.avaibility_poition = 1for which: product.id= priority_category_product.product_id,prirority_category_product.category_id = 55并且priority_category_product.vehicle_group_id等于 group for which vehicle.model_id = 22,vehicle.brand_id = 33

有一个请求。

SELECT product.id FROM product
INNER JOIN priority_category_product
    ON product.id = priority_category_product.product_id
WHERE priority_category_product.category_id = 55
    AND priority_category_product.vehicle_id = ?????...

如何更进一步？

表结构： 

create table product
(
  id int,
  category_id int,
  name text,
  avalibility_position int
)

// Таблица связей
create table priority_category_product
(
  id int,
  category_id int,
  vehicle_group_id int,
  product_id int,
  position int
);

create table vehicle_group
(
  id int,
  name text
)

// Таблица связей
create table vehicle_group_vehicle
(
  id int,
  vehicle_group_id int,
  vehicle_id int
)

create table vehicle
(
  id int,
  brand_id int,
  model_id int
)

在这里摆弄

mysql

1 个回答

  1. Best Answer

    类似的东西，对吧？如果需要，可以在一行中写入多个连接。好像没什么难度？

    SELECT distinct p.*
FROM product as p 
INNER JOIN priority_category_product as pcp ON (
        p.id = pcp.product_id
        and pcp.category_id = 55
    )
INNER JOIN vehicle_group_vehicle as vgv ON (
        pcp.vehicle_group_id = vgv.vehicle_group_id
    )
INNER JOIN vehicle as v ON (
        v.id = vgv.vehile_id
        AND v.brand_id = 33
        AND v.model_id = 22
    )
WHERE 
    p.avaibility_poition = 1

    该表vehicle_group不包含在查询中。

    • 1

相关问题