SQL 显示员工姓名，每位员工的总工资（总工资=工资+佣金）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24095499/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Display employee name,total salary of each employee(total salary=salary+commission)
提问by S.pal
Display employee name, total salary of each employee.
显示员工姓名，每位员工的总工资。
Conditions:
状况：
- if commission is
not NULLthentotal salary=(salary + commission) - else
total salary = salary;
- 如果佣金是
not NULL那么total salary=(salary + commission) - 否则
total salary = salary；
Here is my table:
这是我的表：
Table name: myemp
表名： myemp
Columns:
列：
empno [Primary key], name, salary, commission, deptno
create table myemp
(
empno number primary key,
name varchar2(20),
salary number,
commission number,
deptno number
);
Query:
询问：
select
name, salary, commission,
(salary + ((salary*commission) / 100)) as "total_salary"
from myemp;
This query give incorrect result.
此查询给出不正确的结果。
When commission is NULL, then total_salary = 0. But it should be total_salay=salary;
当佣金为 时NULL，则total_salary = 0。但它应该是total_salay=salary;
How to fix this?
如何解决这个问题？
回答by Ravi
My table like below: SQL> select * from emp;
我的表如下： SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
已选择 14 行。
My result table while i have null value in comm column is:
当我在 comm 列中有空值时，我的结果表是：
SQL> select empno,ename,sal,comm,(sal+nvl(comm,0)) as "Total_sal" from emp;
SQL> select empno,ename,sal,comm,(sal+nvl(comm,0)) as "Total_sal" from emp;
EMPNO ENAME SAL COMM Total_sal
7369 SMITH 800 800
7499 ALLEN 1600 300 1900
7521 WARD 1250 500 1750
7566 JONES 2975 2975
7654 MARTIN 1250 1400 2650
7698 BLAKE 2850 2850
7782 CLARK 2450 2450
7788 SCOTT 3000 3000
7839 KING 5000 5000
7844 TURNER 1500 0 1500
7876 ADAMS 1100 1100
7900 JAMES 950 950
7902 FORD 3000 3000
7934 MILLER 1300 1300
14 rows selected.
已选择 14 行。
回答by temp1212
select salarymonths as totalearing,count() from Employee group by totalearing order by totalearing desc limit 1;
选择工资月份作为totalearing,count() from Employee group by totalearing order by totalearing desc limit 1;
created by Rahul Panwar
由 Rahul Panwar 创建
回答by Gaurav Vanani
you can use nvl function to check availability of salary and commission, if salary and commission is not null then both are summed up, if salary or commission is null then it will take 0 instead of null, if both are null then it will take 0 as total salary so this way you can calculate salary + commission by using nvl function (nvl function is only available in oracle).
您可以使用 nvl 函数来检查工资和佣金的可用性，如果工资和佣金不为空，则两者相加，如果工资或佣金为空，则取 0 而不是空，如果两者都为空，则取 0作为总工资，这样您就可以使用 nvl 函数计算工资 + 佣金（nvl 函数仅在 oracle 中可用）。
select name,salary,commission,nvl(salary,0)+nvl(commission,0) as Total_salary from myemp;
for mysql and sql server you may use IFNULL function.
对于 mysql 和 sql 服务器，您可以使用 IFNULL 函数。
回答by Lennart
(salary + ((salary*coalesce(commission,0) / 100)) as "total_salary"
回答by S.pal
Query:
询问：
select
name, salary, commission,
(salary + coalesce(((salary * commission) / 100), 0)) "total_salary"
from myemp;
回答by Jenn
You are looking for the salary + the commission, if there is one. The issue is not how to conditionally add the commission, but rather how much to add onto the salary. You know that the result will be at least the employees initial salary, so if the commission did not exist then just add 0 as the commission.
您正在寻找工资 + 佣金，如果有的话。问题不是如何有条件地增加佣金，而是增加多少工资。您知道结果至少是员工的初始工资，因此如果不存在佣金，则只需添加 0 作为佣金。
Select name, (salary + IsNull(commission, 0)) as total_salary
