sql查询重复记录和from子查询

group by 的select 列明如果提示重复 记得as起别名

 

select name from (
SELECT name,count(name) as count
FROM Table 
WHERE (OrgUUId = (select top 1 uuid from  org where orgname='xx') ) AND (IsDeleted = 0) 
group by name
having count(name)>1 
--order by count desc 
) as a     /*from后跟子查询必须取别名*/

 

 

join group by结果集:

declare @orgid varchar(500)
set @orgid='306b7b5d-32ce-4574-8304-b1d3e9a1f35b'
select tmp.*,* from A as ft right join
(
select top 100 percent ft_id,count(1) as c from ARules where createuserorguuid=@orgid
group by ft_id
order by c desc

)as tmp on ft.id=tmp.ft_id
order by tmp.c desc

 

 

update student set ClassName＝(select ClassName from class c where c.Id＝ClassId)  --通过班级id更新学生表的班级名