代码之家 › 专栏 › 技术社区 › SRack

如何选择所有合并的记录都不符合条件的记录

relational-division sql mysql

1

SRack · 技术社区 · 5 年前

我使用以下表格进行了设置(使用MySQL):

orders
order_items ,其中一个来自:
products 桌子

我编写了一个查询来选择他们的 产品 type :

SELECT orders.* FROM orders 
INNER JOIN order_items ON order_items.order_id = orders.id   
INNER JOIN products ON products.id = order_items.product_id     
WHERE products.type = 'FooProduct'
AND (
  NOT EXISTS (
    SELECT null
    FROM products
    INNER JOIN order_items ON order_items.product_id = products.id
    WHERE order_items.order_id = orders.id
    AND products.type != 'FooProduct'
  )
 )

FooProduct s、再次得到所有人的命令 BarProduct S

食品 s、或完全 棒产品 s(也称为这两种产品的混合,或其他产品类型)。

全部的 食品 棒产品 .

下面是一个小示例数据,我想从中返回带有ID 3和4的订单:

- orders
id
 1
 2
 3
 4

-- order_items

id order_id product_id
 1        1          1
 2        1          1
 3        2          2
 4        2          2
 5        3          3
 6        3          4
 7        4          1
 8        4          2

-- products
id type
 1 'FooProduct'
 2 'BarProduct'
 3 'OtherProduct'
 4 'YetAnotherProduct'

我尝试过这样做,把它作为一个潜台词,用下面的内容代替现有的 AND

NOT HAVING COUNT(order_items.*) = (
  SELECT null
        FROM products
        INNER JOIN order_items ON  order_items.product_id = products.id
        WHERE order_items.order_id = orders.id
        AND products.type IN ('FooProduct', 'BarProduct')
)

5 回复 | 直到 5 年前

1

Madhur Bhaiya 5 年前

您可以使用 Having

products.type IN ('FooProduct', 'BarProduct') 如果一个产品类型不是其中任何一个,则返回0。我们可以用 Sum() 函数,用于进一步过滤。

SELECT orders.order_id 
FROM orders 
INNER JOIN order_items ON order_items.order_id = orders.id   
INNER JOIN products ON products.id = order_items.product_id 
GROUP BY orders.order_id 
HAVING SUM(products.type IN ('FooProduct', 'BarProduct')) < COUNT(*)

在这种情况下,您要寻找的订单只有 FooProduct 类型,则可以使用以下内容:

SELECT orders.order_id 
FROM orders 
INNER JOIN order_items ON order_items.order_id = orders.id   
INNER JOIN products ON products.id = order_items.product_id 
GROUP BY orders.order_id 
HAVING SUM(products.type <> 'FooProduct') = 0

另一种可能的方法是:

SELECT orders.order_id 
FROM orders 
INNER JOIN order_items ON order_items.order_id = orders.id   
INNER JOIN products ON products.id = order_items.product_id 
GROUP BY orders.order_id 
HAVING SUM(products.type = 'FooProduct') = COUNT(*)

2

1

Gordon Linoff 5 年前

可以使用聚合和 having 条款:

SELECT o.*
FROM orders o INNER JOIN
     order_items oi
     ON oi.order_id = o.id INNER JOIN
     products p
     ON p.id = oi.product_id   
GROUP BY o.id  -- OK assuming `id` is the primary key
HAVING SUM(p.type NOT IN ('FooProduct', 'BarProduct')) > 0;  -- at least one other product

HAVING SUM(p.type = 'FooProduct') < COUNT(*) AND
       SUM(p.type = 'BarProduct') < COUNT(*)

3

1

kiks73 5 年前

这是一个基本的解决方案,虽然效率不高,但很简单:

SELECT * FROM orders WHERE id NOT IN (
    SELECT orders.id FROM orders 
    INNER JOIN order_items ON order_items.order_id = orders.id   
    INNER JOIN products ON products.id = order_items.product_id     
    WHERE products.type = 'FooProduct'
    AND (
      NOT EXISTS (
        SELECT null
        FROM products
        INNER JOIN order_items ON order_items.product_id = products.id
        WHERE order_items.order_id = orders.id
        AND products.type != 'FooProduct'
      )
 )
) AND id NOT IN (
    SELECT orders.id FROM orders 
    INNER JOIN order_items ON order_items.order_id = orders.id   
    INNER JOIN products ON products.id = order_items.product_id     
    WHERE products.type = 'BarProduct'
    AND (
      NOT EXISTS (
        SELECT null
        FROM products
        INNER JOIN order_items ON order_items.product_id = products.id
        WHERE order_items.order_id = orders.id
        AND products.type != 'BarProduct'
      )
 )
)

4

1

Robert Lujo 5 年前

我建议在joined subselect中使用count(distinct),如下所示:

SELECT orders.*
FROM orders 
inner join (
    SELECT orderid, max(products.type) as products_type
    FROM order_items
    INNER JOIN products ON products.id = order_items.product_id
    GROUP BY orderid
    -- distinct count of different products = 1 
    --    -> all order items are for the same product type
    HAVING COUNT(distinct products.type ) = 1 
    -- alternative is:
    -- min(products.type )=max(products.type )
) as tmp on tmp.orderid=orders.orderid 
WHERE 1=1
-- if you want only single type product orders for some specific product
and tmp.products_type = 'FooProduct'

5

1

Salman Arshad 5 年前

找到订单的一个解决方案 所有产品都属于给定类型 这是:

SELECT *
FROM orders
INNER JOIN order_items ON order_items.order_id = orders.id
INNER JOIN products ON products.id = order_items.product_id
WHERE orders.id IN (
    SELECT order_items.order_id
    FROM order_items
    INNER JOIN products ON products.id = order_items.product_id
    GROUP BY order_items.order_id
    HAVING COUNT(CASE WHEN products.type = 'FooProduct' THEN 1 END) = COUNT(*)
)

稍微调整一下上面的命令这是:

HAVING COUNT(CASE WHEN products.type IN ('FooProduct', 'BarProduct') THEN 1 END) = COUNT(*)

找到所有的命令在哪里这是:

HAVING COUNT(CASE WHEN products.type IN ('FooProduct', 'BarProduct') THEN 1 END) = COUNT(*)
AND    COUNT(DISTINCT products.type) = 2

DB Fiddle with tests