with a as(

select 'a' as 姓名,56 as 分数,1 as id,'a1' as 身份证号

union all

select 'a' as 姓名,68 as 分数,2 as id,'a1' as 身份证号

union all

select 'b' as 姓名,54 as 分数,2 as id,'b1' as 身份证号

union all

select 'b' as 姓名,50 as 分数,3 as id,'b1' as 身份证号

union all

select 'c' as 姓名,50 as 分数,4 as id,'c1' as 身份证号

)

--第一种

select 姓名,count(姓名) as 考核次数,

case when max(分数)>=60 then '考核通过'

     else '考核不通过'

end as 考核结果

from a

group by 姓名

--第二种

select a1.姓名,a1.身份证号 from (

select 姓名,id,身份证号 from a group by 姓名,id,身份证号

)a1

inner join (

select id,count(姓名) as 作弊次数

from a

group by id

having(count(姓名))>=2) b on a1.id=b.id

SELECT dbo.Test.TNAME,a. OPENID,a.SCORES,a.INSERTTIME FROM dbo.Test 

JOIN (SELECT OPENID,MIN(INSERTTIME)AS INSERTTIME,MAX(SCORES) AS SCORES FROM dbo.Test GROUP BY OPENID HAVING COUNT(OPENID)>1) AS a 

ON dbo.Test.OPENID=a.OPENID AND a.INSERTTIME = Test.INSERTTIME

数据表TEST

执行结果

1.合格人员

2.考核多次人员

3.抄袭人员