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

clear_zero 2014-12-11 05:41:55
我有一个表有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写出来呢?

谢谢
...全文
487 15 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
gw6328 2015-08-04
  • 打赏
  • 举报
回复
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) )
看题错了。楼主说的其实也不清楚。且,也不对,应该说是同时存在,如果且就一个数据也出不来。
gw6328 2015-08-04
  • 打赏
  • 举报
回复
引用 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啊。
Tiger_Zhao 2015-08-04
  • 打赏
  • 举报
回复
[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 2015-08-04
  • 打赏
  • 举报
回复

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 2015-08-04
  • 打赏
  • 举报
回复
/* 测试数据
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
物润声无 2015-08-03
  • 打赏
  • 举报
回复
楼主,8楼有误,9楼改了一下
物润声无 2015-08-03
  • 打赏
  • 举报
回复


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
)
--试一试

物润声无 2015-08-03
  • 打赏
  • 举报
回复


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
)
--试一试
c02645 2014-12-17
  • 打赏
  • 举报
回复

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

纯手打(语法未检查),应该这样是可以的,因为你两个条件组合,所以应该同个表关联,这样才查得出两个重复条件集
baidu_24196461 2014-12-14
  • 打赏
  • 举报
回复
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 不知道对不对
还在加载中灬 2014-12-11
  • 打赏
  • 举报
回复
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
直接这样即可
clear_zero 2014-12-11
  • 打赏
  • 举报
回复
引用 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也会被选中的
Neo_whl 2014-12-11
  • 打赏
  • 举报
回复


select dept_name from table_name where dpet_attribute in ('A','B')  and dept_attributevalue in (10,20)
group by dept_name
reenjie 2014-12-11
  • 打赏
  • 举报
回复

select dept_name from 表名 where (dept_attribute='A' and dept_attributevalue in(10,20)) or (dept_attribute='B' and dept_attributevalue=20)
reenjie 2014-12-11
  • 打赏
  • 举报
回复
select * from 表名 where (dept_attribute='A' and dept_attributevalue in(10,20)) or (dept_attribute='B' and dept_attributevalue=20)

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧