select a.职工名,b.公司名,c.工资 from (select 职工号,职工名 from Employee where 职工号='003') a inner join Company b on a.职工号=b.职工号 inner join Works c on b.公司名=c.公司名
SELECT A.职工名,b.公司名,c.工资 from Employee a left join Company on a.职工名=b.职工名 left join on Works c on b.公司名=c.公司名 where 职工号='003'
select 职工名,公司名,工资 from Employee inner join Company on Employee.职工号=Company.职工号 inner join Works on Company.公司名=Works.公司名
--职工表 declare @Employee table (employee_num varchar(10),employee_name varchar(10)) --公司表 declare @Company table (employee_num varchar(10),company_num varchar(10),company_name varchar(30)) --工资表 declare @Works table (company_name varchar(30),works decimal(8,2))insert into @Employee (employee_num,employee_name) values ('001','张三') insert into @Employee (employee_num,employee_name) values ('002','李四') insert into @Employee (employee_num,employee_name) values ('003','丁磊')insert into @Company (employee_num,company_num,company_name) values ('001','x_00000','新浪') insert into @Company (employee_num,company_num,company_name) values ('002','x_00001','腾讯') insert into @Company (employee_num,company_num,company_name) values ('003','x_00002','网易')insert into @Works (company_name,works) values ('新浪',8000) insert into @Works (company_name,works) values ('腾讯',8000) insert into @Works (company_name,works) values ('网易',8000)select c.employee_num,c.employee_name,c.company_name,d.works from ( select a.employee_num,a.employee_name,b.company_name from @Employee a ,@Company b where a.employee_num=b.employee_num )as c ,@Works d where c.company_name=d.company_name and c.employee_num='003'/*查询结果 003 丁磊 网易 8000.00 */
select 职工名 姓名,Company.公司名,工资 from Employee, Company, Works where Employee.职工号=Company.职工号 and Company.公司名=Work.公司名 and Employee.职工号='003'
--职工表 create table Employee (employee_num varchar(10),employee_name varchar(10)) --公司表 declare table Company (employee_num varchar(10),company_num varchar(10),company_name varchar(30)) --工资表 declare table Works (company_name varchar(30),works decimal(8,2))insert into Employee (employee_num,employee_name) values ('001','张三') insert into Employee (employee_num,employee_name) values ('002','李四') insert into Employee (employee_num,employee_name) values ('003','丁磊')insert into Company (employee_num,company_num,company_name) values ('001','x_00000','新浪') insert into Company (employee_num,company_num,company_name) values ('002','x_00001','腾讯') insert into Company (employee_num,company_num,company_name) values ('003','x_00002','网易') insert into Company (employee_num,company_num,company_name) values ('002','x_00001','腾讯')insert into Works (company_name,works) values ('新浪',8000) insert into Works (company_name,works) values ('腾讯',8000) insert into Works (company_name,works) values ('网易',8000) --我的SQL语句是这样的 select employee.employee_name as 员工姓名, works.company_name as 公司名, sum(Works.works) as 工资 from employee inner join company on employee.employee_num=Company.employee_num inner join Works on Company.company_name=works.company_name where employee.employee_num='002' group by employee.employee_name,works.company_name order by 工资 desc
from (select 职工号,职工名 from Employee where 职工号='003') a
inner join Company b on a.职工号=b.职工号
inner join Works c on b.公司名=c.公司名
from Employee a
left join Company on a.职工名=b.职工名
left join on Works c on b.公司名=c.公司名
where 职工号='003'
inner join Company on Employee.职工号=Company.职工号
inner join Works on Company.公司名=Works.公司名
--职工表
declare @Employee table (employee_num varchar(10),employee_name varchar(10))
--公司表
declare @Company table (employee_num varchar(10),company_num varchar(10),company_name varchar(30))
--工资表
declare @Works table (company_name varchar(30),works decimal(8,2))insert into @Employee (employee_num,employee_name) values ('001','张三')
insert into @Employee (employee_num,employee_name) values ('002','李四')
insert into @Employee (employee_num,employee_name) values ('003','丁磊')insert into @Company (employee_num,company_num,company_name) values ('001','x_00000','新浪')
insert into @Company (employee_num,company_num,company_name) values ('002','x_00001','腾讯')
insert into @Company (employee_num,company_num,company_name) values ('003','x_00002','网易')insert into @Works (company_name,works) values ('新浪',8000)
insert into @Works (company_name,works) values ('腾讯',8000)
insert into @Works (company_name,works) values ('网易',8000)select c.employee_num,c.employee_name,c.company_name,d.works from
(
select a.employee_num,a.employee_name,b.company_name from @Employee a ,@Company b where a.employee_num=b.employee_num
)as c ,@Works d where c.company_name=d.company_name and c.employee_num='003'/*查询结果
003 丁磊 网易 8000.00
*/
where Employee.职工号=Company.职工号 and Company.公司名=Work.公司名
and Employee.职工号='003'
create table Employee (employee_num varchar(10),employee_name varchar(10))
--公司表
declare table Company (employee_num varchar(10),company_num varchar(10),company_name varchar(30))
--工资表
declare table Works (company_name varchar(30),works decimal(8,2))insert into Employee (employee_num,employee_name) values ('001','张三')
insert into Employee (employee_num,employee_name) values ('002','李四')
insert into Employee (employee_num,employee_name) values ('003','丁磊')insert into Company (employee_num,company_num,company_name) values ('001','x_00000','新浪')
insert into Company (employee_num,company_num,company_name) values ('002','x_00001','腾讯')
insert into Company (employee_num,company_num,company_name) values ('003','x_00002','网易')
insert into Company (employee_num,company_num,company_name) values ('002','x_00001','腾讯')insert into Works (company_name,works) values ('新浪',8000)
insert into Works (company_name,works) values ('腾讯',8000)
insert into Works (company_name,works) values ('网易',8000)
--我的SQL语句是这样的
select employee.employee_name as 员工姓名,
works.company_name as 公司名,
sum(Works.works) as 工资
from
employee inner join company
on employee.employee_num=Company.employee_num
inner join Works
on Company.company_name=works.company_name
where employee.employee_num='002'
group by employee.employee_name,works.company_name
order by 工资 desc