34,576
社区成员
发帖
与我相关
我的任务
分享
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) )
看题错了。楼主说的其实也不清楚。且,也不对,应该说是同时存在,如果且就一个数据也出不来。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
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
/* 测试数据
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
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
)
--试一试
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 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 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
直接这样即可
select dept_name from table_name where dpet_attribute in ('A','B') and dept_attributevalue in (10,20)
group by dept_name
select dept_name from 表名 where (dept_attribute='A' and dept_attributevalue in(10,20)) or (dept_attribute='B' and dept_attributevalue=20)