使用SQLWITH子句然后在一個程序中將其與SELECT陳述句合并

我有一個帶有 nvarchar(max) 型別的 XML 列的表ContactInformationXML。我設法決議此列以CountryMarket使用此查詢獲取我想要的資料：

WITH CastToXML AS (
    SELECT ContactInformationXML, TRY_CAST(ContactInformationXML AS xml) AS x
        FROM Archive_ProgUsers
        WHERE ContactInformationXML IS NOT NULL AND ContactInformationXML <> '' AND ContactInformationXML <> 'Admin - Old Program in Use' AND ContactInformationXML <> 'Admin - Admin Edits'
)
SELECT h.p.value('(/user/contact/@CountryMarket)[1]','nvarchar(max)') AS CountryMarket
    FROM CastToXML
        cross apply x.nodes('/user/contact[1]') AS h(p)

但是，現在我有了該列，我需要將它與同一程序中的另一個查詢組合以顯示一個表。這是第二個查詢：

SELECT DISTINCT
    user_id,
    email,
    app_id,
    ContactInformationXML,
    install_code,
    programStarts
FROM Archive_ProgUsers
INNER JOIN Archive_Installations ON Archive_ProgUsers.id = Archive_Installations.user_id
INNER JOIN (
    SELECT DISTINCT inst_id 
        , COUNT(CASE WHEN Archive_Hits.type <> 'New' AND Archive_Hits.accessed_on BETWEEN CAST(@startdate AS DATE) AND CAST(@enddate AS DATE) THEN 1 END) AS programStarts
    FROM Archive_Hits
    GROUP BY Archive_Hits.inst_id 
) AS hitsCount ON hitsCount.inst_id = Archive_Installations.id
WHERE programStarts > 0
ORDER BY programStarts DESC

我不能使用 UNION 或 UNION ALL 因為我必須在每個串列中有相同數量的運算式。

如何將這 2 個查詢合并到一個程序中，該程序回傳一個如下所示的結果表？

更新 示例 ContactInformationXML 資料：

<user Userid="John Smith"> <contact FirstName="John" LastName="Smith" RegistrationEmailAddress="[email protected]" DisplayEmailAddress="[email protected]" Company="Google" Title="" PhoneNum=" 6666666666" FaxNum="" Address1="example USA, 77" Address2="" City="Earth" StateName="testing;" StateCode="USA" ZipCode="00000" CountryCode="US" CountryMarket="North America" DateModified="2020-03-16T16:35:00"/> </user>

uj5u.com熱心網友回復：

在不知道聯系資訊 XML 的結構的情況下，很難猜測結果應該是什么，但是根據您的查詢，您可以執行以下操作：

With CastToXML AS (
    SELECT user_id, ContactInformationXML, TRY_CAST(ContactInformationXML AS xml) AS x
        FROM Archive_ProgUsers
        WHERE ContactInformationXML IS NOT NULL AND ContactInformationXML <> '' AND ContactInformationXML <> 'Admin - Old Program in Use' AND ContactInformationXML <> 'Admin - Admin Edits'
),
Market AS (
SELECT user_id, h.p.value('(/user/contact/@CountryMarket)[1]','nvarchar(max)') AS CountryMarket
    FROM CastToXML
        cross apply x.nodes('/user/contact[1]') AS h(p)
)
SELECT DISTINCT
    user_id,
    email,
    app_id,
    --ContactInformationXML,
    install_code,
    programStarts,
    CountryMarket
FROM Archive_ProgUsers
INNER JOIN Archive_Installations ON Archive_ProgUsers.id = Archive_Installations.user_id
INNER JOIN (
    SELECT DISTINCT inst_id 
        , COUNT(CASE WHEN Archive_Hits.type <> 'New' AND Archive_Hits.accessed_on BETWEEN CAST(@startdate AS DATE) AND CAST(@enddate AS DATE) THEN 1 END) AS programStarts
    FROM Archive_Hits
    GROUP BY Archive_Hits.inst_id 
) AS hitsCount ON hitsCount.inst_id = Archive_Installations.id
LEFT JOIN Market
  ON Market.user_id = Archive_ProgUsers.user_id
WHERE programStarts > 0
ORDER BY programStarts DESC

uj5u.com熱心網友回復：

看起來您只想從 XML 中提取一個值。因此，您可以非常簡單地做到這一點，只需放入TRY_CASTaCROSS APPLY并.values在其上使用即可。

您也不需要WHERE因為TRY_CAST在這些情況下只會回傳 null

SELECT
    user_id,
    email,
    app_id,
    ContactInformationXML = v.x.value('(/user/contact/@CountryMarket)[1]','nvarchar(max)'),
    install_code,
    programStarts
FROM Archive_ProgUsers
INNER JOIN Archive_Installations ON Archive_ProgUsers.id = Archive_Installations.user_id
INNER JOIN (
    SELECT inst_id 
        , COUNT(CASE WHEN Archive_Hits.type <> 'New' AND Archive_Hits.accessed_on BETWEEN CAST(@startdate AS DATE) AND CAST(@enddate AS DATE) THEN 1 END) AS programStarts
    FROM Archive_Hits
    GROUP BY Archive_Hits.inst_id 
) AS hitsCount ON hitsCount.inst_id = Archive_Installations.id
CROSS APPLY (VALUES (TRY_CAST(ContactInformationXML AS xml)) ) v(x)
WHERE programStarts > 0
ORDER BY programStarts DESC

我建議你重新考慮那些DISTINCTs。它們可能是不必要的。如果您得到重復，您應該仔細查看您的連接。不要只是DISTINCT為了讓重復的東西消失而扔掉它。

標籤：sql sql-server 存储过程 公用表表达式

上一篇：如何在Col3中顯示值，其中Col1值為真，而對于Col1中的假值，如何僅在Col3中顯示NULL

下一篇：從ArcGIS連接SQLServer失敗