Java面试总结mysql

1.根据部门号从高到低，工资从低到高列出每个员工的信息。

SELECT * FROM  User ORDER BY deptid DESC ,salary

2.用一条sql语句查询出每门课都大于80的学生姓名

 select distinct name from Student  where  name not in (select distinct name from student where fenshu<=80)

 3.数据库：表中有ABC三列，用SQL语句实现，当A大于B列时候选择A列否则选择B列，当B大于C列时候选择B列否则选择C列。

select (case  when a >b then a else b end),

(case when b>c  then b esle c end)

from table_name;

3.怎么在sql serever 中查询一个表中某个数据重复条数大于1的所有信息

select * from student  GROUP BY name HAVING COUNT(name)>1；

 

 

4.如果你要查看里面的重复数据只需要在外面加 name in

SELECT * from student  where name in(
select  name  from student  GROUP BY name HAVING COUNT(name)>1);

5.查出表中数据的详情：

SELECT name,
SUM(case student.kecheng when '数学' then  fenshu else 0 end )as 数学,
SUM(case student.kecheng when '语文' then  fenshu  else 0 end )as 语文
 from student GROUP BY name;

7.数据表中的数据实现累加

 select *, (SELECT SUM(num)from tiao where id<=b.id) as num1 from tiao as b；

 8.查询出表student中最大年龄和最小年龄？

 

select  MAX(age)as max_age,MIN(age)as min_age  from student;