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

2014-12-11 05:41:55 598 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

【SQL】查询部门有2个人或以上员工的部门号查询部门有2个人或以上员工的部门号 id payno 1 lisi 1 zhangsan 2 wangmeng 3 zhaoliu 3 wuxia 3 mengfang 4 guodaxia 5 guofurong 结果如下： id 1 3 解决：方法一： SELECT DI... 阅读详情

我有一个表有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写出来呢？

谢谢

...全文

oracle部门员工表查询使用scott/tiger用户下的emp表和dept表完成下列练习，表的结构说明如下 emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号) dept部门表(deptno部门编号/dname部门名称/loc地点) 工资＝薪金＋佣金 ------1．列出至少有一个员工的所有部门阅读详情

clear_zero

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

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)

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

相关推荐