首页 > 试题广场 >

Mysql中表student_table(id,name,b

[单选题]

Mysql中表student_table(id,name,birth,sex)，查询name重复的id最大的记录，比如'张三'重复2次，id分别是1、2，则结果是id=2的记录。如下SQL错误的是（）？

select t4.*
from (
select t2.* 
from 
(select name,count(*) as c1 from student_table GROUP BY name  having c1 > 1)t1 
inner join 
(select name, max(id) as id from student_table group by name ) t2 
on t1.name = t2.name
)t3
right join 
student_table t4
on t3.id = t4.id ;

select t4.*
from (
select t2.* 
from 
(select name,count(*) as c1 from student_table GROUP BY name  having c1 > 1)t1 
inner join 
(select name, max(id) as id from student_table group by name ) t2 
on t1.name = t2.name
)t3
inner join 
student_table t4
on t3.id = t4.id ;

select t4.*
from (
select t2.* 
from 
(select name,count(*) as c1 from student_table GROUP BY name  having c1 > 1)t1 
inner join 
(select name, max(id) as id from student_table group by name ) t2 
on t1.name = t2.name
)t3
left join 
student_table t4
on t3.id = t4.id ;

select t4.*
from (
select t2.* 
from 
(select name,count(*) as c1 from student_table GROUP BY name  having c1 > 1)t1 
left join 
(select name, max(id) as id from student_table group by name ) t2 
on t1.name = t2.name
)t3
left join 
student_table t4
on t3.id = t4.id ;

查看答案及解析

超时空contact

这题让我崩溃了

发表于 2022-02-22 18:50:25 回复(3)

红色足力健

为什么要这么写代码

发表于 2022-01-27 11:00:03 回复(2)

Runka

两个left所以排除left，inner可以和right/left转换，所以选A，从数据分析答案

发表于 2022-05-09 12:47:03 回复(0)

我要当数分

A right join的话是以student_table为主表，因此最后的记录和student_table记录数一样

发表于 2022-03-06 05:42:22 回复(0)

3号室

我懂你，这时候看这个SQL能让你舒服 select name, max(id) as max_id from student_table where name in( select name from student_table grouo by name having count (*)>1 ) group by name;

发表于 2023-12-14 10:55:38 回复(0)

秋招挣扎中

为什么要从 select t2.*里面再select t4.*

发表于 2022-09-21 21:01:08 回复(0)

阿波罗妮亚

t1:按名字分组限定为出现次数大于1的数据 ---->name，count--->出现次数大于1的名字 t2:按名字分组，查找id的最大值---->name，max(id)--->每个名字的id最大值 t3:内连接t1，t2，筛选名字相同的---->name ，count，max(id)--->出现次数大于1的名字对应的id最大值 t4:重命名的原表 右连接t3。t4，返回了t4对应的结果，会有重复数据