Oracle LeetCode 高频 SQL 50 题（进阶版）

httpsindex.php/tags-41973.html" >://leetcode.cn/studyplan/sql-premium-50/

一、查询

1821. 寻找今年具有正收入的客户

select    customer_idfrom Customerswhere year = 2021group by customer_idhaving sum(revenue) > 0

183. 从不订购的客户

select     c.name as Customersfrom Customers cleft join Orders o on c.id = o.customerIdwhere o.id is null

1873. 计算特殊奖金

select    employee_id    ,case when mod(employee_id,2) = 1 and substr(name,1,1) != 'M' then salary        else 0    end as bonusfrom Employeesorder by employee_id

1398. 购买了产品 A 和产品 B 却没有购买产品 C 的顾客

select    customer_id,customer_namefrom Customerswhere customer_id in (    select        customer_id    from Orders    where product_name in ('A','B')    and customer_id not in (select customer_id from Orders where product_name = 'C')    group by customer_id    having count(distinct product_name) = 2)order by customer_id

1112. 每位学生的最高成绩

select  student_id,course_id,grade from (select    e.*    ,rank() over(partition by student_id order by grade desc,course_id) as rk from Enrollments e ) where rk = 1order by student_id

二、连接

175. 组合两个表

select    firstName,lastName,city,statefrom Person p left join Address a on p.PersonId = a.PersonId

1607. 没有卖出的卖家

select seller_namefrom Sellerwhere seller_id not in (select     seller_idfrom Orderswhere to_char(sale_date,'yyyy') = '2020')order by seller_name

1407. 排名靠前的旅行者

select    name,nvl( sum(distance) ,0) as travelled_distancefrom Users u left join Rides r on u.id = r.user_idgroup by name,u.idorder by travelled_distance desc,name 

607. 销售员

select    namefrom SalesPersonwhere sales_id not in (    select        sales_id    from Orders o    left join Company c on o.com_id = c.com_id    where c.name = 'RED')

1440. 计算布尔表达式的值

select e.*,case when operator = '>' and v1.value > v2.value then 'true'when operator = '