27,580
社区成员
发帖
与我相关
我的任务
分享
create table a表
(employeeid varchar(10),name varchar(10),email varchar(20))
insert into a表
select '001','Tom','' union all
select '002','Jerry','' union all
select '003','Cat',''
create table b表
(supplierId int,Name varchar(10),Price decimal(8,2))
insert into b表
select 1,'圆珠笔',1.00 union all
select 2,'白纸',30.00 union all
select 3,'电池',3.00
create table c表
(employeeid varchar(10),supplierId int,Numbers int)
insert into c表
select '001',2,1 union all
select '001',1,2 union all
select '002',1,1 union all
select '002',3,4
select a.name 'EmployeeName',
b.Name 'SupplierName',
isnull(c.Numbers,0) 'Numbers'
from a表 a
cross join b表 b
left join c表 c on a.employeeid=c.employeeid and b.supplierId=c.supplierId
order by a.employeeid
/*
EmployeeName SupplierName Numbers
------------ ------------ -----------
Tom 圆珠笔 2
Tom 白纸 1
Tom 电池 0
Jerry 圆珠笔 1
Jerry 白纸 0
Jerry 电池 4
Cat 圆珠笔 0
Cat 白纸 0
Cat 电池 0
(9 row(s) affected)
*/
select a.name 'EmployeeName',
b.Name 'SupplierName',
c.Numbers
from c表 c
inner join a表 a on c.employeeid=a.employeeid
inner join b表 b on c.supplierId=b.supplierId