1)查询成绩表中name重复的
select name from score group by name having count(*) > 1;
2)查询成绩表中name>2次,并且按照降序排列
select count(name) as '出现次数',name from score group by name having count(name) > 2 order by '出现次数' DESC
3)去重
select distinct name from score
4)找出成绩大于150的,并且按照降序排列
select * from score where salary > 150 order by salary DESC
5)范围查找
select * from 表名 where 字段名 [not] between 值1 and 值2;
select * from score where salary > 150 or (salary < 100 and salary > 60)
6)找出比平均成绩小的
select * from score where salary < (select avg(salary) from score)
7)查询初年龄最小的学生姓名
select name from score where age = (select min(age) from score)
8)
select e.name,e.department,e.salary, t.`平均成绩` from emp e,
(select department,AVG(salary) as `平均成绩` from emp GROUP BY department) t
where e.department = t.department and e.salary < `平均成绩`
9)
方法1:
select y.name 姓名,y.score 数学,s.score 英语 from
(select * from table1 where subject ='数学') y,
(select * from table1 where subject ='英语') s
WHERE y.name=s.name
方法2: :
select Name as '姓名',
MAX(CASE WHEN Subject='数学' THEN Score ELSE 0 END) as '数学',
MAX(CASE WHEN Subject='英语' THEN Score ELSE 0 END) as '英语'
from table1 GROUP BY Name
10)统计学生文科,理科的总分。
select
Name AS '姓名',
CASE WHEN Subject='数学' THEN '理科' ELSE '文科' END AS '科别',
SUM(Score) AS '总分'
from table1 GROUP BY Name,
CASE WHEN Subject='数学' THEN '理科' ELSE '文科' END
11) mysql 中 case when then .... else end 的简单使用
数据SQL CASE 表达式是一种通用的条件表达式,类似于其它语言中的 if/else 语句。
/*
CASE 子句可以用于任何表达式可以有效存在的地方。
condition 是一个返回boolean 的表达式。 如果结果为真,那么 CASE 表达式的结果就是符合条件的 result。
如果结果为假,那么以相同方式搜寻任何随后的 WHEN 子句。
如果没有 WHEN condition 为真,那么 case 表达式的结果就是在 ELSE 子句里的值。
如果省略了 ELSE 子句而且没有匹配的条件, 结果为 NULL。
*/
CASE WHEN condition THEN result
WHEN condition THEN result
.............
[WHEN ...]
[ELSE result]
END
//
11.1) 统计日期胜负总数
SELECT data_time AS '时间',
SUM(CASE WHEN sta = '胜' THEN 1 ELSE 0 END) AS '胜场数',
SUM(CASE WHEN sta = '负' THEN 1 ELSE 0 END) AS '负场数'
from tongji GROUP BY data_time
11.1.1) 假设胜得3分,负得0分统计时间端的得分
select data_time,
SUM(CASE WHEN sta='胜' THEN 3 ELSE 0 END) AS score
from tongji GROUP BY data_time
11.2) 表中有A B C三列,用SQL语句实现:当A列大于B列时选择A列否则选择B列,
当B列大于C列时选择B列否则选择C列。
CASE WHEN 字段>=0 THEN 符合调价返回THEN里的值 WHEN…THEN…****ELSE 否则返回ELSE的值 END
这种写法是不是神似java中的for循环+if语句 ,值得注意的是:不管哪种写法,最后都要加END,否则报错
select
(CASE WHEN a > b THEN a
WHEN a > c THEN a
WHEN b > c THEN b
ELSE c
END )
from 表名
11.3) 有一张表,里面有3个字段:语文,数学,英语。其中有3条记录分别表示语文70分,数学80分,英语58分,
请用一条 sql语句查询出这三条 记录并按以下条件显示出来(并写出您的思路):
大于或等于80表示优秀,大于或等于60表示及格,小于60分表示不及格。
显示格式:
语文 数学 英语
及格 优秀 不及格
SELECT
(CASE WHEN chinese>=80 THEN “优秀” WHEN chinese>=60 THEN “及格” ELSE “不及格” END),
(CASE WHEN math>=80 THEN “优秀” WHEN math>=60 THEN “及格” ELSE “不及格” END),
(CASE WHEN english>=80 THEN “优秀” WHEN english>=60 THEN “及格” ELSE “不及格” END)
FROM table
11.4) 怎么把这样一个表儿
year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成这样一个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
select year,
max(case month when 1 then amount else 0 end) as ‘m1’ ,
max(case month when 2 then amount else 0 end) as ‘m2’ ,
max(case month when 3 then amount else 0 end) as ‘m3’ ,
max(case month when 4 then amount else 0 end) as ‘m4’
from 表名 group by year
12) 取出tb_send表中日期(SendTime字段)为当天的所有记录?(SendTime字段为datetime型,包含日期 与时间)
DATEDIFF(a,b) 该关键字是返回两个datatime类型的参数之间相隔的天数
CURDATE() 这个关键字是返回当前时间的datatime数据
select * from tb_send where DATEDIFF(SendTime,CURDATE() ) = 0;
13)多表联查
select * from t_organization AS o left join t_department AS d on o.organization_id = d.organization_id;
14)分组:group by 排序:order by
select 字段名 聚合函数,聚合函数 from 表名 group by 字段名 [having 筛选条件];
列出每个人的平均成绩和总成绩
select name, avg(salary) as '平均成绩' ,sum(salary) as '总成绩' from score group by name;
列出平均成绩大于70的人名和成绩
select name, avg(salary) as '平均成绩' from score group by name having avg(salary) > 70
列出平均成绩 > 70 总成绩 > 180 的人
select name, avg(salary) as '平均成绩' ,sum(salary) as '总成绩' from score group by name
having avg(salary) > 70 or sum(salary) > 180
列出总成绩并且按照升序排列
select name,sum(score) from score group by name order by sum(score);
列出每科的平均成绩
select obj,avg(salary) as '平均成绩' from score group by obj;
列出总成绩并且按照降序排列,并且值打印3行
select name,sum(score) from score group by name order by sum(score) desc limit 3;