服务器: 消息 8120,级别 16,状态 1,行 1 列 'Univ_Seek_School.SchoolByname' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。 服务器: 消息 8121,级别 16,状态 1,行 1 列 'Univ_Seek_School.SchoolByname' 在 HAVING 子句中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
try: select 表.* from 表,(select SchoolName ,max(len(schoolByName)) as NameLen from 表 group by SchoolName) T where 表.SchoolName=T.SchoolName and len(schoolByName)=T.NameLen
declare @tab table(autoid varchar(20),schoolByName varchar(20),SchoolName varchar(20),SchoolBenelux varchar(20)) insert @tab values('8659','哈尔滨工业大学','工大','哈工大') insert @tab values('8660','工业大学','工大','哈工大') insert @tab values('8661','哈工大','工大','哈工大') insert @tab values('8662','哈工','工大','哈工大') insert @tab values('8663','哈工kkkkkkkkk','工大1','哈工大2') insert @tab values('8664','哈工kk','工大1','哈工大2') insert @tab values('8665','哈工ggggggggggfff','工大1','哈工大2')select * from @tabselect *,[排名]=(select count(1) from @tab where SchoolName=a.SchoolName and len(a.schoolByName)<=len(schoolByName)) from @tab a--你要的结果 select * from ( select *,[排名]=(select count(1) from @tab where SchoolName=a.SchoolName and len(a.schoolByName)<=len(schoolByName)) from @tab a ) b where b.排名=1
列 'Univ_Seek_School.SchoolByname' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
服务器: 消息 8121,级别 16,状态 1,行 1
列 'Univ_Seek_School.SchoolByname' 在 HAVING 子句中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
select 表.* from 表,(select SchoolName ,max(len(schoolByName)) as NameLen from 表 group by SchoolName) T
where 表.SchoolName=T.SchoolName and len(schoolByName)=T.NameLen
declare @tab table(autoid varchar(20),schoolByName varchar(20),SchoolName varchar(20),SchoolBenelux varchar(20))
insert @tab values('8659','哈尔滨工业大学','工大','哈工大')
insert @tab values('8660','工业大学','工大','哈工大')
insert @tab values('8661','哈工大','工大','哈工大')
insert @tab values('8662','哈工','工大','哈工大')
insert @tab values('8663','哈工kkkkkkkkk','工大1','哈工大2')
insert @tab values('8664','哈工kk','工大1','哈工大2')
insert @tab values('8665','哈工ggggggggggfff','工大1','哈工大2')select * from @tabselect *,[排名]=(select count(1) from @tab where SchoolName=a.SchoolName and len(a.schoolByName)<=len(schoolByName))
from @tab a--你要的结果
select * from (
select *,[排名]=(select count(1) from @tab where SchoolName=a.SchoolName and len(a.schoolByName)<=len(schoolByName))
from @tab a ) b where b.排名=1