mysql - 为什么 SQL 不允许对多个子查询使用 WITH 子句?
问题描述
为什么 SQL 只允许嵌套子查询?
例如,拿这个问题
- 找出每个职业中评分最高的用户。
表名是ratings,有列
- 用户身份
- 职业
- 评分
在 Postgres 或 Bigquery 中,我会这样做
with ratings_by_user as (
select occupation, user_id, count(*) num_ratings
from ratings
group by 1,2
),
max_ratings_by_occupation as (
select occupation, max(num_ratings) as max_ratings
from ratings_by_user
group by 1
),
select occupation, user_id
from ratings_by_user
inner join max_ratings_by_occupation
using (occupation)
where num_ratings = max_ratings
但我不确定如何在 SQL 中执行此操作,因为我需要将所有子查询嵌套在一个块中。这是我在 SQL 中的尝试,但它不起作用。
select occupation, user_id, count(*) as num_ratings
from (
select occupation, max(num_ratings) max_ratings
from (
select occupation, user_id, count(*) num_ratings
from users
group by 1,2
) as ratings_table
group by 1
) as max_ratings_table
)
inner join ratings on ratings.occupation = max_ratings_table.occupation
where max_ratings = num_ratings
谁能启发我如何在 SQL 中使用相同样式的 Postgres / Bigquery 我希望按顺序处理我的子查询?我只是发现很难在一大块中解决复杂的问题。
非常感谢您的参与。
解决方案
根据https://dev.mysql.com/doc/refman/8.0/en/with.html和MySQL“WITH”子句-WITH
仅在 MySQL 8+ 上受支持。确保您使用的是适当版本的 MySQL
转换为嵌套版本并不难。我们采用您的工作 sql:
with ratings_by_user as (
select occupation, user_id, count(*) num_ratings
from ratings
group by 1,2
),
max_ratings_by_occupation as (
select occupation, max(num_ratings) as max_ratings
from ratings_by_user
group by 1
),
select occupation, user_id
from ratings_by_user
inner join max_ratings_by_occupation
using (occupation)
where num_ratings = max_ratings
我们复制所有内容,包括 WITH 的括号,并在使用别名之前将其粘贴。
第 1 步,剪切 rating_by_user 并将其粘贴到使用 ratings_by_user 的任何位置(两次)
--cut from here
with ratings_by_user as ,
max_ratings_by_occupation as (
select occupation, max(num_ratings) as max_ratings
from
--paste to here
(
select occupation, user_id, count(*) num_ratings
from ratings
group by 1,2
) ratings_by_user
group by 1
),
select occupation, user_id
from
--and also paste to here
(
select occupation, user_id, count(*) num_ratings
from ratings
group by 1,2
) ratings_by_user
inner join max_ratings_by_occupation
using (occupation)
where num_ratings = max_ratings
第 2 步,剪切 max_ratings_by_occupation 并将其粘贴到使用它的位置:
with ratings_by_user as ,
--cut from here
max_ratings_by_occupation as ,
select occupation, user_id
from
(
select occupation, user_id, count(*) num_ratings
from ratings
group by 1,2
) ratings_by_user
inner join
--paste to here
(
select occupation, max(num_ratings) as max_ratings
from
(
select occupation, user_id, count(*) num_ratings
from ratings
group by 1,2
) ratings_by_user
group by 1
) max_ratings_by_occupation
using (occupation)
where num_ratings = max_ratings
第三步,清理空的withs
select occupation, user_id
from
(
select occupation, user_id, count(*) num_ratings
from ratings
group by 1,2
) ratings_by_user
inner join
(
select occupation, max(num_ratings) as max_ratings
from
(
select occupation, user_id, count(*) num_ratings
from ratings
group by 1,2
) ratings_by_user
group by 1
) max_ratings_by_occupation
using (occupation)
where num_ratings = max_ratings
这将是优化/重写的开始。棘手的部分是它使用了 rating_by_user 两次,因此在步骤 1 中需要两次粘贴
您的重新格式化尝试没有成功,因为您试图在外部级别使用仅存在于内部级别的结果集:
select occupation, user_id, count(*) as num_ratings
from
( --max_ratings_table available inside these brackets
select occupation, max(num_ratings) max_ratings
from (
select occupation, user_id, count(*) num_ratings
from users
group by 1,2
) as ratings_table
group by 1
) as max_ratings_table
--end of max_ratings_table availability
)
inner join ratings on ratings.occupation = max_ratings_table.occupation
-- ^^^^^^^^^^^^^^^^^
-- mrt not available here
where max_ratings = num_ratings
推荐阅读
- http - Golang HTTP 自定义错误处理响应
- c# - 将上下文从控制器操作发送到 Blazor 页面
- zip - 如何使用 tar 和 pigz 单独压缩子文件夹?
- sql - 尝试implenet .env后的Django数据库问题
- regedit - 如何使用 CMD 脚本更改 regedit 的值?
- typescript - 如何修复我的 Stripe redirectToCheckout 集成错误?
- python - 使用 ROS 从相机中提取多帧
- docker - 在 docker 中运行时如何禁用 NGINX 日志记录
- mongodb - MongoDB 架构组织
- php - 如何获取当前帖子分类的术语名称?