WHERE 值不在(子查询)

2022-01-23 00:00:00 subquery mysql

我一直在努力解决这个问题.我有两张桌子.一张带有优惠券和发票号码.一种带有发票编号和客户姓名.

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

所以 Jack 使用了优惠券 A 和 B.而 Jill 只使用了优惠券 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 Fiddle

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 更改了列名 customer，因为两个连接表必须具有不同的列名

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

使用 子查询 我们有:

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

原答案:

使用不在.看看吧.

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'));

编辑我添加了 distinct 以加快查询速度

Edit I have added distinct to make query faster

SQL Fiddle

相关文章