SQL单例查询（Singleton Select）返回多行报错问题求助

Hey there! Let's break down why you're hitting this error and walk through how to fix it.

What's causing the error? #

The "Multiple Rows in Singleton Select" error happens because the subquery you're using to fetch the HOUSE value is designed to return exactly one row and one column for each row from your main SAAIO_GUIAS (G) table. Right now, though, there are multiple rows in SAAIO_GUIAS that match the conditions G.NUM_REFE = H.NUM_REFE AND H.IDE_MH = 'H' AND H.CONS_GUIA = '1' for at least one NUM_REFE—so the subquery spits out more than one value, which the database can't handle in that position.

Solution Options #

Here are a few ways to resolve this, depending on your actual business needs:

1. If each MASTER should only have one corresponding HOUSE

First, check your data for duplicates. There might be multiple H type records with the same NUM_REFE and CONS_GUIA = '1'. Run this query to identify problematic NUM_REFE values:

SELECT NUM_REFE, COUNT(*)
FROM SAAIO_GUIAS
WHERE IDE_MH = 'H' AND CONS_GUIA = '1'
GROUP BY NUM_REFE
HAVING COUNT(*) > 1;

Once you find these duplicates, you'll need to clean up the data (delete or update extra rows) to align with your expected business logic.

2. If multiple HOUSE records per MASTER are allowed

If your business expects one MASTER to have multiple HOUSE entries, adjust your query to handle this scenario:

Option A: Use an aggregate function to return a single value

Pick an aggregate like MAX(), MIN(), or FIRST_VALUE() to grab one representative GUIA from the matching rows. For example:

WITH G1 AS (
    SELECT 
        G.NUM_REFE, 
        G.GUIA AS MASTER, 
        -- Choose MAX or MIN based on which value makes sense for your use case
        (SELECT MAX(H.GUIA)
         FROM SAAIO_GUIAS H 
         WHERE G.NUM_REFE = H.NUM_REFE 
           AND H.IDE_MH = 'H' 
           AND H.CONS_GUIA = '1') AS HOUSE 
    FROM SAAIO_GUIAS G 
    WHERE G.IDE_MH = 'M' 
      AND G.CONS_GUIA = '1'
) 
SELECT * FROM G1;

Option B: Rewrite with a JOIN to return all matching rows

If you want to see every HOUSE associated with a MASTER (even if there are multiple), replace the subquery with a LEFT JOIN (use INNER JOIN if you only want MASTERs that have at least one HOUSE):

SELECT 
    G.NUM_REFE, 
    G.GUIA AS MASTER, 
    H.GUIA AS HOUSE
FROM SAAIO_GUIAS G
LEFT JOIN SAAIO_GUIAS H 
    ON G.NUM_REFE = H.NUM_REFE 
    AND H.IDE_MH = 'H' 
    AND H.CONS_GUIA = '1'
WHERE G.IDE_MH = 'M' 
  AND G.CONS_GUIA = '1';

If you need to combine multiple HOUSE values into a single comma-separated string, check if your database supports string aggregation functions like LISTAGG() (Oracle) or STRING_AGG() (SQL Server). For example, in Oracle:

SELECT 
    G.NUM_REFE, 
    G.GUIA AS MASTER, 
    LISTAGG(H.GUIA, ', ') WITHIN GROUP (ORDER BY H.GUIA) AS HOUSE_LIST
FROM SAAIO_GUIAS G
LEFT JOIN SAAIO_GUIAS H 
    ON G.NUM_REFE = H.NUM_REFE 
    AND H.IDE_MH = 'H' 
    AND H.CONS_GUIA = '1'
WHERE G.IDE_MH = 'M' 
  AND G.CONS_GUIA = '1'
GROUP BY G.NUM_REFE, G.GUIA;

内容的提问来源于stack exchange，提问作者Eduardo