SQL 需要从列返回范围

Question

我有一个表，其中有一个名为 ID 的整数列，其值可能有间隙(例如 1,2,3,4,7,8,10,14,15,16,20)

我想找到一个查询，在上面的示例中，结果如下:

1-47-81014-1620

=更新=

多亏了下面的代码(它似乎在 SQL Server 中运行良好)，我觉得我非常接近让它在 MS-Access 中工作，这是目标。尽管在我的陈述中我仍然遇到语法错误，但我无法弄清楚...

从中选择值(

SELECT islands.PORTID, CSTR(islands.PORTID ) as val 
FROM MYTABLE islands
WHERE NOT EXISTS (SELECT * FROM MYTABLE t2 WHERE t2.PORTID = islands.PORTID - 1)
AND NOT EXISTS (SELECT * FROM MYTABLE t2 WHERE t2.PORTID = islands.PORTID + 1)

UNION 


SELECT 
    rngStart.PORTID
    ,CSTR(rngStart.PORTID ) + '-' 
        + CSTR(MIN(rngEnd.PORTID)) as val
FROM MYTABLE rngStart 
INNER JOIN MYTABLE checkNext ON checkNext.PORTID = rngStart.PORTID + 1
INNER JOIN
(
    SELECT PORTID 
    FROM MYTABLE tblRangeEnd 
    WHERE NOT EXISTS (SELECT * FROM MYTABLE t2 WHERE t2.PORTID = tblRangeEnd.PORTID + 1)
) rngEnd on rngEnd.PORTID > rngStart.PORTID 
WHERE NOT EXISTS (SELECT * FROM MYTABLE t2 WHERE t2.PORTID = rngStart.PORTID - 1)
GROUP BY rngStart.PORTID

) 作为表按 PORTID ASC 排序

Answer 1

SELECT val FROM
(
    -- Get the islands
    SELECT islands.ID, CAST(islands.ID as varchar(10)) as val 
    FROM @t1 islands
    WHERE NOT EXISTS (SELECT * FROM @t1 t2 WHERE t2.ID = islands.ID - 1)
    AND NOT EXISTS (SELECT * FROM @t1 t2 WHERE t2.ID = islands.ID + 1)

    UNION 
    
    -- Get the ranges
    SELECT 
        rngStart.ID
        ,CAST(rngStart.ID as varchar(10)) + '-' 
            + CAST(MIN(rngEnd.ID) as varchar(10)) as val
    FROM @t1 rngStart 
    INNER JOIN @t1 checkNext ON checkNext.ID = rngStart.ID + 1
    INNER JOIN
    (
        SELECT ID 
        FROM @t1 tblRangeEnd 
        WHERE NOT EXISTS (SELECT * FROM @t1 t2 WHERE t2.ID = tblRangeEnd.ID + 1)
    ) rngEnd on rngEnd.ID > rngStart.ID 
    WHERE NOT EXISTS (SELECT * FROM @t1 t2 WHERE t2.ID = rngStart.ID - 1)
    GROUP BY rngStart.ID
) as tbl
ORDER BY ID ASC

我使用了一个名为 @t1 的表变量，但只需将其替换为您的表名即可。

Here它在行动。

编辑

要在 Access 中进行此操作，您必须稍微更改联接。试试这个:

SELECT val FROM
(
    SELECT islands.PORTID, CSTR(islands.PORTID) as val 
    FROM MYTABLE islands
    WHERE NOT EXISTS 
        (SELECT * FROM MYTABLE t2 WHERE t2.PORTID = islands.PORTID - 1)
    AND NOT EXISTS 
        (SELECT * FROM MYTABLE t2 WHERE t2.PORTID = islands.PORTID + 1)

    UNION 

    SELECT 
         rngStart.PORTID
        ,CSTR(rngStart.PORTID) + '-' + CSTR(MIN(endPORTID)) as val
    FROM MYTABLE rngStart 
    INNER JOIN 
    (
        SELECT checkNext.PORTID as nextPORTID, rngEnd.PORTID as endPORTID
        FROM MYTABLE checkNext
        INNER JOIN
        (
            SELECT rngEnd.PORTID
            FROM MYTABLE rngEnd 
            WHERE NOT EXISTS 
                (SELECT * FROM MYTABLE t2 WHERE t2.PORTID = rngEnd.PORTID + 1)
        ) AS rngEnd on rngEnd.PORTID > checkNext.PORTID - 1
    ) AS checkNext ON checkNext.nextPORTID = rngStart.PORTID + 1
    WHERE NOT EXISTS 
        (SELECT * FROM MYTABLE t2 WHERE t2.PORTID = rngStart.PORTID - 1)
    GROUP BY rngStart.PORTID
) as tbl
ORDER BY PORTID ASC