常用的一些sql语句面试题

• 数据的竖向值转化为横向显示
  原数据表test
  
  想要的结果如下：
  
  sql如下：

  select DISTINCT id,name,
  (SELECT score FROM test t WHERE cour = '语文' and t.id=test.id) as '语文' ,
  (SELECT score FROM test t WHERE cour = '数学' and t.id=test.id) as '数学' ,
  (SELECT score FROM test t WHERE cour = '英语' and t.id=test.id) as '英语',
  (SELECT score FROM test t WHERE cour = '历史' and t.id=test.id) as '历史',
   (SELECT score FROM test t WHERE cour = '化学' and t.id=test.id) as '化学'
  from test GROUP BY FIELD(id,2,1,3)
  

  参考链接: link.

• 查询数据大于80分的名称
  原数据：数据表s1
  
  结果：
  
  sql语句：

  -- 方式1
  SELECT DISTINCT name ,fenshu 
  from s1
  where name not in(select DISTINCT name from s1 where fenshu<=80) GROUP BY name
  --方式2
  SELECT name from s1 GROUP BY name having min(fenshu)>80