我这里有一个跨国公司给我的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

 

 

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

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