我想在同一张表上进行内部联接，即 product_attributes 我想要 prod_value = gender 和 prod_attr=Male 和 maingroup = Pants。这意味着，我想要所有性别为男性且属于 Pants 的产品。我什至想打印 prodgroup 并加入我需要 prod_name 的 product_master 表。我怎样才能做到这一点？

表 1:产品属性

+----+------------+-----------+------------+
| id | prod_style | prod_attr | prod_value |
+----+------------+-----------+------------+
|  1 | 0010       | gender    |       Male |
|  2 | 0010       | maingroup |      Pants |
|  3 | 0010       | prodgroup |  Pants_Abc |
|  4 | 0010       | Blue      |      color |
|  5 | 0011       | gender    |       Male |
|  6 | 0011       | maingroup |      Pants |
|  7 | 0011       | prodgroup |  Pants_Pqr |
|  8 | 0012       | gender    |     Female |
|  9 | 0012       | maingroup |      Pants |
| 10 | 0012       | prodgroup |      Pants |
| 11 | 0013       | gender    |     Female |
| 12 | 0013       | maingroup |    Jackets |
+----+------------+-----------+------------+

表 2:Product_master

+----+------------+-----------+
| id | prod_style | prod_name | 
+----+------------+-----------+
|  1 | 0010       | ABC       |
|  2 | 0011       | PQR       |
|  3 | 0012       | XYZ       |
|  4 | 0013       | LMN       |
+----+------------+-----------+

我试过这个解决方案:

select 
    * 
from 
    product_master pm 
    INNER JOIN (select 
                    * 
                from 
                    product_attributes 
                where 
                    prod_value='prodgroup' 
                        and prod_style in(select 
                                                prod_style 
                                          from 
                                                product_attributes 
                                           where 
                                                prod_attr ='pants' 
                                                and prod_value='mainGroup' 
                                                and prod_style in(select 
                                                                        prod_style 
                                                                   from 
                                                                        product_attributes 
                                                                   where 
                                                                        prod_attr='Male'
                                                                  )
                                           )
                ) p ON pm.prod_style = p.prod_style  
ORDER By 
    prod_name

通过我的解决方案，我得到了输出，但不知道它是否是正确的查询编写方式。

使用上述解决方案的输出:

+-----------+------------+-----------+-------------+-----------+-----------+
| id        | prod_style | prod_name | prod_style  | prod_attr | prod_value|
+-----------+------------+-----------+-------------+-----------+-----------+
|  1        | 0010       | ABC       |0010         |Pants_Abc  |prodgroup  |
|  2        | 0011       | PQR       |0011         |Pants_Pqr  |prodgroup  |
|  3        | 0012       | XYZ       |0012         |Pants      |prodgroup  |
|  4        | 0013       | LMN       |0013         |skinny     |prodgroup  |
+-----------+------------+-----------+-------------------------------------+

最佳答案

我构建了这个查询:

DECLARE @Product_attributes TABLE
(
    id INT
    , prod_style VARCHAR(10)
    , prod_attr VARCHAR(10)
    , prod_value VARCHAR(10)
);

DECLARE @Product_master TABLE
(
    ID INT
    , prod_style VARCHAR(10)
    , prod_name VARCHAR(10)
);

INSERT INTO @Product_attributes (id, prod_style, prod_attr, prod_value)
VALUES (1, '0010', 'gender','Male')
    , (2, '0010', 'maingroup','Pants')
    , (3, '0010', 'prodgroup', 'Pants_Abc')
    , (4, '0010', 'Blue', 'color')
    , (5, '0011', 'gender', 'Male')
    , (6, '0011', 'maingroup', 'Pants')
    , (7, '0011', 'prodgroup', 'Pants_Pqr')
    , (8, '0012', 'gender', 'Female')
    , (9, '0012', 'maingroup', 'Pants')
    , (10, '0012', 'prodgroup', 'Pants')
    , (11, '0013', 'gender', 'Female')
    , (12, '0013', 'maingroup', 'Jackets');

INSERT INTO @Product_master (id, prod_style, prod_name)
VALUES (1, '0010', 'ABC')
    , (2, '0011', 'PQR')
    , (3, '0012', 'XYZ')
    , (4, '0013', 'LMN');

SELECT PA1.id, PA1.prod_style, PM.prod_name, PA1.prod_attr, PA1.prod_value
FROM @Product_attributes AS PA1
INNER JOIN @Product_master AS PM
    ON PM.prod_style = PA1.prod_style
WHERE PA1.prod_attr = 'mainGroup'
    AND PA1.prod_value = 'Pants'
    AND EXISTS (
        SELECT 1
        FROM @Product_attributes AS PA2
        WHERE PA2.prod_style = PA1.prod_style
            AND PA2.prod_attr = 'gender'
            AND PA2.prod_value = 'Male'
        );

它会检查您的产品(基于 prod_style)是否适合男性并且是裤子。必须满足这两个条件，但它的输出与您的输出不同，即:

╔════╦════════════╦═══════════╦═══════════╦════════════╗
║ id ║ prod_style ║ prod_name ║ prod_attr ║ prod_value ║
╠════╬════════════╬═══════════╬═══════════╬════════════╣
║  2 ║       0010 ║ ABC       ║ maingroup ║ Pants      ║
║  6 ║       0011 ║ PQR       ║ maingroup ║ Pants      ║
╚════╩════════════╩═══════════╩═══════════╩════════════╝

我在你的逻辑中丢失了什么吗？请告诉我。

关于mysql - 如何在同一张表上进行多次连接，然后再与另一张表连接？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/33141305/