SQL—外部表中要在主表中使用的所有值的关联

对于SQL Server 2005及更高版本,您可以使用 method

在里面 MySQL :

SELECT  s.*,
        GROUP_CONCAT(detail)
FROM    sales s
JOIN    details d
ON      d.sale_id = s.id
GROUP BY
        s.id

在里面 SQL Server :

SELECT  s.*,
        (
        SELECT  CASE WHEN ROW_NUMBER() OVER (ORDER BY sale_id) = 1 THEN ', ' ELSE '' END + detail
        FROM    details d
        WHERE   d.sale_id = s.id
        FOR XML PATH('')
        )
FROM    sales s

在里面 PostgreSQL

SELECT  s.*,
        ARRAY_TO_STRING (
        ARRAY
        (
        SELECT  detail
        FROM    details d
        WHERE   d.sale_id = s.id
        ), ', ')
        )
FROM    sales s

在里面 Oracle :

WITH q AS (
        SELECT  d.sale_id, d.detail,
                ROW_NUMBER() OVER (PARTITION BY d.sale_id ORDER BY d.detail) AS rn
        FROM    sales s
        JOIN    details d
        ON      d.sale_id = s.id
        )
SELECT  *
FROM    (
        SELECT  *
        FROM    q
        MODEL
        PARTITION BY
                (sale_id)
        DIMENSION BY
                (rn)
        MEASURES
                (detail, detail AS group_concat, 0 AS mark)
        RULES UPDATE (
                group_concat[rn > 1] =  group_concat[CV() - 1] || ', ' || detail[CV()],
                mark[ANY] = PRESENTV(mark[CV() + 1], 0, 1)
                )
        )
WHERE   mark = 1
ORDER BY
        id