在sql server中，是否有方法在情况下选择多个值？

Question

我有以下查询

    SELECT
        ip.ITEMID AS ItemID,
        ip.CoUkScreenName AS Name,
        CASE 
            WHEN
                (SELECT COUNT(*) FROM 
                    (SELECT c.CarbonFP, c.Unit, c.Methodology FROM [dbo].[vw_carbon_ChildProducts] AS c
                        WHERE c.ParentItemID = ip.ITEMID
                        GROUP BY c.CarbonFP, c.Unit, c.Methodology
                    ) AS CountResult
                ) = 1
            THEN 
                (SELECT TOP 1 c.CarbonFP FROM [dbo].[vw_carbon_ChildProducts] AS c WHERE c.ParentItemID = ip.ITEMID)
            ELSE
                NULL
        END AS CarbonFP,
        CASE 
            WHEN
                (SELECT COUNT(*) FROM 
                    (SELECT c.CarbonFP, c.Unit, c.Methodology FROM [dbo].[vw_carbon_ChildProducts] AS c
                        WHERE c.ParentItemID = ip.ITEMID
                        GROUP BY c.CarbonFP, c.Unit, c.Methodology
                    ) AS CountResult
                ) = 1
            THEN 
                (SELECT TOP 1 c.Unit FROM [dbo].[vw_carbon_ChildProducts] AS c WHERE c.ParentItemID = ip.ITEMID)
            ELSE
                NULL
        END AS Unit,
        CASE 
            WHEN
                (SELECT COUNT(*) FROM 
                    (SELECT c.CarbonFP, c.Unit, c.Methodology FROM [dbo].[vw_carbon_ChildProducts] AS c
                        WHERE c.ParentItemID = ip.ITEMID
                        GROUP BY c.CarbonFP, c.Unit, c.Methodology
                    ) AS CountResult
                ) = 1
            THEN 
                (SELECT TOP 1 c.Methodology FROM [dbo].[vw_carbon_ChildProducts] AS c WHERE c.ParentItemID = ip.ITEMID)
            ELSE
                NULL
        END AS Methodology
    FROM
        MARCMSITEMPRESENTATION ip
        INNER JOIN (SELECT DISTINCT (cp.ParentItemID) FROM [dbo].[vw_carbon_ChildProducts] AS cp) AS p ON p.ParentItemID = ip.ITEMID

这很好，但我想知道是否有可能在相同的情况下选择所有的3个值，而不是一次只选择一个值，或者是否有更好的方法来构造这个查询，因为它看起来效率低下(我在查询中没有当前的问题--只是考虑可伸缩性)，必须执行相同的查询三次才能得到这三个值。

Answer 1

使用OUTER APPLY

SELECT . . .,
       c.*
FROM MARCMSITEMPRESENTATION ip INNER JOIN
     (SELECT DISTINCT cp.ParentItemID
      FROM [dbo].[vw_carbon_ChildProducts] AS cp
     ) p
     ON p.ParentItemID = ip.ITEMID OUTER APPLY
     (SELECT c.*
      FROM [dbo].[vw_carbon_ChildProducts] c
      WHERE c.ParentItemID = ip.ITEMID
     ) c

通常，如果没有TOP，就不应该使用ORDER BY。在您的原始版本中，这三列可能来自不同的行。在这个版本中，所有三列都来自同一行，但哪一行是不确定的。

编辑：

只有当计数为1时才匹配：

SELECT . . .,
       c.*
FROM MARCMSITEMPRESENTATION ip INNER JOIN
     (SELECT DISTINCT cp.ParentItemID
      FROM [dbo].[vw_carbon_ChildProducts] AS cp
     ) p
     ON p.ParentItemID = ip.ITEMID OUTER APPLY
     (SELECT c.CarbonFP, c.Unit, c.Methodology
      FROM [dbo].[vw_carbon_ChildProducts] c
      WHERE c.ParentItemID = ip.ITEMID 
      GROUP BY c.CarbonFP, c.Unit, c.Methodology
      HAVING COUNT(*) = 1
     ) c

Answer 2

感谢戈登给我指明了正确的方向。我使用以下查询成功地获得了相同的结果集(不确定它是否更有效，但它看起来更好，因为它没有重复查询)：

SELECT 
    ip.ITEMID,
    ip.CoUkScreenName AS Name,
    u.CarbonFP, 
    u.Unit, 
    u.Methodology
FROM MARCMSITEMPRESENTATION ip 
INNER JOIN
    (SELECT 
        DISTINCT cp.ParentItemID
    FROM 
        [dbo].[vw_carbon_ChildProducts] AS cp
    ) AS p ON p.ParentItemID = ip.ITEMID 
LEFT OUTER JOIN
    (SELECT 
        c.ParentItemID, 
        c.CarbonFP, 
        c.Unit, 
        c.Methodology
    FROM 
        [dbo].[vw_carbon_ChildProducts] AS c
    INNER JOIN 
        (
            SELECT 
                p.ParentItemID 
            FROM
                (SELECT 
                    ParentItemID, 
                    CarbonFP, 
                    Unit, 
                    Methodology
                FROM 
                    [dbo].[vw_carbon_ChildProducts]
                GROUP BY 
                    ParentItemID, 
                    CarbonFP, 
                    Unit, 
                    Methodology) AS p
            GROUP BY 
                p.ParentItemID
            HAVING COUNT(p.ParentItemID) = 1
        )  AS f ON f.ParentItemID = c.ParentItemID
    GROUP BY 
        c.ParentItemID, 
        c.CarbonFP, 
        c.Unit, 
        c.Methodology
     ) AS u ON u.ParentItemID = ip.ITEMID