drop table emp;
drop table dept;
create table dept
(
deptId int primary key,
deptName varchar(20),
)
create table emp
(
empId int primary key,
empName varchar(20),
empCardId varchar(18) unique,
empBirthday datetime,
deptId int references dept(deptId)
)
insert into dept values(1,'人事部')
insert into dept values(2,'生产部')
insert into dept values(3,'研发部')insert into emp values(1,'xiaozhou','444059595959456123','2001-01-01',1)
insert into emp values(2,'xiaofan','444059595959456121','2001-01-01',1)
insert into emp values(3,'jiangyu','444059595959456124','2002-03-04',2)
insert into emp values(4,'wanglei','444059595959456125','2002-03-05',2)
insert into emp values(5,'xiaotan','444059595959456126','2002-03-05',3)
insert into emp values(6,'laoshi','444059595959456127','2002-03-05',3)
insert into emp values(7,'小城','444059595959456128','1988-01-05',2)
insert into emp values(8,'大东','444059595959456129','1988-01-05',1)
insert into emp values(9,'下子','444059595959456122','1988-01-05',3)
一张员工表,一张部门表,关联的部分是部门Id;
(1)、查找身份证为444059595959456121的员工的部门名称及员工名?
(2)、查找生日相同且部门编号相同的 员工编号、姓名、生日及部门名称?
注意:问题1没难度,关键是问题2能答出来即可得分,谢谢。小弟初来,捐出身家10分
drop table dept;
create table dept
(
deptId int primary key,
deptName varchar(20),
)
create table emp
(
empId int primary key,
empName varchar(20),
empCardId varchar(18) unique,
empBirthday datetime,
deptId int references dept(deptId)
)
insert into dept values(1,'人事部')
insert into dept values(2,'生产部')
insert into dept values(3,'研发部')insert into emp values(1,'xiaozhou','444059595959456123','2001-01-01',1)
insert into emp values(2,'xiaofan','444059595959456121','2001-01-01',1)
insert into emp values(3,'jiangyu','444059595959456124','2002-03-04',2)
insert into emp values(4,'wanglei','444059595959456125','2002-03-05',2)
insert into emp values(5,'xiaotan','444059595959456126','2002-03-05',3)
insert into emp values(6,'laoshi','444059595959456127','2002-03-05',3)
insert into emp values(7,'小城','444059595959456128','1988-01-05',2)
insert into emp values(8,'大东','444059595959456129','1988-01-05',1)
insert into emp values(9,'下子','444059595959456122','1988-01-05',3)
一张员工表,一张部门表,关联的部分是部门Id;
(1)、查找身份证为444059595959456121的员工的部门名称及员工名?
(2)、查找生日相同且部门编号相同的 员工编号、姓名、生日及部门名称?
注意:问题1没难度,关键是问题2能答出来即可得分,谢谢。小弟初来,捐出身家10分
where stuclass.classid=student.classid
and student.studentid=1这个用where刚测试了也行 自己看看吧 没用你的表明
这样能查出生日相同的,但是部门还不同,lz看你自己能不加条件进去查出你自己想要的结果
select e5.empId,e5.empName,e5.empBirthday,dept.deptName from dept,(
select e3.* from emp e3,
(select e1.empBirthday,e1.deptId from emp e1 group by e1.empBirthday,e1.deptId having count(*) >1 ) e4 where e3.deptId = e4.deptId
and e3.empBirthday = e4.empBirthday
)
e5 where dept.deptId = e5.deptId
select d.deptname,e.empname from emp e,dept d
where e.deptid=d.deptid
and e.empcardid='444059595959456121'(2)、查找生日相同且部门编号相同的 员工编号、姓名、生日及部门名称?
select distinct e1.empid,e1.empname,e1.empbirthday,d.deptname from emp e1,dept d
where e1.deptid=d.deptid
and e1.deptid in (
select deptid from emp
group by empbirthday,deptid
having count(empbirthday)>1 and count(deptid)>1
)
and e1.empbirthday in (
select empbirthday from emp
group by empbirthday,deptid
having count(empbirthday)>1 and count(deptid)>1
)
select table1.empId,table1.empName,table1.empBirthday,(select dept.deptName from dept where dept.deptId=table1.deptID)as deptName from emp table1,emp table2where table1.empId!=table2.empId and table1.empBirthday = table2.empBirthday and table1.deptId=table2.deptId
from emp e,emp e1,dept d where e.empbirthday=e1.empbirthday
and e.empid!=e1.empid and e.deptid=e1.deptid and e.deptid=d.deptid刚刚建表给你写出来了。
自己调试了应该没错。
用的oracle啊
这位兄台,谢谢你了。我忘记来这看了,后来我搞出来了,虽然你也弄出来了,但其实没你这么复杂。
select e1.empId,e1.empName,e1.empBirthday,deptName
from emp e1,emp e2,dept
where e1.empBirthday=e2.empBirthday
and e1.deptId=e2.deptId
and e1.empId<>e2.empId
and e1.deptId=dept.deptId
你自己试下吧!
不过真的谢谢了。