查询以获得dept ID

让我们谈谈查询 我有员工表 empId ename deptId 1 P 10 2 Q 10 3 X 20 4 Y 20 5 Z 20 6 P1 10 7 Q1 10 8 X1 20 9 Y1 20 10 Z1 20 11 m 30 和认证table certId empid 1 1 2 3 3 5 4 11 现在他想要部门没有员工完成50％认证的部门ID 输出： 部门ID 10 20 我试过以下查询 选择 a.deptID 来自 （选择 COUNT（*）为总计，deptId from #employee Group by deptId）A join （选择 COUNT（*）为 deptwise，deptId 来自 #employee emp left join #certifiaction cert on cert.empid = emp.empid 其中 cert.certId null group by emp.deptId）B ON A .deptId = B.deptId 其中（b.deptwise * 0。 1 ）/（ a.total * 0. 1 ）> 0. 5 但是他不想要这个查询。如何做出派生表？ 请帮我解决，将有助于另一次面试。 谢谢 Pratap 解决方案 试试这个： WITH CTE0（deptid，deptcount） AS （ 选择 e1.deptid，count（c.empid） 来自员工e1 关键字>加入证书c e1.empid = c.empid group by e1.deptid ）， CTE1（deptid，deptcount） AS （ 选择 deptid，count（empid） 来自 employee group by deptid ） SELECT CTE1.deptid FROM cte0 join cte1 ON CTE0.deptid = CTE1.deptid WHERE CTE0.deptcount / CTE1.deptcount< ; 0 。 5 参考： SQL SERVER 2008中的公用表表达式（CTE） [ ^ ] Hi Guys ,Today I went to Interview for SQL developer.He asked me one question ,after long struggle to gave up my self from there lets talk about queryI have Employee tableempId ename deptId1 P 102 Q 103 X 204 Y 205 Z 206 P1 107 Q1 108 X1 209 Y1 2010 Z1 2011 m 30and Certification table certId empid1 12 33 54 11Now he wants the dept Id on which doesn't have 50% of certification done by their employeesoutput : Dept ID 1020 I tried by with below query Select a.deptID from(Select COUNT(*)as total ,deptId from #employeeGroup by deptId) Ajoin (Select COUNT(*) as deptwise ,deptId from #employee emp left join #certifiaction cert on cert.empid=emp.empid where cert.certId is null group by emp.deptId) BON A.deptId=B.deptIdwhere (b.deptwise*0.1) /(a.total*0.1)>0.5But he doesn't want this query.How to do that with out doing derived tables? Please help me to resolve ,will help for another interview .Thanks Pratap 解决方案 Try this:WITH CTE0 (deptid, deptcount)AS( select e1.deptid, count(c.empid) from employee e1 join certificate c on e1.empid = c.empid group by e1.deptid),CTE1 (deptid, deptcount)AS( select deptid, count(empid) from employee group by deptid)SELECT CTE1.deptid FROM cte0 join cte1 ON CTE0.deptid=CTE1.deptid WHERECTE0.deptcount / CTE1.deptcount < 0.5refer: Common Table Expressions(CTE) in SQL SERVER 2008[^] 这篇关于查询以获得dept ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！