注册 登录
编程论坛 MySQL论坛

[求助] 请问这个SQL语句怎么写.

当当 发布于 2007-07-13 09:44, 1651 次点击

有两张表(员工表和客户表)

#员工表
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语句写出来吗?

3 回复
#2
lmhllr2007-07-13 13:01
select `EmployeeId`,`EmployeeName`,`EmployeeTelephone`,`EmployeeAddress`,`count(*) as CustomCount from Employee` where `EmployeeId`=(select count(*) from `Custom` group by `EmployeeId`);

没测试,看看行不行,不行跟帖!
#3
myfor2007-07-14 13:47
SELECT e.EmployeeId, EmployeeName, EmployeeTelephone, EmployeeAddress, COUNT(CustomeId) FROM Employee e LEFT JOIN Custome c ON e.EmployeeId=c.EmployeeId GROUP BY c.CustomeId;
#4
leixiangchao2007-07-25 15:26
支持楼上
1