请问这个SQL语句怎么写.
有两张表(员工表和客户表)
#员工表
create table Employee
(
EmployeeId int(8) auto_increment, #员工ID
EmployeeName varchar(8), #员工姓名
EmployeeAge varchar(3), #员工年龄
EmployeeTelephone varchar(8), #员工电话
EmployeeAddress varchar(50), #员工地址
primary key(EmployeeId) #主键约束
)ENGINE=InnoDB DEFAULT CHARSET=GBK;
#客户表
create table Custome
(
CustomeId int(8) auto_increment,
CustomeName varchar(8),
CustomeTelephone varchar(8),
CustomeCompany varchar(50),
EmployeeId int(8),
PRIMARY KEY(CustomeId),
FOREIGN KEY(EmployeeId) REFERENCES Employee(EmployeeId)
)ENGINE=InnoDB DEFAULT CHARSET=GBK;
然后插入一些数据:
insert into employee(EmployeeName,EmployeeAge,EmployeeTelephone,EmployeeAddress) value
( '张三 ', '26 ', '8574650 ', '杭州),
( '李四 ', '26 ', '8574651 ', '广州 '),
( '王五 ', '27 ', '8574652 ', '东芝 '),
( '赵六 ', '28 ', '8574653 ', '厦门 ')
insert into custome(customeName,customeTelephone,customeCompany,EmployeeId) value
( '客户1 ', '135685 ', 'IT科技1 ',1),
( '客户2 ', '135685 ', 'IT科技2 ',1),
( '客户3 ', '135685 ', 'IT科技3 ',2),
( '客户4 ', '135685 ', 'IT科技4 ',2),
( '客户5 ', '135685 ', 'IT科技5 ',3),
( '客户6 ', '135685 ', 'IT科技6 ',4)
我现在需要查询后显示成这样
员工编号 员工姓名 员工电话 员工地址 客户总数
1 张三 8574650 杭州 2
...
请问能够用一句SQL语句写出来吗?
[解决办法]
select employee.EmployeeId,EmployeeName,EmployeeTelephone,EmployeeAddress,count(CustomeId) from employee left join custome on employee.EmployeeId=custome.EmployeeId group by employee.EmployeeId
[解决办法]
select a.EmployeeId,a.EmployeeName,a.EmployeeTelephone,a.EmployeeAddress,b.cuscount
from Employee as a left join (select Employeeid ,count(*) cuscount from Custome group by Employeeid) as b on a.Employeeid =b.Employeeid
