MySQL带有加入语句

Question

我有以下表

1）折扣

+--------------------------------------------+
| ID   discount_description   discount_type  |
+--------------------------------------------+
| 17   20% off                PERCENT        |
| 19   Citric                 ABSOLUTE       |
+--------------------------------------------+

表2-包含产品

+------+--------------+------------------+
|  ID  | discount_id  |    product_id    |
+------+--------------+------------------+
|   2  |          17  |         52238403 |
|   3  |          17  |         52238409 |
|   4  |          19  |         52238408 |
+------+--------------+------------------+

表3-排除产品

+---------------------------------+---------------+
| ID   discount_id                |  product_id   |
+---------------------------------+---------------+
| 2    17                         |      52238411 |
| 3    17                         |      52238408 |
+---------------------------------+---------------+

我需要查询才能根据产品ID从包含的表和排除表中获取所有折扣。它还应包括折扣的行，该折扣不存在于包含/排除表中的产品中。

以下是一个基于产品522238408,52238403的样本折扣。

    选择        discounts.id as ID1，        discount_products.product_id as p1，        dubl_discount_products.product_id as p2    从折扣中    左加入1 = 1        和discounts.id = discount_products.discount_id    左加入`dubl_discount_products` on 1 = 1        and discounts.id = dubl_discount_products.discount_id    werry_products.product_id in（52238408,522238403）        和dubl_discount_products.product_id不在（522238408,52238403）中

查询只是一个基本版本，我很确定它是不正确的。但基本上我想检查两个表格的产品ID。

我想获得折扣19，因为排除产品的输出为52238408，因此应忽略17。但是它给出了17。不知道我缺少什么，任何帮助都非常感谢。

谢谢

Answer 1

不确定是否想要此内容，您不包括预期输出。

创建表/插入数据

CREATE TABLE discounts
    (`ID` INT, `discount_description` VARCHAR(7), `discount_type` VARCHAR(8))
;
INSERT INTO discounts
    (`ID`, `discount_description`, `discount_type`)
VALUES
    (17, '20% off', 'PERCENT'),
    (19, 'Citric', 'ABSOLUTE')
;
CREATE TABLE discount_products
    (`ID` INT, `discount_id` INT, `product_id` INT)
;
INSERT INTO discount_products
    (`ID`, `discount_id`, `product_id`)
VALUES
    (2, 17, 52238403),
    (3, 17, 52238409),
    (4, 19, 52238408)
;

CREATE TABLE exclude_discount_products
    (`ID` INT, `discount_id` INT, `product_id` INT)
;
INSERT INTO exclude_discount_products
    (`ID`, `discount_id`, `product_id`)
VALUES
    (2, 17, 52238411),
    (3, 17, 52238408)
;

查询

SELECT 
   discounts.id AS ID1
 , product_id AS p1
 , ( 
      # SELECT matching product_id FROM exclude_discount_products based on product_id (checks excludes)
      SELECT
        product_id
      FROM
        exclude_discount_products
      WHERE
        product_id = discount_products_NOT_IN_exclude_discount_products.product_id
    )
     AS p2
FROM ( 
  # SELECT all discount_products that dont have the same discount_id, product_id as in exclude_discount_products 
  SELECT 
     *
  FROM 
   discount_products
  WHERE (
      discount_id
    , product_id
  ) 
  NOT IN (
    SELECT 
        discount_id
      , product_id
    FROM 
      exclude_discount_products  
  )  
)
 AS 
  discount_products_NOT_IN_exclude_discount_products
INNER JOIN 
 discounts
ON
  discount_products_NOT_IN_exclude_discount_products.discount_id = discounts.id   
WHERE
 product_id IN(
    52238408
  , 52238403 
)

结果

   ID1        p1        p2  
------  --------  ----------
    17  52238403      (NULL)
    19  52238408    52238408