sql - SQL 选择具有匹配所有 WHERE 子句的多个连接属性的项目

Question

我目前正在研究库存系统。当然，我们需要一个搜索功能，它可以查找具有多个属性的项目，其中所有搜索项在单个项目中匹配（但可能不在单个属性上）。

项目包含多个属性（由每个属性上的项目 ID 连接）并且属性包含值（参见末尾的表格）。我也无法更改数据库。

目前，我尝试过：

SELECT 
    i0_.itm_id AS itm_id_0,
    i0_.itm_inventory_num AS itm_inventory_num_1,
    i0_.itm_datetime AS itm_datetime_2,
    i0_.itm_locked_by AS itm_locked_by_3,
    i0_.stp_id AS stp_id_5
FROM item i0_
INNER JOIN property p1_
    ON i0_.itm_id = p1_.itm_id
WHERE
    p1_.par_value LIKE '%hP Dc5800%' AND
    p1_.par_value LIKE '%coopsco%'
GROUP BY
    i0_.itm_id,
    i0_.itm_inventory_num,
    i0_.itm_datetime,
    i0_.itm_locked_by,
    i0_.stp_id

但我知道两者AND之间的LIKE做法是不正确的，因为这意味着属性需要匹配两个关键字。如果我使用OR，则只需要匹配一个属性即可返回该项目，这对于搜索功能没有意义。

在我之前尝试过的人将所有属性值存储在每个项目的“关键字”字段中，但我真的不喜欢这样，因为在更新属性时，您还必须更新关键字。当信息已经在属性中可用时，它也没有任何意义。

那么，我该怎么做呢？提醒一下，我需要获取具有与所有搜索关键字匹配的属性的项目，但不是一个参数中的所有关键字。

Item
----------
itm_id INT
itm_datetime DATETIME
stp_id INT
itm_locked_by NVARCHAR(15)
itm_deleted_at DATETIME
itm_inventory_num NVARCHAR(12)


Property
----------
par_id INT
itm_id INT
ptl_id INT
par_value NVARCHAR(200)
par_deleted_at DATETIME

标签： sqlsql-server

Answer 1

也许您需要加入property两次：

SELECT DISTINCT
    i0_.itm_id AS itm_id_0,
    i0_.itm_inventory_num AS itm_inventory_num_1,
    i0_.itm_datetime AS itm_datetime_2,
    i0_.itm_locked_by AS itm_locked_by_3,
    i0_.stp_id AS stp_id_5
FROM item i0_
INNER JOIN property p1_ ON i0_.itm_id = p1_.itm_id AND p1_.par_value LIKE '%hP Dc5800%'
INNER JOIN property p2_ ON i0_.itm_id = p2_.itm_id AND p2_.par_value LIKE '%coopsco%'

我使用DISTINCT而不是GROUP BY因为在这种情况下不需要聚合。

或与EXISTS：

SELECT 
    i0_.itm_id AS itm_id_0,
    i0_.itm_inventory_num AS itm_inventory_num_1,
    i0_.itm_datetime AS itm_datetime_2,
    i0_.itm_locked_by AS itm_locked_by_3,
    i0_.stp_id AS stp_id_5
FROM item i0_
WHERE
  EXISTS (SELECT 1 FROM property p WHERE i0_.itm_id = p.itm_id AND p.par_value LIKE '%hP Dc5800%')
  AND 
  EXISTS (SELECT 1 FROM property p WHERE i0_.itm_id = p.itm_id AND p.par_value LIKE '%coopsco%')