not exists
的相关子查询select distinct a.document_id from tablename a inner join tablename b b on a.document_id=b.payment_docid
where not exists
(select 1 from tablename b1 where b1.payment_docid=b.payment_docid and currency<>'EUR')
试试这个查询:
select payment_docId from MyTable
group by payment_docId
having max(currency) = 'EUR'
and min(currency) = 'EUR'
或者您可以使用having count(*) = 1
与min
或max
一起使用。
select t1.* from table2 as t1
where exists( select 1 from table2 t2 where t1.payment_docid=t2.payment_docid
having count(distinct currency)=1)
and currency='EUR'
SELECT
pd.payment_doc_id
, pd.currency
FROM DocTable dt
INNER JOIN PaymentDocs pd
ON dt.document_id = pd.payment_doc_id AND pd.currency IN ('EUR')
GROUP BY
:SELECT
pd.payment_doc_id
, pd.currency
FROM DocTable dt
INNER JOIN PaymentDocs pd
ON dt.document_id = pd.payment_doc_id AND pd.currency IN ('EUR')
GROUP BY pd.payment_doc_id
, pd.currency
select doc_id
from table t
group by doc_id
having min(currency) = max(currency) and min(currency) = 'EUR';