Leetcode--SQL刷题(601-1070)

-- 601 解决连续天数问题，注意FIND_IN_SET()函数的使用
-- 602 可以先试用UNION ALL,然后通过一个GROUP By结束 
-- 603 通过JOIN的方法解决连续性问题 
-- 610 三角形的判断，只考虑相加条件即可 
-- 612 只能使用全连接，注意round(col,2),sqrt(col),pow(col,2)等函数的使用
-- 613 和612题是一个套路，只是需要使用abs(col)方法即可
-- 615 注意date_format(col,'%Y-%m')的使用; GROUP By的新方式；使用JOIN，而不是在SELECT中嵌套子查询
-- 618 透视列的做法；注意max(col)中,如果col不为null，输出就是col
-- 619 注意方法3和方法2的区别，使用max来处理空值的问题
-- 626 MOD(col,2)函数处理奇偶问题；注意不需要JOIN或者子查询，可以直接得到结果
-- 627 善于使用IF函数
-- 1045 这道题非常重要，善用JOIN去解决问题

601.体育馆的人流量

方法一:自己写的
select id, visit_date, people from stadium
where id in
(select distinct id from
(select a.id
from stadium a join stadium b on a.id=b.id+1
join stadium c on b.id=c.id+1
where a.people>=100 and b.people>=100 and c.people>=100
union all
select b.id
from stadium a join stadium b on a.id=b.id+1
join stadium c on b.id=c.id+1
where a.people>=100 and b.people>=100 and c.people>=100
union all
select c.id
from stadium a join stadium b on a.id=b.id+1
join stadium c on b.id=c.id+1
where a.people>=100 and b.people>=100 and c.people>=100) t1);

方法二:对方法一的改进,但是性能差不多;三种分类情况再理解一下.
select distinct a.* from stadium a,stadium b,stadium c
where a.people>=100 and b.people>=100 and c.people>=100
and (
     (a.id = b.id-1 and b.id = c.id -1) or
     (a.id = b.id-1 and a.id = c.id +1) or
     (a.id = b.id+1 and b.id = c.id +1)
) order by a.id

方法三:使用窗口函数 标准写法 无法运行 不知道性能
select id,visit_date,people from
(
select id
,lead(people,1) over(order by id) ld
,lead(people,2) over(order by id) ld2
,visit_date
,lag(people,1) over(order by id) lg
,lag(people,2) over(order by id) lg2
,people
from stadium
) a
where (a.ld>=100 and a.lg>=100 and a.people>=100)
or (a.ld>=100 and a.ld2>=100 and a.people>=100)
or (a.lg>=100 and a.lg2>=100 and a.people>=100)


方法四: 性能极佳
select id,visit_date,people from stadium ,
(
	select if(count(0)>=3,GROUP_CONCAT(id),NULL) as ids -- 步骤3和步骤4：通过差值分组之后合并连续的ID数
	from 
	(
		SELECT 
			id
			,visit_date
			,people
			,IF(people>99,@num:=@num+1,@num:=0) -- 步骤1：用来连续计数大于99的数
			,IF(@num>0,id-@num,-1) as groupNum -- 步骤2：用来分组的差值
		FROM stadium,(SELECT @num:=0)a -- 定义@num变量
	) temp
	where groupNum>-1 -- -1表示当前数值是小于100的
	group by groupNum -- 步骤3：通过差值分组
)tempId
WHERE tempId.ids is NOT NULL and FIND_IN_SET(id,tempId.ids) -- 步骤5：最终查询数据
order by id 

602.好友申请 II ：谁有最多的好友

# 0.6510（好像没有别的很好的方法）
SELECT id, count(*) num
FROM
    (select requester_id id from request_accepted
    union all
    select accepter_id  id from request_accepted) t
GROUP BY id
ORDER BY num DESC
LIMIT 1;

603. 连续空余座位

#  0.6359 学会在自连接中实现相邻id连接，以及abs函数使用
SELECT distinct C1.seat_id
FROM cinema as C1 join cinema as C2
ON (C1.free='1' and C2.free='1' and abs(C1.seat_id-C2.seat_id)=1)
order by C1.seat_id

607. 销售员

# 应该没有更简单的方法0.5298
SELECT name 
FROM salesperson s
WHERE sales_id NOT IN
	(SELECT sales_id
	FROM company c,orders o
	WHERE c.com_id=o.com_id and c.name="RED");

608.树节点

# 自认为自己的思路无敌 0.9346
# 先判断是否是根节点，再找出中间节点。
SELECT id, (CASE when p_id is null then "Root"
                 when id  in (select p_id from tree) then "Inner"
                 else "Leaf" end) "Type"
FROM 
    tree;

610.判断三角形（这个题表名错了）

# 方法1 0.7450
SELECT x,y,z, (CASE when (x+y>z) and (x+z>y) and (y+z>x) then "Yes"
                   else "No" end) "triangle"
FROM
    triangle;
#方法2 按理说应该更好一点 但是实际没有0.6694（可能是系统问题）
SELECT x,y,z, (CASE when (x+y<=z) or (x+z<=y) or (y+z<=x) then "No"
                   else "Yes" end) "triangle"
FROM
    triangle;

612. 平面上的最近距离

# 第一次方法 0.4332，注意 ！= 判断条件的写法
SELECT  CAST(sqrt(power(a.x-b.x,2)+power(a.y-b.y,2)) AS decimal(10,2)) shortest
FROM 
    point_2d a,point_2d b
WHERE (a.x,a.y) != (b.x,b.y)
ORDER BY shortest
LIMIT 1

其中where判断条件还有两种写法：
WHERE not (a.x=b.x and a.y=b.y)
WHERE a.x != b.x OR a.y != b.y 

# 优化 0.85
SELECT min(round(sqrt(pow(a.x-b.x,2)+pow(a.y-b.y,2)),2)) shortest
FROM
    point_2d a, point_2d b
 WHERE not (a.x=b.x and a.y=b.y)

613. 直线上的最近距离

# 0.9614 很简单，但是进阶可以考虑一下
SELECT min(abs(a.x-b.x)) shortest
FROM
    point a, point b
 WHERE a.x <> b.x

614. 二级关注者

# 数据中有重复数据，导致这个方法不能用
SELECT followee as "follower", count(*) num
FROM 
    follow
WHERE followee in (SELECT follower from follow)
GROUP BY followee

# 方法2 修正 0.35
SELECT
    f1.follower, count(distinct(f2.follower)) num
FROM
    follow f1,follow f2
WHERE
    f1.follower=f2.followee
GROUP BY f1.foll**加粗样式**ower;

# 方法3(自己的方法 0.8462) 对方法1的更正， 因为数据中有重复数据。
SELECT followee as "follower", count(distinct follower) num
FROM 
    follow
WHERE followee in (SELECT follower from follow)
GROUP BY followee

615. 平均工资：部门与公司比较
妈的hhhh！mysql中group by中使用别名不报错！

# 方法1 0.533（自己做对啦）
# 问题：两个子查询中，分组时多做了一次时间格式化运算（贼SB!）
SELECT t1.pay_month,t1.department_id, (CASE when t1.sal>t2.sal then "higher"
                                           when t1.sal<t2.sal then "lower"
                                           else "same" end) as comparison
FROM
    (SELECT date_format(pay_date,'%Y-%m') pay_month, department_id, avg(amount) sal
    FROM 
        salary s join employee e
    ON s.employee_id=e.employee_id
    GROUP BY date_format(pay_date,'%Y-%m'),department_id) t1,

    (SELECT date_format(pay_date,'%Y-%m') pay_month,avg(amount) sal FROM salary
    GROUP BY date_format(pay_date,'%Y-%m')) t2
WHERE t1.pay_month=t2.pay_month
ORDER BY department_id,pay_month;

# 方法2 0.9353
# 重点注意group by的分组方法
SELECT t1.pay_month,t1.department_id, (CASE when t1.sal>t2.sal then "higher"
                                           when t1.sal<t2.sal then "lower"
                                           else "same" end) as comparison
FROM
    (SELECT date_format(pay_date,'%Y-%m') pay_month, department_id, avg(amount) sal
    FROM 
        salary s join employee e
    ON s.employee_id=e.employee_id
    GROUP BY 1,2) t1,

    (SELECT date_format(pay_date,'%Y-%m') pay_month,avg(amount) sal FROM salary
    GROUP BY 1) t2
WHERE t1.pay_month=t2.pay_month
ORDER BY department_id,pay_month;

提示：从日期中取出年份+月份，也可以使用left/right函数，，，自己理解一下，不推荐。

618. 学生地理信息报告

# 自己的错误做法：
SELECT max(IF(continent='America',name, null))  `America`,
       max(IF(continent='Asia',name, null)) `Asia`,
       max(IF(continent='Europe',name,null))  `Europe`
FROM 
    student
GROUP BY name
ORDER BY continent,name;

# 这样的输出是：
[["Jack",null,null],
["Jane",null,null],
[null,"Xi",null],
[null,null,"Pascal"]]

# 方法2：重点学习 代码逻辑 0.75
# 思考一下这里为什么加max？如果不加max，那么打印的可能就是组内的null值
SELECT 
    MAX(if(A.continent = 'America',A.NAME,NULL)) AS `America`,
    MAX(if(A.continent = 'Asia',A.NAME,NULL)) AS `Asia`,
    MAX(if(A.continent = 'Europe',A.NAME,NULL)) AS `Europe`
FROM
(
	SELECT S.*,
	@trank:=if(@pre_con = S.continent,
		@trank + 1,
		1
	) AS `trank`,
	@pre_con:=S.continent AS `pre`
	FROM student AS S,(SELECT @pre_con:=NULL,@trank:=0) AS T
	ORDER BY S.continent,S.NAME
) AS A
GROUP BY A.trank

619. 只出现一次的最大数字

# 方法1 有问题
SELECT if(num is null,null, num) "num"
FROM my_numbers
GROUP BY num Having count(*)=1
ORDER BY num desc
LIMIT 1

# 方法2：更正方法1:0.8725
# 判断null的方法要注意
SELECT ifNULL
    ((SELECT num
    FROM my_numbers
    GROUP BY num Having count(*)=1
    ORDER BY num desc
    LIMIT 1), null) num;

# 方法3：扩展思路 0.8223
SELECT MAX(num) as num
FROM
    (SELECT num
    FROM my_numbers
    GROUP BY num Having count(*)=1) t

620.有趣的电影

SELECT
	id,
	movie,
	description,
	rating
FROM
	cinema
WHERE
	id & 1
AND description <> 'boring'
ORDER BY
	rating DESC

这里还是使用<>好一点,not like没有这个快.
掌握位判断的方法,比mod和%都快.
奇数和1位与运算都是1,偶数都是0,因此本题中可以作为筛选条件
626.换座位

自己的答案 性能一般 但是值得鼓励
思路:建造辅助列 通过两个排序去做
select cast((case when 1 then @ran:=@ran+1 end) as decimal(10,0)) id, student
from(
select id,student,(case 
when id&1 then @gro:=@gro+1
else @gro
end) num
from seat,(select @gro:=0) t
order by num,id desc) t1, (select @ran:=0) t2;


方法二:逻辑简直无敌!性能极佳!!
逻辑:偶数id-1, 奇数id+1, 最后一个奇数id不变
SELECT (CASE 
            WHEN MOD(id,2) = 1 AND id = (SELECT COUNT(*) FROM seat) THEN id
            WHEN MOD(id,2) = 1 THEN id+1
            ElSE id-1
        END) AS id, student
FROM seat
ORDER BY id;

627.交换工资

方法1
update salary set sex=
case sex
when 'm' then 'f'
else 'm'
end;

方法2
UPDATE salary 
SET sex = if(sex = "m","f","m");

1045.买下所有产品的客户

# 方法1：自己的方法 0.7354（很笨重）
SELECT customer_id
FROM
    (SELECT distinct customer_id,product_key
    FROM customer) T
GROUP BY customer_id
HAVING count(*)=(SELECT count(distinct product_key) FROM product);

# 方法2：0.9274（还是连表查询更好一点）
SELECT customer_id
FROM
    (select customer_id,count(distinct product_key) as num 
    from Customer
    group by customer_id
    ) t
JOIN 
    (select count(product_key) as num
    from Product) m 
on t.num = m.num;

1050. 合作过至少三次的演员和导演

# 很简单的一道题
SELECT actor_id ACTOR_ID,director_id DIRECTOR_ID
FROM actordirector
GROUP BY actor_id,director_id
having count(*)>=3;

1070. 产品销售分析 III

# 方法1： 我认为是对的，但是顺序和答案不同。
SELECT product_id, year first_year, quantity, price
FROM
    (SELECT s.*, (case when @pro=product_id then @ran:=@ran+1
                    when @pro:=product_id then @ran:=1 end) ran
    FROM Sales s,(select @pro:=null, @ran:=null) t1
    ORDER BY product_id,year) t2
WHERE ran=1

# 方法2：思路清晰 0.7642（估计第一种更好）
SELECT s.product_id, s.year first_year,quantity,price
FROM
    sales s, (select product_id,min(year) year from sales group by product_id) t
WHERE s.product_id=t.product_id and s.year=t.year;