Sql查询仅获取包含特定列中给定值的记录

I have following tables:

我有以下表格：

Table A

表A.

Aid, Cid, Jid
A1,  C1,  J1
A2,  C1,  J2
A3,  C2,  J1
A4,  C2,  J3

Table C[Cid, X] with Cid as foreign key in table A.

表C [Cid，X]，表C中有Cid作为外键。

I want to get all the CIds from table A that contains both the Jids J1 and J2. For above table, I want to return C1 as the output. If I put an AND clause, I don't get any records and if I put an OR clause for the J column, I also get C2 in the output. Please assist.

我想从表A中获取包含Jids J1和J2的所有CId。对于上表，我想返回C1作为输出。如果我放置一个AND子句，我没有得到任何记录，如果我为J列放置一个OR子句，我也在输出中得到C2。请协助。

5 个解决方案

#1

You may either use a HAVING COUNT ( DISTINCT) with a CASE block or, as suggested by Salman, filter it in where clause.

您可以使用带有CASE块的HAVING COUNT（DISTINCT），或者如Salman所建议的那样，在where子句中过滤它。

SQL Fiddle

SQL小提琴

PostgreSQL 9.6 Schema Setup:

PostgreSQL 9.6架构设置：

CREATE TABLE TableA
    (Aid varchar(3), Cid varchar(3), Jid varchar(3))
;

INSERT INTO TableA
    (Aid, Cid, Jid)
VALUES
    ('A1', 'C1', 'J1'),
    ('A2', 'C1', 'J2'),
    ('A3', 'C2', 'J1'),
    ('A4', 'C2', 'J3'),
    ('A5', 'C2', 'J3'),
    ('A7', 'C3', 'J2'),
    ('A8', 'C3', 'J2'),
    ('A9', 'C4', 'J1'),
    ('A10','C4', 'J1'),
    ('A11','C1', 'J3'),
    ('A12','C1', 'J2')

;

Query 1:

查询1：

SELECT cid
FROM   TABLEA
GROUP  BY cid
HAVING COUNT (DISTINCT CASE jid
                         WHEN 'J1' THEN 'J1'
                         WHEN 'J2' THEN 'J2'
                       END) = 2

Results:

结果：

| cid |
|-----|
|  C1 |

Query 2:

查询2：

SELECT cid
  FROM   TABLEA
  WHERE jid IN ('J1', 'J2')
    GROUP  BY cid
 HAVING COUNT ( DISTINCT jid ) = 2

Results:

结果：

| cid |
|-----|
|  C1 |

#2

Self Join Solution

This self join will get you the necessary combinations:

这种自我加入将为您提供必要的组合：

select distinct a1.Cid
    from A as a1
    inner join A as a2 on a2.Cid = a1.Cid
    where a1.Jid = 'J1' and a2.Jid = 'J2'

note: It only works with testing for 2 Jid's.

注意：它仅适用于2个Jid的测试。

I hope this helps.

我希望这有帮助。

#3

get all the CIds from table A that contains both the Jids J1 and J2

从表A中获取包含Jids J1和J2的所有CId

This means that the query result will contain C1 and C2 based on data in Table A, so the below won't happen.

这意味着查询结果将包含基于表A中数据的C1和C2，因此不会发生以下情况。

I want to return C1 as the output

我想返回C1作为输出

#4

SELECT DISTINCT Cid
FROM Table_A a
JOIN Table_A b
ON a.Cid = b.Cid
WHERE a.Jid = 'J1'
AND b.Jid = 'J2';

#5

try,

尝试，

    select distinct cid from TableA  a  outer  apply 
 (select jid from TableA where jid='J2' and cid=a.Cid) b 
where a.jid='J1' and  b.jid is not null