采取两个表，一对多，如何过滤多个表，然后连接第一个表中的所有匹配项？

Question

我提前为篇幅表示歉意，解决方案可能很微不足道，只是想尽可能提供信息。

表格

我有两个值得注意的表格：items 和 products，这是一对多的关系。一件商品可以有多种颜色和材质不同的产品。品牌是一个外部类别表，在此 select 语句中不必发挥太多作用。

因此，物品例如是特定的鞋子，例如“公园大道”鞋子。 例如，产品是梅洛抛光小牛皮。 这个品牌就是 Allen Edmonds。 总的来说，您会得到一款采用梅洛抛光小牛皮制成的 Allen Edmonds 公园大道鞋。

“显示几乎所有内容”搜索中缺少结果

有人决定创建一个手动标记，将默认颜色和材质与鞋子关联起来，这样当您搜索时，每种类型的鞋子只会显示一次，当你点击它时，你可以找到它的其他颜色和材质。没关系，但有些鞋子没有默认的材质和颜色设置。不幸的是，那些没有至少一个默认集的内容不会出现在搜索中。

当前选择语句

这是当前选择，它过滤掉没有手动设置默认值的所有内容：

SELECT DISTINCT items.ItemId
     , items.Name
     , items.BrandCategoryId
     , items.CatalogPage
     , items.GenderId
     , items.PriceRetail
     , items.PriceSell
     , items.PriceHold
     , items.Descr
     , items.FlagStatus as ItemFlagStatus
     , products.ImagetnURL
     , products.FlagDefault
     ,  products.ProductId
     , products.Code as ProductCode
     , products.Name as ProductName
     , brands.Name as BrandName 
FROM items
   , products
   , brands 
WHERE items.ItemId = products.ItemId
  AND items.BrandCode = brands.Code
  AND items.FlagStatus != 'U'
  AND products.FlagStatus != 'U' 
  AND products.FlagDefault = 'Y';

不是我选择的代码，我怀疑该语句的“DISTINCT”部分是不好的想法，但我不太清楚如何摆脱它。

不过，我现在遇到的大问题是最后一行

AND products.FlagDefault = 'Y'

过滤掉了至少没有一个手动默认设置的所有内容。

编辑：这是对查询的解释：

+----+-------------+----------+--------+-----------------------------------------------------------+---------+---------+-------------------------+-------+--------------------------------+
| id | select_type | table    | type   | possible_keys                                             | key     | key_len | ref                     | rows  | Extra                          |
+----+-------------+----------+--------+-----------------------------------------------------------+---------+---------+-------------------------+-------+--------------------------------+
|  1 | SIMPLE      | brands   | ALL    | NULL                                                      | NULL    | NULL    | NULL                    |    38 | Using temporary                |
|  1 | SIMPLE      | products | ALL    | FlagStatus,FlagStatus_2,FlagStatus_3,flagstatusanddefault | NULL    | NULL    | NULL                    | 16329 | Using where; Using join buffer |
|  1 | SIMPLE      | items    | eq_ref | PRIMARY,BrandCode,FlagStatus,FlagStatus_2,FlagStatus_3    | PRIMARY | 4       | sherman.products.ItemId |     1 | Using where                    |
+----+-------------+----------+--------+-----------------------------------------------------------+---------+---------+-------------------------+-------+--------------------------------+
3 rows in set (0.01 sec)

这是对产品、商品和品牌的描述：

mysql> describe products;
+-------------+--------------+------+-----+-------------------+-----------------------------+
| Field       | Type         | Null | Key | Default           | Extra                       |
+-------------+--------------+------+-----+-------------------+-----------------------------+
| ProductId   | int(11)      | NO   | PRI | NULL              | auto_increment              |
| ItemId      | int(11)      | YES  |     | NULL              |                             |
| Code        | varchar(15)  | YES  | MUL | NULL              |                             |
| Name        | varchar(100) | YES  |     | NULL              |                             |
| MaterialId  | int(11)      | YES  | MUL | NULL              |                             |
| PriceRetail | decimal(6,2) | YES  |     | NULL              |                             |
| PriceSell   | decimal(6,2) | YES  |     | NULL              |                             |
| PriceHold   | decimal(6,2) | YES  |     | NULL              |                             |
| Cost        | decimal(6,2) | YES  |     | NULL              |                             |
| FlagDefault | char(1)      | NO   |     | N                 |                             |
| FlagStatus  | char(1)      | YES  | MUL | NULL              |                             |
| ImagetnURL  | varchar(50)  | YES  |     | NULL              |                             |
| ImagefsURL  | varchar(50)  | YES  |     | NULL              |                             |
| ImagelsURL  | varchar(50)  | YES  |     | NULL              |                             |
| DateStatus  | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| DateCreated | timestamp    | YES  |     | NULL              |                             |
+-------------+--------------+------+-----+-------------------+-----------------------------+
16 rows in set (0.02 sec)

mysql> describe items
    -> ;
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| Field           | Type         | Null | Key | Default           | Extra                       |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| ItemId          | int(11)      | NO   | PRI | NULL              | auto_increment              |
| Code            | varchar(25)  | YES  |     | NULL              |                             |
| Name            | varchar(100) | YES  | MUL | NULL              |                             |
| BrandCode       | char(2)      | YES  | MUL | NULL              |                             |
| CatalogPage     | int(3)       | YES  |     | NULL              |                             |
| BrandCategoryId | int(11)      | YES  |     | NULL              |                             |
| TypeId          | int(11)      | YES  | MUL | NULL              |                             |
| StyleId         | int(11)      | YES  | MUL | NULL              |                             |
| GenderId        | int(11)      | YES  | MUL | NULL              |                             |
| PriceRetail     | decimal(6,2) | YES  |     | NULL              |                             |
| PriceSell       | decimal(6,2) | YES  |     | NULL              |                             |
| PriceHold       | decimal(6,2) | YES  |     | NULL              |                             |
| Cost            | decimal(6,2) | YES  |     | NULL              |                             |
| PriceNote       | longtext     | YES  |     | NULL              |                             |
| FlagTaxable     | char(1)      | YES  |     | NULL              |                             |
| FlagStatus      | char(1)      | YES  | MUL | NULL              |                             |
| FlagFeatured    | char(1)      | YES  |     | NULL              |                             |
| MaintFlagStatus | char(1)      | YES  |     | NULL              |                             |
| Descr           | longtext     | YES  |     | NULL              |                             |
| DescrNote       | longtext     | YES  |     | NULL              |                             |
| ImagetnURL      | varchar(50)  | YES  |     | NULL              |                             |
| ImagefsURL      | varchar(50)  | YES  |     | NULL              |                             |
| ImagelsURL      | varchar(50)  | YES  |     | NULL              |                             |
| DateCreated     | date         | NO   |     | 0000-00-00        |                             |
| DateStatus      | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
25 rows in set (0.00 sec)

mysql> describe brands;
+--------------+------------------+------+-----+-------------------+-----------------------------+
| Field        | Type             | Null | Key | Default           | Extra                       |
+--------------+------------------+------+-----+-------------------+-----------------------------+
| BrandId      | int(11) unsigned | NO   | PRI | NULL              | auto_increment              |
| Code         | varchar(6)       | YES  |     | NULL              |                             |
| PriceCode    | varchar(4)       | YES  |     | NULL              |                             |
| Name         | varchar(50)      | YES  |     | NULL              |                             |
| WebsiteURL   | varchar(50)      | YES  |     | NULL              |                             |
| LogoURL      | varchar(50)      | YES  |     | NULL              |                             |
| LogoTopURL   | varchar(50)      | YES  |     | NULL              |                             |
| BrandURL     | varchar(50)      | YES  |     | NULL              |                             |
| Descr        | longtext         | YES  |     | NULL              |                             |
| DescrShort   | longtext         | YES  |     | NULL              |                             |
| BeltDescr    | longtext         | YES  |     | NULL              |                             |
| ImageURL     | varchar(50)      | YES  |     | NULL              |                             |
| SaleImageURL | varchar(50)      | YES  |     | NULL              |                             |
| SaleCode     | varchar(6)       | YES  |     | NULL              |                             |
| SaleDateBeg  | date             | YES  |     | NULL              |                             |
| SaleDateEnd  | date             | YES  |     | NULL              |                             |
| FlagStatus   | char(1)          | YES  |     | NULL              |                             |
| DateStatus   | timestamp        | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| DateCreated  | timestamp        | YES  |     | NULL              |                             |
+--------------+------------------+------+-----+-------------------+-----------------------------+
19 rows in set (0.00 sec)

我正在探索的可能性

子选择，将所有内容研磨成一个 我有一个 select语句

，在一个完美的、零执行时间的世界中，它可以通过选择每个项目的第一个产品（按该 flagdefault 字段排序）来工作，例如：

  AND products.productid =
    (select productid
     from products
     where products.itemid = items.itemid
       AND products.FlagStatus != 'U'
     order by FlagDefault='Y'
            , itemid
     limit 1);

将手动切换默认值的检查替换为仅默认排序的 id，即使它没有切换，并且只获取第一个结果。

该语句逐渐停止，实际上导致站点上的其他使用将 mysql 语句置于死锁（我想是因为读取这些表使它们在其他地方不可用）。

加入以确保一个表是不同的而不是下一个表？

一种可能有效的解决方法是执行以下操作：

select distinct ItemId from products ORDER BY default

然后进一步专门获取这些 itemids 的数据，但我不知道如何在单个语句中实现这一点，不确定如何很好地连接选择不同，我希望即使一开始就使选择“不同”也不理想，因为它选择的内容比开始所需的要多然后将它们削减，但我确实没有更好的选择来确定清晰度。

建议？

一般来说，select 语句可以进行很多改进，具体来说，我确实可以使用一些关于如何过滤最具体表的结果的建议，并且仅-然后- 将上游连接到作为一对多关系中的“一”的表。

Answer 1

从 WHERE 中删除：

AND products.FlagStatus != 'U'
AND products.FlagDefault = 'Y'

添加到 FROM：

(
   (SELECT ProductId
    FROM products
    WHERE FlagStatus != 'U' AND FlagDefault = 'Y')
UNION
   (SELECT MIN(ProductId)
    FROM products
    WHERE FlagStatus != 'U'
    GROUP BY ItemId
    HAVING MAX(FlagDefault) != 'Y')
) AS defaults

添加到 WHERE：

AND defaults.ProductId = products.ProductId

我对具有 的行使用术语“非隐藏” >FlagStatus != 'U'，因为我假设这就是标志的用途。

第一个 SELECT 给出了所有默认产品的 ProductId，第二个给出了所有没有默认产品的商品的 ProductId。隐藏项目由两者过滤，因此如果默认产品已被隐藏，则会显示非默认产品。连接后，您将获得包含某些非隐藏产品的每个项目的 ProductId。

我假设 FlagDefault 只能具有值 'Y' 或 'N'。第二个查询使用 MAX(FlagDefault) 过滤掉具有默认产品的项目，这是有效的，因为 'Y' > “N”。

通过将其连接到原始查询的 products 表，而不是使用 FlagDefault 进行过滤，您应该得到与原始查询相同的结果，只不过您还为每个查询获取一行没有默认产品的项目。

我已经测试了这个查询，但我没有用你原来的查询来测试它，因为我没有任何有意义的数据（阅读：你的数据）来测试它。这个有效，所以组合也应该有效。出于同样的原因，我没有任何有关性能的实际数据 - 而且我也不是查询性能方面的专家（更像是新手）。但是，据我所知，WHERE 子句中的子查询应该对性能不利，但在 FROM 子句中它们应该没问题。所以，测试一下，我希望它足够快并且适合工作。

正如其他人提到的，如果您还没有 products.ItemId 和 BrandCode 列的索引，那么您绝对应该添加它们。您还应该考虑是否要求每个项目都有一个手动选择的默认值是否可以，或者是否可以放弃手动选择的默认值并始终使用随机的默认值。另一件需要考虑的事情是，如果您确实需要产品中没有默认值的数据 - 您是否可以在没有这些产品的图像 url、产品名称（使用商品名称？）和产品代码的情况下生存？

编辑：另一种可能性：您可以将products.FlagDefault更改为items.DefaultProductId。这样，可以更轻松地查明某个项目是否具有默认产品，并且每个项目仅强制执行一个默认产品。

Answer 2

SELECT
        items.ItemId,
        items.Name,
        items.BrandCategoryId,
        items.CatalogPage,
        items.GenderId,
        items.PriceRetail,
        items.PriceSell,
        items.PriceHold,
        items.Descr,
        items.FlagStatus as ItemFlagStatus,
        T3.ImagetnURL,
        T3.FlagDefault,
        T3.ProductId,
        T3.Code as ProductCode,
        T3.Name as ProductName,
        brands.Name as BrandName 
FROM    items INNER JOIN
        (
            SELECT DISTINCT
                T1.ItemId,
                T1.ImagetnURL,
                T1.FlagDefault
                T1.ProductId,
                T1.Code
                T1.Name,
                T1.FlagStatus
            FROM
                products AS T1 LEFT JOIN
                products AS T2 ON T1.products.ProductId = T2.products.ProductId
                    AND T2.FlagDefault = 'Y'
        ) AS T3 ON items.ItemId = T3.ItemId INNER JOIN 
        brands ON items.BrandCode = brands.Code
WHERE   items.FlagStatus != 'U'
        AND T3.FlagStatus != 'U'

Answer 3

我不确定我是否完全理解 FlagStatus 和 FlagDefault。对于没有默认值的商品，其所有产品是否都有 products.FlagDefault != 'Y' ？

如果是的话，你可以试试这个吗？它将（希望返回没有默认值的产品字段中带有 NULL 的所有项目）：

SELECT items.ItemId
     , items.Name
     , items.BrandCategoryId
     , items.CatalogPage
     , items.GenderId
     , items.PriceRetail
     , items.PriceSell
     , items.PriceHold
     , items.Descr
     , items.FlagStatus as ItemFlagStatus
     , products.ImagetnURL
     , products.FlagDefault
     , products.ProductId
     , products.Code as ProductCode
     , products.Name as ProductName
     , brands.Name as BrandName 
FROM items
  LEFT JOIN
     products 
    ON items.ItemId = products.ItemId
    AND products.FlagDefault = 'Y'
  JOIN
     brands 
    ON items.BrandCode = brands.Code
;

LEFT JOIN：

  LEFT JOIN
     products 
    ON items.ItemId = products.ItemId
    AND products.FlagDefault = 'Y'

相当于：

  LEFT JOIN
    ( SELECT *
      FROM products
       WHERE products.FlagDefault = 'Y'
    ) AS p
    ON items.ItemId = p.ItemId

所以，正如您所问，“过滤出最具体表的结果，然后仅将上游连接到...”

当使用LEFT JOIN时，如果您将您拥有的过滤条件放在 ON 子句中，或者稍后放在 WHERE 子句中的所有 JOINS 之后。

Answer 4

我不确定性能，因为您没有发布表结构和大小或解释计划，但是在您的第一个查询（具有默认产品的项目）和为每个项目获取一个产品的查询之间使用 UNION 怎么样？ ，仅适用于没有默认产品的商品？

它有点长，但是试一试 - 让我知道它是否为您提供了正确的数据以及需要多长时间......

(SELECT items.ItemId
     , items.Name
     , items.BrandCategoryId
     , items.CatalogPage
     , items.GenderId
     , items.PriceRetail
     , items.PriceSell
     , items.PriceHold
     , items.Descr
     , items.FlagStatus as ItemFlagStatus
     , products.ImagetnURL
     , products.FlagDefault
     , products.ProductId
     , products.Code as ProductCode
     , products.Name as ProductName
     , brands.Name as BrandName
FROM items
     JOIN products ON items.ItemId = products.ItemId
     JOIN brands ON items.BrandCode = brands.Code
WHERE items.FlagStatus != 'U'
  AND products.FlagStatus != 'U'
  AND products.FlagDefault = 'Y'
GROUP BY items.ItemId)
UNION
(SELECT items.ItemId
     , items.Name
     , items.BrandCategoryId
     , items.CatalogPage
     , items.GenderId
     , items.PriceRetail
     , items.PriceSell
     , items.PriceHold
     , items.Descr
     , items.FlagStatus as ItemFlagStatus
     , products.ImagetnURL
     , products.FlagDefault
     , products.ProductId
     , products.Code as ProductCode
     , products.Name as ProductName
     , brands.Name as BrandName
FROM items
     JOIN products ON items.ItemId = products.ItemId
     JOIN brands ON items.BrandCode = brands.Code
WHERE items.FlagStatus != 'U'
  AND products.FlagStatus != 'U'
  AND products.FlagDefault != 'Y'
  AND items.ItemId NOT IN 
      (SELECT DISTINCT itemId 
       FROM products 
       WHERE products.FlagDefault = 'Y')
GROUP BY items.ItemId)