时间戳

首页 > 解决方案 > SQL查找下订单100或更少的客户的姓名

sql - SQL查找下订单100或更少的客户的姓名

问题描述

表Orders：

(order_num: 002; 010; 003), 
(order_amt: 50.6; 99.66; 101), 
(customer_id: 06; 02; 03)

表Customer：

(customer_id: 02; 06; 03), 
(customer_name: James; Mike; Dan), 
(city: Miami; London; Berlin)

这就是给出的所有信息

SELECT customer_name
FROM Customer
WHERE customer_id IN (
  SELECT customer_id
  FROM Orders
  WHERE order_amt < 100
);

============================

SELECT c.customer_name
  FROM Customer c
 WHERE EXISTS(SELECT 1
                FROM Orders o
               WHERE c.customer_id = o.customer_id
                 AND o.order_amt < 100);

============================

SELECT c.customer_name
  FROM Orders o
  JOIN Customer c
    ON c.customer_id = o.customer_id
 GROUP BY c.customer_id, c.customer_name      
HAVING SUM(o.order_amt) < 100;

============================

SELECT DISTINCT c.customer_name
  FROM Orders o
  JOIN Customer c
    ON c.customer_id = o.customer_id
 WHERE o.order_amt < 100;

标签： sql

解决方案

不需要使用 GROUP BY，我认为 EXISTS 或 LEFT SEMI JOIN 是可以的。

WITH 
Customer(customer_id, customer_name, city) AS (
VALUES ('01', 'Mike', 'New York'), ('05', 'Zusi', 'California'), ('02', 'Green', 'London')),
Orders(order_num, order_amt, customer_id) AS (
VALUES ('001', 50.5, '05'),('009', 99.65, '01'),('002', 100, '02'))

SELECT A.customer_name
FROM Customer AS A
WHERE EXISTS (SELECT 1
  FROM Orders AS B
  WHERE A.customer_id = B.customer_id
    AND B.order_amt < 100
)

推荐阅读