针对8+表的特定汽车行业SQL查询(mysql)

Question

问题

我需要在很多表中搜索，但我无法让查询正常工作。

信息设置

在汽车行业工作。它们有MTOC (型号、类型、选项和颜色)的概念。这四个项目结合在一起创建一个产品类型。然后，产品类型用于由VIN (车辆标识号)标识的产品的多个实例。

ER图

我以下列方式构造了这些数据：

​

​

输出

我试图在大多数这些表中编写一个搜索查询，以便用户可以查看按Model分组的产品列表(MTOC)。输出应该如下所示：

+----------|--------------+
| Model A  | MTOC Z       +
+----------|--------------+
| Model A  | MTOC Y       +
+----------|--------------+
| Model A  | MTOC X       +
+----------|--------------+
| Model B  | MTOC J       +
+----------|--------------+
| Model B  | MTOC K       +
+----------|--------------+
| Model C  | MTOC N       +
+----------|--------------+

Considerations

我希望能够通过所有领域，包括VIN搜索，但有数百万的VIN记录。最优方法是最优的。

还请注意，它是一个搜索字符串(一个输入字段)，以搜索所有表寻找一个命中。

示例

1. 用户可以输入“火刃”并按“搜索”。
2. 然后，用户可以键入JZ2SD03UGHK并在新搜索中按下search。

对于上下文，每个表中的示例数据如下：

Model - CBR600RA
Nickname - Fireblade
Type - 3U
Option - 2
Colour - R344
Colour Description - Candy Apple Red
mtoc_name - CBR600RA3R1
VIN - JZ2SD03UGHK000999

我的尝试没有能够跨所有表进行搜索，尽管它适用于某些表，而不是Item (VIN)。我可以根据要求上传。

如果没有为此设置数据结构，请说明需要更改什么才能使其与SQL一起工作。

任何帮助都是非常感谢的。

我的尝试加上Stackoverflow的帮助

下面的解决方案越来越接近，但并不完全是这样。

SELECT
    model.model_name,
    product.mtoc_name
FROM
  mydb.item
  LEFT JOIN
  (
    mydb.product_item
    LEFT JOIN
    (
      mydb.product
      LEFT JOIN
      (
        mydb.product_mtoc
        LEFT JOIN
        (
          mydb.model
        )
        ON
          product_mtoc.model_id=model.model_id
        LEFT JOIN
        (
          mydb.type
        )
        ON
          product_mtoc.type_id=type.type_id
        LEFT JOIN
        (
          mydb.option
        )
        ON
          product_mtoc.option_id=option.option_id
        LEFT JOIN
        (
          mydb.colour
        )
        ON
          product_mtoc.colour_id=colour.colour_id
      )
      ON
        product.product_id=product_mtoc.product_id
    )
    ON
      product_item.product_id=product.product_id
  )
  ON
    item.item_id=product_item.item_id
  WHERE
    exists ( select * from mydb.product_item left join mydb.item on item.cbu_id = product_item.cbu_id where product_item.product_id = product.product_id and item.vin like '%JZ2SD03UGHK%' )
    or model.model_name like '%JZ2SD03UGHK%'
    or type.type_name like '%JZ2SD03UGHK%'
  GROUP BY model.model_name

不幸的是，这将返回Model和MTOC，但只返回一个Model/MTOC组合(多次)。查询需要很长时间才能运行。

如果我删除where子句的第二行和后续行(或model.model_name类似于‘%.(等等)它将为VIN工作。我需要把我在这里签入的所有字段加起来。

Answer 1

我不知道mysql，但也许这会有所帮助。在MSSQL中，我将创建一个以搜索条件为参数的存储过程，然后存储过程如下所示：

select whatever_columns
from product_mtoc pm
join Type t on t.type_id = pm.type_id
join colour c on c.colour_id = pm.colour_id
join product p on p.product_id = pm.product_id
.
.
.etc.
where 
(colour_name like '%' + @ColourName + '%' or @ColourName is null)
and
(colour_description like '%' + @ColourDesc + '%' or @ColourDesc is null)
and
(@vin is null or exists(select * from product_item pi join item i ont i.item_id = pi.otem_id where pi.prodcut_id = p.product_id and item.vin like '%' + @vin + '%' ))
and
.
.
.etc.
group by selected_columns_not_aggregated

您可能需要使用“上”或“低”函数，以确保您的搜索不区分大小写，如果您愿意的话。