编程论坛 » MySQL » [求助] 请问这个SQL语句怎么写.

当当 发表于 2007-7-13 09:44

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

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

lmhllr 发表于 2007-7-13 13:01

select `EmployeeId`,`EmployeeName`,`EmployeeTelephone`,`EmployeeAddress`,`count(*) as CustomCount from Employee` where `EmployeeId`=(select count(*) from `Custom` group by `EmployeeId`);<BR><BR>没测试，看看行不行，不行跟帖！

myfor 发表于 2007-7-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;

leixiangchao 发表于 2007-7-25 15:26

支持楼上

查看完整版本: [求助] 请问这个SQL语句怎么写.