使用String_AGG在SQL中使用條件查詢？

我有 3 個表的關系是 Policy (N) -> PolicyService <- Service (N)：

DXBusinessPolicy_Policy

DXBusinessPolicy_Service

DXBusinessPolicy_PolicyService

問題：輸入輸入服務（ServiceCode），輸出是PolicyID、PolicyCode、PolicyName和該策略的服務串列（串列ServiceCode用“，”連接）。

例如：我的輸入是：“INT”。結果期望：

我試圖解決這個問題如下：

ALTER PROC FindPolicyByService
    @ServiceCode varchar(200)
AS 
BEGIN
    SELECT dbo.DXBusinessPolicy_Policy.ID AS PolicyID,
           dbo.DXBusinessPolicy_Policy.Code AS PolicyCode,
           dbo.DXBusinessPolicy_Policy.Name AS PolicyName,
           STRING_AGG(dbo.DXBusinessPolicy_Service.Code, ',') 
    FROM dbo.DXBusinessPolicy_Policy 
            join dbo.DXBusinessPolicy_PolicyService ON dbo.DXBusinessPolicy_Policy.ID = dbo.DXBusinessPolicy_PolicyService.PolicyID
            join dbo.DXBusinessPolicy_Service ON dbo.DXBusinessPolicy_PolicyService.ServiceID = dbo.DXBusinessPolicy_Service.ID
    WHERE dbo.DXBusinessPolicy_Service.Code = @ServiceCode
    GROUP by dbo.DXBusinessPolicy_Policy.ID, dbo.DXBusinessPolicy_Policy.Code, dbo.DXBusinessPolicy_Policy.Name
END

exec FindPolicyByService "INT"

但結果并不是我所期望的

uj5u.com熱心網友回復：

我終于找到了對我的問題最準確的答案。謝謝大家！最好的解決辦法是：

ALTER PROC FindPolicyByService
    @codes varchar(200)
AS 
BEGIN
   SELECT p.ID AS PolicyID,
       p.Code AS PolicyCode,
       p.Name AS PolicyName,
       STRING_AGG(s.Code, ',') AS ServiceCode
    FROM dbo.DXBusinessPolicy_Policy AS p
    JOIN dbo.DXBusinessPolicy_PolicyService AS ps ON p.ID = ps.PolicyID
    JOIN dbo.DXBusinessPolicy_Service AS s ON ps.ServiceID = s.ID
    WHERE p.ID IN 
    (
        SELECT subps.PolicyID
        FROM dbo.DXBusinessPolicy_PolicyService AS subps
        JOIN dbo.DXBusinessPolicy_Service AS subs ON subps.ServiceID = subs.ID
        WHERE subs.Code = @ServiceCode
    )
    GROUP by p.ID, p.Code, p.Name
END

或其他解決方案：

ALTER PROC FindPolicyByService
    @ServiceCode varchar(200)
AS 
BEGIN
    SELECT DIstinct policy.ID AS PolicyID,
           policy.Code AS PolicyCode,
           policy.Name AS PolicyName,
           (SELECT STRING_AGG(tempService.Code, ',')  FROM dbo.DXBusinessPolicy_Policy tempPolicy
                 JOIN dbo.DXBusinessPolicy_PolicyService tempPolicyService
                    ON tempPolicy.ID = tempPolicyService.PolicyID 
                 JOIN dbo.DXBusinessPolicy_Service tempService
                    ON tempPolicyService.ServiceID = tempService.ID
            WHERE policyservice.PolicyID = PolicyID) AS ServiceCode
    FROM dbo.DXBusinessPolicy_Policy policy
     JOIN dbo.DXBusinessPolicy_PolicyService policyservice
        ON policy.ID = policyservice.PolicyID 
     JOIN dbo.DXBusinessPolicy_Service service
        ON policyservice.ServiceID = service.ID AND service.Code = @ServiceCode
    GROUP BY
        policy.ID,
        policyservice.PolicyID,
        policy.Code,
        policy.Name
END

這一切都給了我與預期相同的結果：

uj5u.com熱心網友回復：

我嘗試更新我的商店，例如：

ALTER PROC FindPolicyByService
    @ServiceCode varchar(200)
AS 
BEGIN
    SELECT policy.ID AS PolicyID,
           policy.Code AS PolicyCode,
           policy.Name AS PolicyName,
           STRING_AGG(service.Code, ',') 
    FROM dbo.DXBusinessPolicy_Policy policy
    LEFT JOIN dbo.DXBusinessPolicy_PolicyService policyservice
        ON policy.ID = policyservice.PolicyID
    LEFT JOIN dbo.DXBusinessPolicy_Service service
        ON policyservice.ServiceID = service.ID AND
           service.Code = @ServiceCode
    GROUP BY
        policy.ID,
        policy.Code,
        policy.Name
END

exec FindPolicyByService "INT"

我得到的結果：

看起來service.Code = @codes條件不起作用。

我預期的結果：

我也試過不使用service.Code = @codes條件

這個結果接近我的預期，剩下的我只想得到包括的服務 INT

標籤：sql sql-server 查询语句 存储过程 字符串聚合

上一篇：SQLServer：替換為NULL

下一篇：兩個日期之間的SQL總和數量