每科成绩大于80分 查询 删除重复记录

-- 1条件嵌套查询
SELECT DISTINCT Name FROM DB_Achievement A
WHERE A.Name not in(
SELECT Distinct S.name FROM DB_Achievement S WHERE S.Achievement <80)
-- 2 分组查询
SELECT Name
FROM DB_Achievement
GROUP BY Name
Having MIN(Achievement)>=80
-- 3 去重查询
SELECT Distinct
[Name]
,[Subjects]
,[Achievement] FROM DB_Achievement

-- 多字段 删除重复记录
DELETE A
FROM DB_Achievement A ,
(SELECT Name,Subjects,Achievement FROM DB_Achievement
GROUP BY Name,Subjects,Achievement
HAVING COUNT(*) > 1 ) B
WHERE A.Name=B.Name AND a.Subjects= b.Subjects AND a.Achievement= b.Achievement
AND A.ID NOT IN (SELECT MIN(ID) AS ID
FROM DB_Achievement
GROUP BY Name,Subjects,Achievement
HAVING COUNT(*) > 1)