mysql - 拿两个表，一对多，我怎样才能过滤掉很多表，然后加入 1 表中的所有匹配项？

Question

我提前为篇幅道歉，解决方案可能很简单，只是想尽可能提供信息。

表格

我有两个注释表:items 和 products，它们是一对多的关系。一件商品可以有多种产品，这些产品在颜色和 Material 上有所不同。 Brand 是一个外部类别表，在此 select 语句中不需要扮演太多角色。

所以一个项目是，例如，一双特定的鞋子，例如“公园大道”鞋。例如，一种产品是 merlot 抛光小牛皮。品牌就是艾伦·埃德蒙兹 (A​​llen Edmonds)。总的来说，你会得到一双 Allen Edmonds 公园大道鞋，采用梅洛抛光小牛皮制成。

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

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

当前选择语句

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

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

然后进一步专门获取这些 itemid 的数据，但我不确定如何在单个语句中实现这一点，不确定如何很好地加入 select distincts，我希望即使进行该选择“distinct”首先并不理想，因为它选择的比开始时需要的多，然后再将它们削减，但我没有更好的选择来确定清晰度，真的。

建议？

一般来说，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。这样可以更轻松地查明某件商品是否具有默认产品，并且每件商品仅强制执行一个默认产品。