我这里有一个跨国公司给我的SQL培训题，给大家分享一下（1）--答案

CREATE TABLE `department` (

`id` int(5) NOT NULL auto_increment,

`name` varchar(20) default '(NULL)',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=gbk

CREATE TABLE `employee` (

`id` int(5) NOT NULL auto_increment,

`name` varchar(20) NOT NULL,

`departmentid` int(5) NOT NULL,

PRIMARY KEY (`id`),

KEY `departmentid` (`departmentid`),

CONSTRAINT `employee_ibfk_2` FOREIGN KEY (`departmentid`) REFERENCES `department` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=gbk

2. select department.name as '部门名称', count(employee.name) as '员工数' from department join employee on(department.id=employee.departmentid) where department.name is not null group by department.name



3. select employee.name as '姓名' from department join employee on(department.id=employee.departmentid) where department.name is null



4. select employee.name as '姓名', count(employee.name) as '部门数' from department join employee on(department.id=employee.departmentid) group by employee.name having count(employee.name)>1



5. select department1.name as '部门名称',department2.name as '关联部门' from employee employee1 ,department department1, employee employee2 ,department department2 where employee1.departmentid=department1.id and employee2.departmentid=department2.id and not employee1.departmentid = 1 and not employee2.departmentid = 1 and employee1.name=employee2.name and not department1.name=department2.name group by department1.name,department2.name

这个是公司认为比较可以的代码，大家有更好，更高效的代码希望大家分享哦

接下来，我会为大家带来下一阶段的培训


本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/chen1255/archive/2009/08/08/4425913.aspx