There is one table,
Wages table
-------------------------------------------
Emp_id | basic salary | seniority salary |
-------------------------------------------
1 | 1.00 | 1.00 |
-------------------------------------------
2 | 1.00 | 2.00 |
-------------------------------------------
3 | 1.00 | 3.00 |
-------------------------------------------
4 | 1.00 | 4.00 |
-------------------------------------------
.........
You can use the "one-sentence combination query" command in the preceding table to query the wage statistical table. The format of the content to be retrieved is as follows:
-----------------------------------------------------------------
Emp_id | basic salary | seniority salary | Total | ranking
------------------------------------------------------------------
1 | 1.00 | 1.00 | 2.00 | x
------------------------------------------------------------------
2 | 1.00 | 2.00 | 3.00 | y
------------------------------------------------------------------
3 | 1.00 | 3.00 | 4.00 | ..
------------------------------------------------------------------
4 | 1.00 | 4.00 | 5.00 | ..
------------------------------------------------------------------
Answer:
Copy codeThe Code is as follows:
Begin tran
Create table Wages (Emp_id bigint not null primary key, basic salary money, seniority salary money)
Go
Insert into Wages (Emp_id, basic salary, seniority salary) values (1, 1.00, 1.00)
Insert into Wages (Emp_id, basic salary, seniority salary) values (2, 1.00, 2.00)
Insert into Wages (Emp_id, basic salary, seniority salary) values (3, 1.00, 3.00)
Insert into Wages (Emp_id, basic salary, seniority salary) values (4, 1.00, 4.00)
If @ error> 0 rollback else commit tran
Select Emp_id, basic salary, seniority salary, basic salary + seniority salary as total, row_number () over (order by basic salary + seniority salary) as ranking from Wages order by total
-- Drop table Wages