leetcode-sql-刷题汇总(补充ing)

leetcode-sql-刷题汇总

607. 销售员

https://leetcode-cn.com/problems/sales-person/

# 思路1: not in 
select name 
from 
SalesPerson
where name not in 
(select s.name
from 
Company as c
join 
Orders as o 
on c.com_id = o.com_id
join 
SalesPerson as s 
on o.sales_id = s.sales_id
where c.name = 'RED')


# 思路2: 不需要找到人名，只需要找到id，排除id即可
select
    name
from
    salesperson
where
    sales_id not in 
    (select sales_id from orders where com_id in 
        (select com_id from company where name = 'RED'))

197. 上升的温度

https://leetcode-cn.com/problems/rising-temperature/

# Write your MySQL query statement below

# 这个不对，因为数据可能不是日期连续的。。。
select id 
from 
(select 
id, 
lag(Temperature, 1) over (order by recordDate) as preTemperature, Temperature,
lag(recordDate, 1) over (order by recordDate) as preRecordDate, recordDate
from
Weather)as t 
where 1 
and DATE_ADD(preRecordDate, interval 1 day) = recordDate
and Temperature > preTemperature 




# 思路2:就是笛卡尔积，然后判断出来即可
select t2.id 
from 
Weather as t1 
join 
Weather as t2 
where 1
and DATE_ADD(t1.recordDate, interval 1 day) = t2.recordDate
and t2.Temperature > t1.Temperature

1148. 文章浏览 I

https://leetcode-cn.com/problems/article-views-i/

# 思路1: 就where即可
select distinct author_id as id
from Views 
where author_id = viewer_id
order by author_id

1581. 进店却未进行过交易的顾客

https://leetcode-cn.com/problems/customer-who-visited-but-did-not-make-any-transactions/

# 思路1: left join 
select v.customer_id, 
count(1) as count_no_trans
from 
Visits as v 
left join 
Transactions as t 
on v.visit_id = t.visit_id
where t.transaction_id is null
group by v.customer_id

608. 树节点

https://leetcode-cn.com/problems/tree-node/

# 思路1: 就关联出父节点和子节点即可
select id, 
if(p_count > 0 && c_count > 0, 'Inner', if(p_count = 0, 'Root', 'Leaf')) as type
from 
(select t1.id, 
# t2.id as p_id, t3.id as child_id,
count(t2.id) as p_count,
count(t3.id) as c_count
from 
tree as t1 
left join 
tree as t2 
on t1.p_id = t2.id
left join 
tree as t3 
on t3.p_id = t1.id
group by id) <