在SQL中使用内联视图

Question

我有一个包含员工(emp(的数据库表，其中包含公司名称和工资。通过使用内联视图查询，我想列出姓名、工资和每个员工占所有员工总工资的百分比(salary/tot_sal*100(的新名称。我很难理解视图的用法。我尝试了以下代码，但没有成功。有什么想法吗？

create view tot_sal as
select sum(sal); 
select name, salary, salary/tot_sal*100 
from tot_sal

Answer 1

您有两次SELECT和一个分号。视图的名称不能与表的名称相同。您必须为每列命名。尝试以下

create view tot_sal_view as select 
sum(sal) sum_salary,
name, 
salary,
salary/tot_sal*100
from tot_sal;

Answer 2

尝试：

create view tot_sal_view as select name,salary, salary/(select sum(salary) from employees e)*100 
from employees;

给定示例：

CREATE TABLE employees (
name varchar(50)  ,
salary decimal(10,2)
);
INSERT INTO employees  VALUES 
('Steven',24000.00),
('Neena',17000.00),
('Lex',17000.00),
('Alexander',9000.00),
('Bruce',6000.00),
('David',4800.00),
('Valli',4800.00),
('Diana',4200.00),
('Nancy',12000.00),
('Daniel',9000.00),
('John',8200.00),
('Ismael',7700.00),
('Jose Manuel ',7800.00),
('Luis', 6900.00),
('Den',11000.00),
('Alexander',3100.00),
('Shelli',2900.00);

结果：

name         salary salary/(select sum(salary) from employees e)*100
Steven      24000.00    15.444015
Neena       17000.00    10.939511
Lex         17000.00    10.939511
Alexander   9000.00    5.791506
Bruce       6000.00    3.861004
David       4800.00    3.088803
Valli       4800.00    3.088803
Diana       4200.00    2.702703
Nancy       12000.00    7.722008
Daniel      9000.00    5.791506
John        8200.00    5.276705
Ismael      7700.00    4.954955
Jose Manuel 7800.00    5.019305
Luis        6900.00    4.440154
Den         11000.00   7.078507
Alexander   3100.00    1.994852
Shelli      2900.00    1.866152

查看演示