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
`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