|
我有下表,包括两个字段,即a和b,如下所示:# c0 w" Q5 `1 g: }& D U: E
create table employe( empID varchar department varchar(10));插入一些记录:
. F' M8 o) `9 S" @1 n8 r2 n/ qinsert into employe values('A101','Z'),('A101','X'),('A101','Y'),('A102','Z'),('A102','X A103','Z'),('A103','Y'),('A104','X'),('A104','Y'),('A105','Z'),('A106','X');select * from employe;empID department------------------A101 ZA101 XA101 YA102 ZA102 XA103 ZA103 YA104 XA104 YA105 ZA106 X注意
8 U+ i0 B% X+ K3 j! ?6 w& K$ {:现在我想向员工展示独一无二的部门Z部门员工Y。所以,根据条件,只有A103应该显示员工,因为他只属于部门Z和部门Y。但是员工A101不应因属而出现Z,X,and Y。
' @9 U5 ~! ]# b5 ~) A: P预期结果 :$ z* r, M$ a2 j$ C4 \1 Z- V3 G' }. v5 b
若条件如下:Z,Y结果如下:! ^7 i0 T* l3 A! \: A4 a5 Y4 U6 ?( R
empID------A103若条件如下:Z,X结果如下:$ Q6 A6 [$ X) c" {
empID------A102若条件如下:Z,X并且Y后续结果应为:4 N& W8 l+ a/ c, \' F
empID------A101注意 :我只想在那里where子句中执行此操作(不想使用groupbyandhaving因为我where该子句也将包含在另一个子句中。8 ?8 C* b. } e: X
* u3 z6 l! j2 M( d3 E9 I. ? 解决方案: # a7 P; k2 p1 E8 G/ z
第一个解决方案+ y3 Z* K& _/ q- b4 @; S# w( h
sqlfiddle' Y \5 x9 W, u( f I/ l7 K$ M
SELECT empIdFROM SELECT empID,cc = COUNT(DISTINCT department) FROM employe WHERE department IN('Y','Z GROUP BY empID)tWHERE t.cc = 2 AND t.cc = SELECT COUNT(*) FROM employe WHERE empID = t.empID )第二个解决方案& f' L. y/ Q8 C" {) K6 Y4 `4 H
sqlfiddle, R" Y. f, C ]1 x8 h4 |$ g* i
SELECT e.empIdFROM employe eWHERE e.department IN('Y','Z')GROUP BY e.empIDHAVING COUNT(e.department) = 2 AND COUNT(e.department) = (SELECT COUNT(*) FROM employe WHERE empID = e.empId)不使用GROUP BY和HAVING:6 k' ?) I# @1 @/ d7 \
SELECT DISTINCT e.empIDFROM employe eWHERE EXISTS( SELECT 1 FROM employe WHERE department = 'Z' AND empID = e.empID ) AND EXISTS( SELECT 1 FROM employe WHERE department = 'Y' AND empID = e.empID ) AND NOT EXISTS( SELECT 1 FROM employe WHERE department NOT IN('Y','Z') AND empID = e.empID ) |
|