select 学员ID,笔试成绩,面试成绩 from
(
select distinct 学员ID,
isnull((select top 1 考试成绩 from 表名 where 学员ID=别名.学员ID and 考试类型='笔试' and 考试成绩='合格'),'') as 笔试成绩,
isnull((select top 1 考试成绩 from 表名 where 学员ID=别名.学员ID and 考试类型='面试' and 考试成绩='合格'),'') as 面试成绩
from 表名 别名
) 别名
where 笔试成绩='合格' and 面试成绩='合格'
(
select distinct 学员ID,
isnull((select top 1 考试成绩 from 表名 where 学员ID=别名.学员ID and 考试类型='笔试' and 考试成绩='合格'),'') as 笔试成绩,
isnull((select top 1 考试成绩 from 表名 where 学员ID=别名.学员ID and 考试类型='面试' and 考试成绩='合格'),'') as 面试成绩
from 表名 别名
) 别名
where 笔试成绩='合格' and 面试成绩='合格'
(SELECT 考试成绩
FROM table
WHERE 学员ID = a.学员ID AND 考试类型 = '笔试' AND 考试次数 =
(SELECT MAX(考试次数)
FROM table
WHERE 学员ID = a.学员ID AND 考试类型 = '笔试')) AS 笔试成绩,
(SELECT 考试成绩
FROM table
WHERE 学员ID = a.学员ID AND 考试类型 = '面试' AND 考试次数 =
(SELECT MAX(考试次数)
FROM table
WHERE 学员ID = a.学员ID AND 考试类型 = '面试')) AS 面试成绩
FROM table a
(select 学员ID,考试成绩 from 表名 where 考试成绩='合格' and 考试类型='笔试' group by 学员ID,考试成绩) a
,
(select 学员ID,考试成绩 from 表名 where 考试成绩='合格' and 考试类型='面试' group by 学员ID,考试成绩) b
where a.学员ID=b.学员ID
create table StudentQK(學員ID varchar(30),考試類型 varchar(20),考試次數 int,考試成績 varchar(20))
insert into StudentQK
select '01', '筆試', 1, '不合格' union all
select '01', '筆試', 2, '合格' union all
select '01', '面試', 1, '缺考' union all
select '01', '面試', 2, '合格' union all
select '02', '筆試', 1, '不合格' union all
select '02', '筆試', 2, '合格'--測試
select * from StudentQK select 學員ID,考試類型,min(考試成績) 考試成績
from StudentQK
group by 學員ID,考試類型
having min(考試成績)='合格'
--刪除測試環境
drop table StudentQK
from 考试成绩管理表
where (考试类型='面试' and 考试成绩='合格')
or
(考试类型='笔试' and 考试成绩='合格')
group by 学员ID
having count(1)>1
FROM test aWHERE a.cj='hege' and a.id in (select id from test b group by id having count(b.id)>2)