php - 连接查询 - 避免 "SET SQL_BIG_SELECTS=1"和超时

Question

我有两个 mysql 表:

表 1(80K+ 产品):

_products

product_id         status          ean          category

表 2:(700K+ 产品属性，与表一的“ean”列相匹配)

 _productinformation

 info_id        ean       info_group         info_type         info_value

我的挑战:

我想从“产品”表中选择 30 个产品(基于其状态和类别)，并将 ean 列上的这些产品与另一个表进行匹配。接下来，我想过滤掉“productinformation”表中的匹配项，这些匹配项的 ean 与“products”中选择的匹配项相同，并且“info_type”列与特定值匹配。下面的 SQL 几乎满足了我的要求，除了两个问题:

1. 需要使用 SQL BIG QUERIES(这使得查询速度极慢，最多 5 分钟)
2. 该查询不仅返回一行包含 ean 和 info_type 的选定行，而且返回 40 多行，每个 ean 包含 40 多次完全相同的信息(我认为这是具有该特定 ean 代码的总行数在表“产品信息”中)。

我构建的查询:

SELECT    _products.ean
        , _products.status
        , product_brand.info_value as product_brand
        , product_type.info_value as product_type
        , product_price.info_value as product_price
    FROM _products
    LEFT JOIN _productinformation ON _products.ean = _productinformation.ean
    LEFT JOIN _productinformation as product_brand ON _products.ean = product_brand.ean
    LEFT JOIN _productinformation as product_type ON _products.ean = product_type.ean
    LEFT JOIN _productinformation as product_price ON _products.ean = product_price.ean
    WHERE product_brand.info_type = 'brand'
        AND product_type.info_type = 'type'
        AND product_price.info_type = 'price'
        AND  _products.category='1'

这会返回类似的内容(具有相同产品的 40 多行):

     ean            status     product_brand     product_type     product_price     0123456789     1          brand1            type1            0.00     0123456789     1          brand1            type1            0.00     0123456789     1          brand1            type1            0.00     0123456789     1          brand1            type1            0.00     0123456789     1          brand1            type1            0.00     etc.

但是，我想看看 30 种不同的产品:

     ean            status     product_brand     product_type     product_price     0123456789     1          brand1            type1            0.00     9876543210     3          brand6            type3            15.00     6548214656     45         brand34           type1            99.00      245511411241   4          brand324          type1            98.00     etc.

有人可以告诉我我正在寻找的查询是否可能吗？它应该是什么样子？我已经尝试了 100 多个不同的查询(又是 3 天......)，但都失败了。上面的查询最为接近。希望有人能帮助我!谢谢!

Answer 1

我将使用下表作为示例来解释这个答案。它们与您的类似，只是我遗漏了一些似乎在此查询中不起作用的字段。

#Products
+ ---------- + ------ + ------------ +
| Product_Id | Status | EAN          |
+ ---------- + ------ + ------------ +
| 1          | 1      | 0123456789   |
| 2          | 3      | 9876543210   |
| 3          | 45     | 6548214656   |
| 4          | 4      | 245511411241 |
+ ---------- + ------ + ------------ +

#Info
+ ------- + ------------ + --------- + ---------- +
| Info_Id | EAN          | Info_Type | Info_Value |
+ ------- + ------------ + --------- + ---------- +
| 1       | 0123456789   | brand     | brand1     |
| 2       | 0123456789   | type      | type1      |
| 3       | 0123456789   | price     | 0.00       |
| 4       | 9876543210   | brand     | brand6     |
| 5       | 9876543210   | type      | type3      |
| 6       | 9876543210   | price     | 15.00      |
| 7       | 6548214656   | brand     | brand34    |
| 8       | 6548214656   | type      | type1      |
| 9       | 6548214656   | price     | 99.00      |
| 10      | 245511411241 | brand     | brand324   |
| 11      | 245511411241 | type      | type1      |
| 12      | 245511411241 | price     | 98.00      |
+ ------- + ------------ + --------- + ---------- +

现在考虑以下查询

select    i.EAN
        , p.ProductStatus
        , case info_type when 'brand' then info_value end as brand
        , case info_type when 'type' then info_value end as [type]
        , case info_type when 'price' then info_value end as price
    from #Info i
    inner join #Products p on p.ean = i.ean

它会生成一个类似的表格

+ ------------ + ------ + -------- + ----- + ----- +
| EAN          | status | brand    | type  | price |
+ ------------ + ------ + -------- + ----- + ----- +
| 0123456789   | 1      | brand1   | NULL  | NULL  |
| 0123456789   | 1      | NULL     | type1 | NULL  |
| 0123456789   | 1      | NULL     | NULL  | 0.00  |
| 9876543210   | 3      | brand6   | NULL  | NULL  |
| 9876543210   | 3      | NULL     | type3 | NULL  |
| 9876543210   | 3      | NULL     | NULL  | 15.00 |
| 6548214656   | 45     | brand34  | NULL  | NULL  |
| 6548214656   | 45     | NULL     | type1 | NULL  |
| 6548214656   | 45     | NULL     | NULL  | 99.00 | 
| 245511411241 | 4      | brand324 | NULL  | NULL  |
| 245511411241 | 4      | NULL     | type1 | NULL  |
| 245511411241 | 4      | NULL     | NULL  | 98.00 |
+ ------------ + ------ + -------- + ----- + ----- +

如您所见，我仅在选择部分中使用带有某些 case 语句的单个联接来创建我想要的列。与使用多个联接相比，这具有巨大的优势，因为没有记录重复。

最后，一个简单的聚合将给我们我们想要的东西

select    i.EAN
        , p.ProductStatus
        , max(case info_type when 'brand' then info_value end) as brand
        , max(case info_type when 'type' then info_value end) as [type]
        , max(case info_type when 'price' then info_value end) as price
    from #Info i
    inner join #Products p on p.ean = i.ean
    group by i.EAN, p.ProductStatus

+ ------------ + ------ + -------- + ----- + ----- +
| EAN          | Status | brand    | type  | price |     
+ ------------ + ------ + -------- + ----- + ----- +
| 0123456789   | 1      | brand1   | type1 | 0.00  |
| 245511411241 | 4      | brand324 | type1 | 98.00 |
| 6548214656   | 45     | brand34  | type1 | 99.00 |
| 9876543210   | 3      | brand6   | type3 | 15.00 |
+ ------------ + ------ + -------- + ----- + ----- +

然后，您可以使用 CTE 来过滤该表的特定字段，如下所示(仅限 SQL Server):

; with
    CTE as (
        select    i.EAN
                , p.ProductStatus
                , max(case info_type when 'brand' then info_value end) as brand
                , max(case info_type when 'type' then info_value end) as [type]
                , max(case info_type when 'price' then info_value end) as price
            from #Info i
            inner join #Products p on p.ean = i.ean
            group by i.EAN, p.ProductStatus
    )
select *
    from CTE
    where price >= 99 -- or whatever other filter you want

希望这有帮助!