以下题目均来自:https://leetcode.cn/
1、1440. 计算布尔表达式的值
写一个 SQL 查询, 以计算表 Expressions 中的布尔表达式.
返回的结果表没有顺序要求.
# 返回的结果没有以布尔值呈现
select v1.value,e.operator,v2.value,
(case when operator = '=' then v1.value = v2.value
when operator = '<' then v1.value < v2.value
when operator = '>' then v1.value > v2.value
end) value
from expressions e
left join variables v1 on e.left_operand = v1.name
left join variables v2 on e.right_operand = v2.name;
#以下写法可以返回布尔值
select v1.name left_operand,e.operator,v2.name right_operand,
(case when operator = '=' and v1.value = v2.value then 'true'
when operator = '<' and v1.value < v2.value then 'true'
when operator = '>' and v1.value > v2.value then 'true'
else 'false'
end) value
from expressions e
left join variables v1 on e.left_operand = v1.name
left join variables v2 on e.right_operand = v2.name;
2、1264. 页面推荐
写一段 SQL 向user_id = 1 的用户,推荐其朋友们喜欢的页面。不要推荐该用户已经喜欢的页面。
你返回的结果中不应当包含重复项。
# 1、先找到user_id=1的朋友id
# 2、根据朋友id找对应的喜欢列表
# 3、排除掉user_id=1的喜欢列表(不能用!=,因为可能有多个值)
select distinct page_id as recommended_page
from likes
where user_id in (
select user2_id
from friendship
where user1_id = 1
union
select user1_id
from friendship
where user2_id = 1) and page_id not in ( select page_id from likes where user_id = 1);
3、570. 至少有5名直接下属的经理
select name
from employee
where id in (
select managerId
from employee
group by managerId
having count(managerId) >= 5
)
4、1303. 求团队人数
select e.employee_id, t.team_size
from employee e
left join (
select team_id, count(team_id) team_size
from employee
group by team_id
) t on e.team_id = t.team_id;
select e1.employee_id,count(*)
from employee e1 left join employee e2 on e1.team_id = e2.team_id
group by e1.employee_id;
5、180、连续出现的数字
编写一个 SQL 查询,查找所有至少连续出现三次的数字。
返回的结果表中的数据可以按 任意顺序 排列。
# 思路一:关联三张表,a 表的id+1等于b表的id,b表的id+1等于c表的id
select distinct a.num AS ConsecutiveNums
from logs a,
logs b,
logs c
where a.num = b.num
and b.num = c.num
and a.id = b.id - 1 and b.id = c.id - 1
;
# 思路二:id+1,num 和 id+2,num 的 数据能够在表中找到
select distinct num AS ConsecutiveNums
from logs
where (id+1,num) in (select * from logs)
and (id+2,num) in (select * from logs)
;
以上两种方式不够灵活
select distinct Num as ConsecutiveNums
from (
select Num,
case
when @prev = Num then @count := @count + 1
when (@prev := Num) is not null then @count := 1
end as CNT
from Logs, (select @prev := null,@count := null) as t
) as temp
where temp.CNT >= 3