连接之后有重合列的表格不能作为子查询

最新推荐文章于 2024-10-01 17:33:05 发布

rella1103

最新推荐文章于 2024-10-01 17:33:05 发布

阅读量151

点赞数

文章标签： mysql 数据分析

本文链接：https://blog.csdn.net/rella1103/article/details/107362218

版权

题目背景：找出数学成绩排名前五的学生姓名

select *,
          rank() over(partition by sc.c_id order by sc.scores desc) as ranking
          from test.score as sc inner join test.course as c on sc.c_id=c.c_id and c.c_name="数学"

上面代码执行正常没问题，可做为子查询，下面的代码执行就报错

 select  a.s_id  from
         (select *,
          rank() over(partition by sc.c_id order by sc.scores desc) as ranking
          from test.score as sc inner join test.course as c on sc.c_id=c.c_id and c.c_name="数学")a
where ranking<6

报错内容如下：
15:37:01 select a.s_id from (select *, rank() over(partition by sc.c_id order by sc.scores desc) as ranking from test.score as sc inner join test.course as c on sc.c_id=c.c_id and c.c_name=“数学”)a where ranking<6 Error Code: 1060. Duplicate column name ‘c_id’ 0.000 sec

后来我把 * 改为有限的几个字段，代码如下，执行ok：

select s_name from test.student
where s_id in (
      select  a.s_id
      from (
          select  sc.s_id,sc.c_id,c.c_name,
          rank() over(partition by sc.c_id order by sc.scores desc) as ranking
          from test.score as sc inner join test.course as c on sc.c_id=c.c_id and c.c_name="数学"
      ) a
      where ranking<6
      )