day13 SQL基础-连接

1440. 计算布尔表达式的值

写一个 SQL 查询, == 以计算表 Expressions 中的布尔表达式.==

返回的结果表没有顺序要求.

查询结果格式如下例所示.

select
	e.*,
	case when e.operator='=' and a.value=b.value then 'true' 
			when e.operator='>' and a.value>b.value then 'true'
			when e.operator='<' and a.value<b.value then 'true'
			else 'false'
	 end as value 
from
	expressions as e left join variables as a on e.left_operand =a.name
	left join variables as b on b.name=e.right_operand

1264. 页面推荐

写一段 SQL 向user_id = 1 的用户，推荐其朋友们喜欢的页面。不要推荐该用户已经喜欢的页面。

你返回的结果中不应当包含重复项。

返回结果的格式如下例所示。

方法一：
select
	 distinct page_id as recommended_page 
from
     Likes as l
where
	l.user_id in (
	select
		user2_id
	from
		Friendship as f
	where
		f.user1_id='1'
	union
	select
		user1_id
	from
		Friendship as f
	where
		f.user2_id='1'
		
	) and page_id not in(
			select page_id from Likes where user_id='1'
	)
	方法二：
	select
	    distinct l.page_id as recommended_page 
    from
		Likes as l
    where
		l.user_id in (
			select
				if(f.user1_id<f.user2_id , f.user2_id , f.user1_id) as user2_id
			from
				Friendship as f
			where
				f.user1_id='1' or f.user2_id='1'
		) and page_id not in(
			   select page_id from Likes where user_id='1'
		)

570. 至少有5名直接下属的经理

编写一个SQL查询，查询至少有5名直接下属的经理 。

以 任意顺序 返回结果表。

查询结果格式如下所示。

方法一：
select
	t.name
from
	Employee as t
where
    t.id in (
			select 
				e.managerId
			from
				Employee as e
		    group by
				e.managerId
			having
				count(*)>4
	)
方法二：左连接

1303. 求团队人数

编写一个 SQL 查询，以求得每个员工所在团队的总人数。

查询结果中的顺序无特定要求。

查询结果格式示例如下：

考点：窗口函数
select
	e.employee_id,
	count(e.team_id) over(partition by e.team_id) as team_size  
from
	Employee as e