如何写sql查询具有多个特定属性的部门

[clear_zero]

我有一个表有2个列
dept_name,dept_attribute
Dept1 A
Dept1 B
Dept1 C
Dept1 D

Dept2 B
Dept2 C
Dept2 D

Dept3 A
Dept3 B
Dept3 C
Dept3 D
Dept3 E

我现在要查同时具有A，B，C，D属性的部门. 搜索结果应该是dept1,dept3
这个用sql语句怎么写，有没有什么简洁的写法呢

谢谢

[还在加载中灬]

先条件再聚合，速度应该也还行

SELECT dept_name FROM TB
WHERE dept_attribute IN('A','B','C','D')
GROUP BY dept_name
HAVING COUNT(dept_attribute)=4

如果多加判断属性的个数，加到IN 和后面的4 比如同时具有A B C D E，那就

SELECT dept_name FROM TB
WHERE dept_attribute IN('A','B','C','D','E')
GROUP BY dept_name
HAVING COUNT(dept_attribute)=5

[--小F--]

你这个不太严谨的 要是A,B,C,D有重复的话 你就会错误了。

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2014-12-11 09:22:30
-- Version:
--      Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86) 
--	Feb 10 2012 19:13:17 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([dept_name] varchar(5),[dept_attribute] varchar(1))
insert [tb]
select 'Dept1','A' union all
select 'Dept1','B' union all
select 'Dept1','C' union all
select 'Dept1','D' union all
select 'Dept2','B' union all
select 'Dept2','C' union all
select 'Dept2','D' union all
select 'Dept3','A' union all
select 'Dept3','B' union all
select 'Dept3','C' union all
select 'Dept3','D' union all
select 'Dept3','E'
--------------开始查询--------------------------
SELECT dept_name FROM TB WHERE dept_attribute IN('A','B','C','D') GROUP BY dept_name HAVING COUNT(DISTINCT dept_attribute)=(SELECT COUNT(distinct dept_attribute) FROM TB WHERE dept_attribute IN('A','B','C','D'))
----------------结果----------------------------
/* dept_name
---------
Dept1
Dept3

(2 行受影响)
*/

我这个 随便写写 也不够优化的

[还在加载中灬]

SELECT dept_name FROM TB

WHERE dept_attribute IN('A','B','C','D')

GROUP BY dept_name

HAVING COUNT(DISTINCT dept_attribute)=4

那就加个DISTINCT，之前考虑这是属性应该不会重复才没加～