人力资源不要求统计全体员工的工资指数,工资指数的计算公式为年龄与工资的乘积,这就需要将FAge和FSalary的乘积作为一个工资指数列体现到检索结果中。执行下面的SQL语句:
SELECT FNumber, FName, FAge * FSalary FROM T_Employee
+----------+--------+--------------+
| fnumber | fname | fage*fsalary |
+----------+--------+--------------+
| DEV001 | Tom | 207500.00 |
| DEV002 | Jerry | 64400.00 |
| DEV003 | Potter | NULL |
| HR001 | Jane | 98900.00 |
| HR002 | Tina | 82500.00 |
| IT001 | Smith | 154000.00 |
| IT002 | NULL | 75600.00 |
| SALES001 | Timmy | 132500.00 |
| SALES002 | Stone | 290500.00 |
+----------+--------+--------------+
同样,这里的“FAge*FSalary”并不是一个世纪存在的列,但是它们在查询出来的数据中看起来是一个实际存在的字段,他们完全可以被看成一个普通字段,比如可以为此字段定别名。执行下面的SQL语句:
SELECT FNumber, FName, FAge*FSalary AS FSalaryIndex FROM T_Employee
+----------+--------+--------------+
| FNumber | FName | FSalaryIndex |
+----------+--------+--------------+
| DEV001 | Tom | 207500.00 |
| DEV002 | Jerry | 64400.00 |
| DEV003 | Potter | NULL |
| HR001 | Jane | 98900.00 |
| HR002 | Tina | 82500.00 |
| IT001 | Smith | 154000.00 |
| IT002 | NULL | 75600.00 |
| SALES001 | Timmy | 132500.00 |
| SALES002 | Stone | 290500.00 |
+----------+--------+--------------+
前面提到的常量字段完全可以当做普通字段来看待,那么普通字段也可以和常量字段进行计算,甚至常量字段间也可以进行计算。比如人力资源不要求统计每个员工的工资幸福指数,工资幸福指数的计算公式为工资/(年龄-21) ,而且要求在每行数据前添加一列,这列的值等于125与521的和。我们编写下面的SQL语句:
SELECT 125 + 521, FNumber, FName, FSalary/(FAge-21) AS FHappyIndex FROM T_Employee
+---------+----------+--------+-------------+
| 125+521 | fnumber | fname | fhappyindex |
+---------+----------+--------+-------------+
| 646 | DEV001 | Tom | 2075.000000 |
| 646 | DEV002 | Jerry | 328.571429 |
| 646 | DEV003 | Potter | NULL |
| 646 | HR001 | Jane | 2150.000000 |
| 646 | HR002 | Tina | 825.000000 |
| 646 | IT001 | Smith | 785.714286 |
| 646 | IT002 | NULL | 466.666667 |
| 646 | SALES001 | Timmy | 1325.000000 |
| 646 | SALES002 | Stone | 592.857143 |
+---------+----------+--------+-------------+
计算字段也可以在WHERE语句等子句或者UPDATE、DELETE中使用。比如下面的SQL语句用来检索所有工资幸福指数大于1000的员工信息:
SELECT * FROM T_Employee WHERE FSalary/(FAge-21)>1000
+----------+-------+------+---------+-------------+---------------+
| FNumber | FName | FAge | FSalary | FSubCompany | FDepartment |
+----------+-------+------+---------+-------------+---------------+
| DEV001 | Tom | 25 | 8300.00 | Beijing | Development |
| HR001 | Jane | 23 | 4300.00 | Beijing | HumanResource |
| SALES001 | Timmy | 25 | 5300.00 | Beijing | Sales |
+----------+-------+------+---------+-------------+---------------+