sql语句中的case

CREATE TABLE stu(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
score INT
)

INSERT INTO stu VALUES (NULL,'张三',60)
INSERT INTO stu VALUES (NULL,'李四',70)
INSERT INTO stu VALUES (NULL,'李娃',50)

SELECT 
   id,NAME,score,
   CASE 
   WHEN score>=60 THEN 'pass'
   WHEN score<60 THEN 'fail' END LEVEL
FROM stu

 

SELECT id,NAME,score,
   CASE
      WHEN score>=60 AND score<70 THEN '及格'
      WHEN score>=70 AND score<80 THEN '良好'
      WHEN score>=80 AND score<90 THEN '中'
      WHEN score>=90 THEN '优'
      ELSE '差' END AS LEVEL
FROM stu

SELECT id,NAME,score,
    CASE 
      WHEN score>=60 AND score<70 THEN '及格' END AS level1,
    CASE
      WHEN score>=70 AND score<80 THEN '良好' END AS LEVEL2,
    CASE
      WHEN score>=80 AND score<90 THEN '中' END AS LEVEL3,
    CASE
      WHEN score>=90 THEN '优' END AS LEVEL4,
    CASE
      WHEN score<60 THEN  '差' END AS LEVEL0
FROM stu

 

#按照性别计算各成绩等级中的数量

SELECT sex,
      SUM(CASE WHEN score<60 THEN 1 ELSE 0 END) AS '差',
      SUM(CASE WHEN score>=60 AND score<80 THEN 1 ELSE 0 END) AS '中',
      SUM(CASE WHEN score>=80 AND score<90 THEN 1 ELSE 0 END) AS '良',
      SUM(CASE WHEN score>=90   THEN 1 ELSE 0 END) AS '优'
FROM stu
GROUP BY sex

 

=========================去重============

SELECT * FROM stu s
WHERE (s.name,s.score,s.sex)
IN (SELECT NAME,score,sex FROM stu GROUP BY NAME,score,sex HAVING COUNT(*)>1)