1.测试数据库
部门表
deptId deptName
1 研发部
2 市场部
3 客服部
雇员表
empId empName deptId
1 小罗 1
2 罗曾英 3
问题:
写sql语句,给定一个部门名称,查询该部门下的雇员
1.连接法
view plaincopy to clipboardprint?
- select t2.* from department t1
- join employee t2 on t1.deptId=t2.deptId
- where t1.deptName='研发部'
select t2.* from department t1join employee t2 on t1.deptId=t2.deptIdwhere t1.deptName='研发部'2.子查询法
view plaincopy to clipboardprint?
- select * from employee
- where depId in
- (
- select deptId from department where deptName='研发部'
- )
我用过的最长的联合查询代码实例,大家有空研究一下:
view plaincopy to clipboardprint?
- insert into T_cardMenu_851(C_MSISDN,C_CPROFILEID,C_USERLEVEL,C_USERCLASS,C_PARENTID,C_MENUID,C_APPLEN,C_ISLEAF,C_STAT,C_DOWNDATE,C_SYNID,C_BATCHNO,C_MENUNAME) select '8613511981403',c.C_CPROFILEID,c.C_USERLEVEL,c.C_USERCLASS,b.C_PARENTID,b.C_MENUID,length(b.C_BYTECODE)/2,decode(b.C_PARENTID,0,0,1),1,c.C_REGTIME,1,d.C_BATCHNO,b.c_menuname from t_appdefault a,t_menu b,T_userinfo_851 c,t_cardprofile d where c.C_CPROFILEID=a.C_CPROFILEID and c.C_USERLEVEL=a.C_USERLEVEL and c.C_USERCLASS=a.C_USERCLASS and b.C_CPROFILEID=a.C_CPROFILEID and b.C_USERLEVEL=a.C_USERLEVEL and a.C_PARENTID=b.C_PARENTID and a.C_MENUID=b.C_MENUID and b.C_USERCLASS=a.C_USERCLASS and c.C_MSISDN='8613511981403' and a.C_STAT=1 and a.C_DOWNLOAD=1 and d.C_CPROFILEID=c.C_CPROFILEID