1.产生一个结果集,如果一个员工工资小于等于2000,就返回消息“NO”,如果大于等于
4000,就返回消息“YES”。如果在这两者之间,就返回“OK”.结果集如下:
EMP表原始数据
Name sal
------------------------------
Wsp 2300
Hjj 6700
king 6000
ford 1800
james 1200
按要求,显示效果图
Name sal status
------------------------------------------
Wsp 2300 ok
Hjj 6700 yes
king 6000 yes
ford 1800 no
james 1200 no
select Name, sal,
case WHEN sal <= 2000 THEN "NO"
WHEN sal >=4000 THEN "YES"
ELSE "OK"
end
as status
FROM EMP;
2.从表中随机返回5 条
记录
混凝土 养护记录下载土方回填监理旁站记录免费下载集备记录下载集备记录下载集备记录下载
!可以修改下面的语句,要求下次执行时产生不同的结果集
Select ename,job from emp
Select ename, job from emp order by rand() limit 5;
3.如:emp表
Deptno cnt
------------------------------
10 3
20 5
30 6
效果图:
Deptno_10 Deptno_20 Deptno_30
---------------------------------------------------
3 5 6
SELECT
SUM(CASE WHEN Deptno = 10 THEN 3 ELSE 0 END) as Deptno_10,
SUM(CASE WHEN Deptno = 20 THEN 5 ELSE 0 END) as Deptno_20,
SUM(CASE WHEN Deptno = 30 THEN 6 ELSE 0 END) as Deptno_30 FROM emp;
4.如表emp原台数据如
empno job name
-------------------------------------------------------------
1 analyst scott
2 analyst ford
3 cleark smith
4 cleark miller
5 cleark james
6 manager jones
7 manager clark
8 manager blank
效果图:
Cleark manager analyst
----------------------------------------------
Smith jones scott
Miller clark ford
James blank
SELECT
MAX(IF(job='cleark',name,'')) AS cleark,
MAX(IF(job='manager',name,'')) AS manager,
MAX(IF(job='analyst',name,'')) AS analyst
FROM emp
GROUP BY name
ORDER BY name DESC
浙公网安备 33021202002483