值不在哪里(子查询) [英] WHERE value IS NOT IN (subquery)

问题描述

我一直在努力查询. 我有两张桌子.一种带有优惠券和发票编号.一个带有发票编号和客户名称.

I've been struggling with this query. I have two tables. One with coupons and Invoicenumbers. One with Invoicenumbers and customer names.

我需要让那些没有使用给定优惠券的客户.

I need to get the customers who have not used a given coupon.

以下是表格:

促销表:

Promotions
Invoice | Coupon
----------------
1       | couponA
2       | couponB
3       | couponB

订单表:

Orders
Invoice | Customer
------------------
1       | Jack
2       | Jack
3       | Jill

因此，杰克使用了优惠券A和B.而吉尔只使用了优惠券B.

So Jack has used coupons A and B. And Jill has only used coupon B.

如果我的查询对象是未使用优惠券A的精选客户，我应该得到Jill.

If my query were select customers who have not used coupon A, I should get Jill.

这有效，但看起来笨拙且缓慢.有更好的方法吗?

This works, but it seems clumsy and slow. Is there a better way?

SELECT Customer 
FROM Promotions INNER JOIN Orders
ON Promotions.Invoice = Orders.Invoice
WHERE Customer NOT IN(
    SELECT Customer 
    FROM Promotions INNER JOIN Orders
    ON Promotions.Invoice = Orders.Invoice
    WHERE Coupon = couponA)
GROUP BY Customer

感谢您的光临！

这是一个SQLFiddle模式 http://sqlfiddle.com/#!2/21d31/6

edit: Here's an SQLFiddle schema http://sqlfiddle.com/#!2/21d31/6

推荐答案

已更新:当我们容易做到时，我们应该使用首选使用联接来获得更好的性能. 加入与子查询

Updated: We should use prefer to use joins for better performance when its easy to do for us. Join vs. sub-query

Sql小提琴

Select distinct Customer from orders o
join 
(
  SELECT distinct Customer as changedname FROM Orders o2 
  join
  (
     Select distinct invoice from Promotions where Coupon='couponA'
  ) t3
  on o2.invoice = t3.invoice      
) t2
on o.customer != t2.changedname;

注意:我更改了t3的列名称客户，因为两个联接表必须具有不同的列名称

Note: I changed column name customer for t3 because two joined tables must have different column names

说明:

当您有大数据时，使用内部查询或子查询很昂贵.改为使用联接，让我们学习将子查询转换为联接

Using inner or sub query is expensive when you have big data. use joins instead, lets learn converting subquery to join

有了子查询，我们有了:

With Subquery We had:

Select distinct Customer from orders where customer not in 
(SELECT distinct Customer FROM Orders where invoice in
(Select distinct invoice from Promotions where Coupon='couponA'));

第一步:

Select distinct Customer from orders o
join 
(
  SELECT distinct Customer as changedname FROM Orders where invoice in
  (Select distinct invoice from Promotions where Coupon='couponA')
) t2
on o.customer != t2.changedname;

第二步:

Select distinct Customer from orders o
join 
(
  SELECT distinct Customer as changedname FROM Orders o2 where invoice 
  join
  (
     Select distinct invoice from Promotions where Coupon='couponA'
  ) t3
  on o2.invoice = t3.invoice      
) t2
on o.customer != t2.changedname;

就是这样，对于具有很多行的表，速度要快得多

And that's it, much faster for tables having numerous rows

原始答案:

使用not in.看看.

Select distinct Customer from orders where customer not in 
(SELECT distinct Customer FROM Orders where invoice in
(Select distinct invoice from Promotions where Coupon='couponA'));

编辑:我添加了独特的功能以加快查询速度

Edit I have added distinct to make query faster

SQL提琴

这篇关于值不在哪里(子查询)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！