Server选择

Question

我有下表，名为tblMastTestElement

cTestCode    |  cElementCode
24HCRECLE    |  CRE
CALCRECLE    |  CRE
CALEXR       |  CRE
CRE          |  CRE
CRECLE       |  CRE
EGFR         |  CRE
EGFR         |  EG          

我需要一个查询来返回EGFR作为测试代码，其中只有元素CRE和EG。

像这样的东西

Select cTestCode
from tblMastTestElement
where cElementCode IN('CRE' And 'EG')

Answer 1

要返回具有cElementCode for 的cElementCode，可以使用 'CRE‘和'EG’(而不是或)。

SELECT cTestCode
FROM   tblMastTestElement
WHERE  cElementCode IN ( 'CRE', 'EG' )
GROUP  BY cTestCode
HAVING COUNT(DISTINCT cElementCode) = 2 

Answer 2

Select distinct t1.cTestCode
from tblMastTestElement t1
join tblMastTestElement t2 on t2.cTestCode = t1.cTestCode
where t1.cElementCode = 'EG'
and t2.cElementCode = 'CRE'

Answer 3

这样就行了。

Select t1.cTestCode
from tblMastTestElement t1
where t1.cElementCode = 'CRE'
AND EXISTS (
    Select 1
    from tblMastTestElement t2
    where t2.cElementCode = 'EG'
    AND t1.cTestCode = t2.cTestCode)
)