使用SQL子句，然后在一个过程中将它与SELECT语句合并
使用SQL子句，然后在一个过程中将它与SELECT语句合并
提问于 2021-10-20 18:22:23
我有一个带有nvarchar(max) ContactInformationXML类型XML列的表。我成功地解析了这个列，以获得我想要的数据，使用这个查询获得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，因为每个列表中必须有相同数量的表达式。
如何将这两个查询合并到一个返回类似于此的结果表的过程中？
更新示例ContactInformationXML数据：
<user Userid="John Smith"> <contact FirstName="John" LastName="Smith" RegistrationEmailAddress="j.smith@example.com" DisplayEmailAddress="j.smith@example.com" 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>回答 2
Stack Overflow用户
回答已采纳
发布于 2021-10-21 00:57:18
如果不知道联系信息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 DESCStack Overflow用户
发布于 2021-10-21 15:21:03
看起来，您只需要从XML中提取一个值。因此，您可以非常简单地通过将TRY_CAST放置在CROSS 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我建议你重新考虑那些DISTINCT，它们可能是不必要的。如果您正在获得副本，则应仔细查看您的连接。不要把DISTINCT扔在上面，让重复的东西消失。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/69651199
复制相关文章
相似问题
腾讯云开发者
