SELECT BATCH_ID
FROM PM_BATCH_ST
WHERE RESULT IN ('ISC', 'LLC')
GROUP BY BATCH_ID
HAVING COUNT(DISTINCT RESULT) = 2;

我认为以下内容可以：

select distinct batch_id
from  PM_BATCH_ST a
where a.result = 'ISC' and 
  exists (
    select 1
    from  PM_BATCH_ST b
    where a.batch_id=b.batch_id 
      and b.result='LLC'
  )

这可以通过INTERSECT来实现

SELECT BATCH_ID
FROM PM_BATCH_ST
WHERE RESULT = 'ISC'

INTERSECT

SELECT BATCH_ID
FROM PM_BATCH_ST
WHERE RESULT = 'LLC'

测试结果：

BATCH_ID
1000384769
1000564932
1000585739

对于2个值，我只需要self-JOIN并使用WHERE子句，如下所示：

SELECT st1.BATCH_ID
FROM PM_BATCH_ST st1
JOIN PM_BATCH_ST st2 ON st1.BATCH_ID = st2.BATCH_ID
WHERE st1.RESULT = 'ISC' AND st2.RESULT = 'LLC'

这应该给予所有BATCH_ID，同时具有ISC和LLC，作为RESULT
SQL Fiddle导致：
| 批次ID|
| --|
| 1000564932 |
| 1000585739 |
| 1000384769 |

CREATE TABLE PM_BATCH_ST (
    BATCH_ID INT,
    RESULT TEXT
);

INSERT INTO PM_BATCH_ST (BATCH_ID, RESULT)
VALUES
    (1000564932, 'ISC'),
    (1000564932, 'LLC'),
    (1000585739, 'ISC'),
    (1000585739, 'LLC'),
    (1000384769, 'ISC'),
    (1000384769, 'LLC'),
    (1000384757, 'LLC'),
    (1000888940, 'ISC');

/* @Qubicon has the most tighten-up answer. Below is another way of doing it*/

WITH ISC_table AS (
  SELECT *
  FROM PM_BATCH_ST
  WHERE CONVERT(VARCHAR, RESULT) IN ('ISC')
),
    LLC_table AS (
  SELECT *
  FROM PM_BATCH_ST
  WHERE CONVERT(VARCHAR, RESULT) IN ('LLC')
)

SELECT
  t1.BATCH_ID AS BATCH_ID
FROM ISC_table AS t1
INNER JOIN LLC_table AS t2
ON t1.BATCH_ID = t2.BATCH_ID;

| 批次ID|
| --|
| 1000564932 |
| 1000585739 |
| 1000384769 |
fiddle

这是另一种方法，你可以达到预期的结果。

SELECT DISTINCT a.BATCH_ID
FROM PM_BATCH_ST a
INNER JOIN PM_BATCH_ST b ON a.BATCH_ID = b.BATCH_ID
WHERE a.RESULT = 'ISC' AND b.RESULT = 'LLC';