MYSQL-LeetcodePractice-DAY05&06-合并

只记录了很有学习意义的题目。

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

题目

合并的写代码思路见这个图👋

图不知道为什么显示不了了T_T

代码

select
    customer_id , count(customer_id) count_no_trans
from
    visits v 
left join
    transactions t
on 
    v.visit_id = t.visit_id
where amount is null 
group by v.customer_id
order by v.customer_id;

对于多表联结这个表一定要记住！

✍ORDER BY升序降序

SELECT columns FROM table
ORDER BY column;  #升序
SELECT columns FROM table
ORDER BY column DESC; #降序

607.销售员

题目

两种思路

1.暴力求解层层嵌套

select s.name from SalesPerson s where sales_id not in(
    select sales_id from Orders o where com_id in(
        select com_id from Company c where c.name = 'RED' 
    )
);

2.OUTER JOIN 和NOT IN

首先建一个临时表保存向RED公司销售过东西的人，然后用姓名信息将这个表与salesperson表建立联系

select *
from orders o
left join company c on o.com_id = c.com_id
where
c.name = 'RED' ;

select s.name from salesperson s
where s.sales_id not in(select *
from orders o
left join company c on o.com_id = c.com_id
where
c.name = 'RED');

197.上升的温度

题目

考点

1.datediff()

返回两个日期之间的时间

1	DATEDIFF(datepart,startdate,enddate)

✍datepart参数可以是下面的值

datepart	缩写
年	yy, yyyy
季度	qq, q
月	mm, m
年中的日	dy, y
日	dd, d
xxxxxxxxxx11 1SELECT product_id, ‘store1’ store, store1 price2FROM Products3WHERE store1 IS NOT NULL4UNION ALL5SELECT product_id, ‘store2’ store, store2 price6FROM Products7WHERE store2 IS NOT NULL8UNION ALL9SELECT product_id, ‘store3’ store, store3 price10FROM Products11WHERE store3 IS NOT NULL;sql	xxxxxxxxxx11 1SELECT product_id, ‘store1’ store, store1 price2FROM Products3WHERE store1 IS NOT NULL4UNION ALL5SELECT product_id, ‘store2’ store, store2 price6FROM Products7WHERE store2 IS NOT NULL8UNION ALL9SELECT product_id, ‘store3’ store, store3 price10FROM Products11WHERE store3 IS NOT NULL;sql
星期	dw, w
小时	hh
分钟	mi, n
秒	ss, s
毫秒	ms
微妙	mcs
纳秒	ns

思路讲解

代码

SELECT
    weather.id AS 'Id'
FROM
    weather
        JOIN
    weather w ON DATEDIFF(weather.date, w.date) = 1
        AND weather.Temperature > w.Temperature
;