sql基础day04

以下题目均来自：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