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

[clear_zero]

我有一个表有3个列，
dept_name,dept_attribute，dept_attributevalue
Dept1 A 10
Dept1 A 20
Dept1 B 20
Dept1 C 10
Dept1 C 30
Dept1 D 10

Dept2 B 10
Dept2 C 10
Dept2 D 10

Dept3 A 10
Dept3 B 20
Dept3 C 10
Dept3 D 20
Dept3 E 20

我现在要找具有(attribute=A and (value=10 or value =20)) 且(attribute=B and (value=20))的部门
结果是dept1,dept3

怎么用sql写出来呢？

谢谢

[reenjie]

select * from 表名 where (dept_attribute='A' and dept_attributevalue in(10,20)) or (dept_attribute='B' and dept_attributevalue=20)

[reenjie]

select dept_name from 表名 where (dept_attribute='A' and dept_attributevalue in(10,20)) or (dept_attribute='B' and dept_attributevalue=20)

[Neo_whl]

select dept_name from table_name where dpet_attribute in ('A','B')  and dept_attributevalue in (10,20)
group by dept_name

[clear_zero]

引用 1 楼 reenjie 的回复:

select * from 表名 where (dept_attribute='A' and dept_attributevalue in(10,20)) or (dept_attribute='B' and dept_attributevalue=20)

这个不对啊，如果我给的例子里面有这个 Dept2 B 20 dept2也会被选中的

[还在加载中灬]

SELECT dept_name FROM TB

WHERE (dept_attribute='A' and (dept_attributevalue=10 or dept_attributevalue=20))
	OR(dept_attribute='B' and (dept_attributevalue=20))

GROUP BY dept_name

HAVING COUNT(DISTINCT dept_attribute)=2

直接这样即可

[baidu_24196461]

select * from ( select dept_name from table_name where dept_attribute=‘A’ and dept_attributevalue in ('10','20') intersect select dept_name from table_name where dept_attribute=‘b’ and dept_attributevalue=‘20’ )c 不知道对不对

[c02645]

select a.dept_name from TB  a inner join TB b on a.dept_name=b.dept_name where 
(a.dept_attribute='A' and (a.dept_attributevalue=10 or a.dept_attributevalue=20))
    and (b.dept_attribute='B' and (b.dept_attributevalue=20)) group by a.dept_name

纯手打（语法未检查），应该这样是可以的，因为你两个条件组合，所以应该同个表关联，这样才查得出两个重复条件集

[物润声无]

select * from table t1 where exist (
(select 1 from t1 where dept_attribute=‘A’ and  dept_attributevalue in ('10','20')
and exist
( 
select 1 from t1 where exists dept_attribute=‘B’ and  dept_attributevalue =20
)
--试一试

[物润声无]

select * from tablename t1 where exists (
(select 1 from t1 where dept_attribute=‘A’ and  dept_attributevalue in ('10','20')
and exists
( 
select 1 from t1 where dept_attribute=‘B’ and  dept_attributevalue =20
)
--试一试

[物润声无]

楼主，8楼有误，9楼改了一下

[Tiger_Zhao]

/* 测试数据
WITH table1(dept_name,dept_attribute,dept_attributevalue) AS (
    SELECT 'Dept1','A',10 UNION ALL
    SELECT 'Dept1','A',20 UNION ALL
    SELECT 'Dept1','B',20 UNION ALL
    SELECT 'Dept1','C',10 UNION ALL
    SELECT 'Dept1','C',30 UNION ALL
    SELECT 'Dept1','D',10 UNION ALL
    SELECT 'Dept2','B',10 UNION ALL
    SELECT 'Dept2','C',10 UNION ALL
    SELECT 'Dept2','D',10 UNION ALL
    SELECT 'Dept3','A',10 UNION ALL
    SELECT 'Dept3','B',20 UNION ALL
    SELECT 'Dept3','C',10 UNION ALL
    SELECT 'Dept3','D',20 UNION ALL
    SELECT 'Dept3','E',20
)*/
    SELECT dept_name
      FROM table1
     WHERE (dept_attribute='A' AND dept_attributevalue IN (10,20))
        OR (dept_attribute='B' AND dept_attributevalue = 20)
  GROUP BY dept_name
    HAVING COUNT(DISTINCT(dept_attribute)) = 2

dept_name
---------
Dept1
Dept3

[gw6328]

WITH table1(dept_name,dept_attribute,dept_attributevalue) AS (
    SELECT 'Dept1','A',10 UNION ALL
    SELECT 'Dept1','A',20 UNION ALL
    SELECT 'Dept1','B',20 UNION ALL
    SELECT 'Dept1','C',10 UNION ALL
    SELECT 'Dept1','C',30 UNION ALL
    SELECT 'Dept1','D',10 UNION ALL
    SELECT 'Dept2','B',10 UNION ALL
    SELECT 'Dept2','C',10 UNION ALL
    SELECT 'Dept2','D',10 UNION ALL
    SELECT 'Dept3','A',10 UNION ALL
    SELECT 'Dept3','B',20 UNION ALL
    SELECT 'Dept3','C',10 UNION ALL
    SELECT 'Dept3','D',20 UNION ALL
    SELECT 'Dept3','E',20
)

SELECT DISTINCT dept_name FROM table1 WHERE dept_attribute='A' AND dept_attributevalue IN (10,20) OR dept_attribute='B' AND dept_attributevalue =20

[Tiger_Zhao]

[Quote=引用 12 楼 jinfengyiye 的回复:]

SELECT DISTINCT dept_name FROM table1 WHERE dept_attribute='A' AND dept_attributevalue IN (10,20) OR dept_attribute='B' AND dept_attributevalue =20

[/Quote]
这两组测试数据你就过不了

    SELECT 'Dept4','A',20 UNION ALL
    SELECT 'Dept5','B',20

[gw6328]

引用 13 楼 Tiger_Zhao 的回复:

[Quote=引用 12 楼 jinfengyiye 的回复:]

SELECT DISTINCT dept_name FROM table1 WHERE dept_attribute='A' AND dept_attributevalue IN (10,20) OR dept_attribute='B' AND dept_attributevalue =20

[/Quote] 这两组测试数据你就过不了

    SELECT 'Dept4','A',20 UNION ALL
    SELECT 'Dept5','B',20

过了啊。怎么过不到？出来 1,3,4,5啊。

[gw6328]

SELECT DISTINCT dept_name FROM table1 a
WHERE EXISTS(SELECT 1 FROM table1 b WHERE dept_name=a.dept_name AND dept_attribute='A' AND dept_attributevalue IN (10,20) )
AND EXISTS (SELECT 1 FROM table1 b WHERE dept_name=a.dept_name AND dept_attribute='B' AND dept_attributevalue IN (20) )

看题错了。楼主说的其实也不清楚。且，也不对，应该说是同时存在，如果且就一个数据也出不来。