如何获取具有多个行但不同列值的记录？

Question

我有一个名为“disp_table”的表，包含3列：service_code、row_status 和 business_unit_id。

| service_code | row_status | business_unit_id |
| --- | --- | --- |
| A | 1 | BU1 |
| A | 0 | BU2 |
| A | 0 | BU3 |
| B | 1 | BU1 |
| B | 0 | BU2 |
| C | 0 | BU2 |
| C | 0 | BU3 |

我需要一个查询来获取在所有业务单元（business_unit_id）下，其 row_status 均为 0 的服务代码。在上述案例中，正确答案应为 C。

我正在处理的表有数百万条记录。如何找出在每个业务单元下 row_status 都等于 0 的服务代码？

我已经编写了一个查询，但似乎返回了 A、B、C，这是不正确的。

CREATE TABLE #tmpTable (pc_ccs_9xx_service_code VARCHAR(10));
CREATE TABLE #tmpTable1 (pc_ccs_9xx_service_code VARCHAR(10));

INSERT INTO #tmpTable
SELECT TOP 500 
    pcdis.service_code
FROM pc_ccs_9xx_deposit_install_service pcdis
WHERE pcdis.row_status = 1
ORDER BY pcdis.service_code ASC;

SELECT * FROM #tmpTable;

INSERT INTO #tmpTable1
SELECT TOP 500 pcdis.service_code 
FROM pc_ccs_9xx_deposit_install_service pcdis
JOIN #tmpTable tt ON tt.pc_ccs_9xx_service_code = pcdis.service_code AND pcdis.row_status = 1
ORDER BY pcdis.service_code ASC;

SELECT TOP 500 tt.pc_ccs_9xx_service_code 
FROM #tmpTable tt
JOIN #tmpTable1 tt1 ON --tt1.pc_ccs_9xx_service_code = tt.pc_ccs_9xx_service_code AND 
tt1.pc_ccs_9xx_service_code NOT IN (SELECT TOP 100 pc_ccs_9xx_service_code FROM #tmpTable)
ORDER BY tt.pc_ccs_9xx_service_code ASC;

DROP TABLE #tmpTable;
DROP TABLE #tmpTable1;

eumiro · Answer

请尝试使用以下查询：

WITH YOUR_TABLE_DATA (service_code,row_status,business_unit_id)AS
(
  SELECT 'A',   1,  'BU1' UNION ALL
  SELECT 'A' ,  0,  'BU2' UNION ALL
  SELECT 'A' ,  0,  'BU3' UNION ALL
  SELECT 'B',   1,  'BU1' UNION ALL
  SELECT 'B',   0,  'BU2' UNION ALL
  SELECT 'C',   0,  'BU2' UNION ALL
  SELECT 'C' ,  0,  'BU3'
)
SELECT C.service_code
FROM YOUR_TABLE_DATA AS C
GROUP BY C.service_code
HAVING MIN(C.row_status)=MAX(C.row_status)
AND MIN(C.row_status)=0

Erwin Brandstetter · Answer

您可以使用 NOT EXISTS 如下所示：

SELECT t.*
FROM your_table t
WHERE NOT EXISTS
  (SELECT 1 FROM your_table tt 
   WHERE tt.service_code = t.service_code
     AND tt.row_status <> 0);

您也可以使用 GROUP BY 和 HAVING 条件如下：

SELECT t.service_code
FROM your_table t
GROUP BY t.service_code
HAVING COUNT(CASE WHEN t.row_status <> 0 THEN 1 END) = 0;

-- 如果您的 row_status 字段只包含0或正数，则可以使用以下HAVING子句
HAVING SUM(t.row_status) = 0;