成都私人网站建设,投资公司排名,网站建设需要几步,北京海淀区网络科技有限公司备注:测试数据库版本为MySQL 8.0
需求:求emp表各个岗位的工资之和#xff0c;如无#xff0c;用0代替
如需要scott用户下建表及录入数据语句#xff0c;可参考:scott建表及录入数据sql脚本
CASE语法
SELECT deptno,ifnull(sum(case when job MANAGER then sal else 0 …备注:测试数据库版本为MySQL 8.0
需求:求emp表各个岗位的工资之和如无用0代替
如需要scott用户下建表及录入数据语句可参考:scott建表及录入数据sql脚本
CASE语法
SELECT deptno,ifnull(sum(case when job MANAGER then sal else 0 end),0) s_MANAGER,ifnull(sum(case when job ANALYST then sal else 0 end),0) s_ANALYST,ifnull(sum(case when job CLERK then sal else 0 end),0) s_CLERK,ifnull(sum(case when job PRESIDENT then sal else 0 end),0) s_PRESIDENT,ifnull(sum(case when job SALESMAN then sal else 0 end),0) s_SALESMANFROM empGROUP BY deptno;
mysql SELECT deptno,- ifnull(sum(case when job MANAGER then sal else 0 end),0) s_MANAGER,- ifnull(sum(case when job ANALYST then sal else 0 end),0) s_ANALYST,- ifnull(sum(case when job CLERK then sal else 0 end),0) s_CLERK,- ifnull(sum(case when job PRESIDENT then sal else 0 end),0) s_PRESIDENT,- ifnull(sum(case when job SALESMAN then sal else 0 end),0) s_SALESMAN- FROM emp- GROUP BY deptno;
----------------------------------------------------------------
| deptno | s_MANAGER | s_ANALYST | s_CLERK | s_PRESIDENT | s_SALESMAN |
----------------------------------------------------------------
| 10 | 2450.00 | 0.00 | 1300.00 | 5000.00 | 0.00 |
| 20 | 2975.00 | 6000.00 | 1900.00 | 0.00 | 0.00 |
| 30 | 2850.00 | 0.00 | 950.00 | 0.00 | 5600.00 |
----------------------------------------------------------------
3 rows in set (0.00 sec)需求:部门编号为20的所有的员工信息以行的形式显示
group_concat语法
group_concat语法: GROUP_CONCAT([DISTINCT] expr [,expr …] [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name …]] [SEPARATOR str_val])
-- 默认分隔符是,
SELECT T.DEPTNO, group_concat(t.ename) namesFROM EMP TWHERE T.DEPTNO 20GROUP BY T.DEPTNO;-- 指定根据ename字段排序,分隔符为-
SELECT T.DEPTNO, group_concat(t.ename order by ename SEPARATOR -) namesFROM EMP TWHERE T.DEPTNO 20GROUP BY T.DEPTNO;
mysql -- 默认分隔符是,
mysql SELECT T.DEPTNO, group_concat(t.ename) names- FROM EMP T- WHERE T.DEPTNO 20- GROUP BY T.DEPTNO;
--------------------------------------
| DEPTNO | names |
--------------------------------------
| 20 | SMITH,JONES,SCOTT,ADAMS,FORD |
--------------------------------------
1 row in set (0.00 sec)mysql
mysql -- 指定根据ename字段排序,分隔符为-
mysql SELECT T.DEPTNO, group_concat(t.ename order by ename SEPARATOR -) names- FROM EMP T- WHERE T.DEPTNO 20- GROUP BY T.DEPTNO;
--------------------------------------
| DEPTNO | names |
--------------------------------------
| 20 | ADAMS-FORD-JONES-SCOTT-SMITH |
--------------------------------------
1 row in set (0.00 sec)
