时间戳

首页 > 解决方案 > 在视图中使用子查询会导致 SSMS 显示错误

sql - 在视图中使用子查询会导致 SSMS 显示错误

问题描述

我正在处理一个巨大的存储过程，它可以工作但有点笨拙，我正试图退休并改用视图。

我有以下由 SSMS 的视图设计器生成的 SQL

SELECT        
    dbo.Trip.Title AS Heading,
    dbo.Trip.Description, 
    dbo.Trip.TripLengthDays AS DayAmount, 
    dbo.TripDetail.PriceTo, 
    dbo.City.Name AS CityName, 
    dbo.Country.Name AS CountryName
FROM
    dbo.Trip 
INNER JOIN
    dbo.TripDetail ON dbo.Trip.ID = dbo.TripDetail.TripID 
INNER JOIN
    dbo.TripCity ON dbo.Trip.ID = dbo.TripCity.TripID 
INNER JOIN
    dbo.City ON dbo.TripCity.CityID = dbo.City.ID 
INNER JOIN
    dbo.Country ON dbo.City.CountryID = dbo.Country.ID

为了清楚起见，我将其截断了一点。

当我在 SQL 上移动以生成 XML 字符串时，当我将它传递给设计者时会感到不安......

SELECT        
    dbo.Trip.Title AS Heading,
    dbo.Trip.Description, 
    dbo.Trip.TripLengthDays AS DayAmount, 
    dbo.TripDetail.PriceTo, 
    dbo.City.Name AS CityName, 
    dbo.Country.Name AS CountryName,
    (SELECT e.Id, e.Name AS [ActivityName]
     FROM [dbo].[MyDatabase] e
     INNER JOIN [trip].[ActivityLookup] l ON l.ActivityId = e.Id
     WHERE Trip.id = l.TripId
     FOR XML PATH('Activity'), ROOT('Activities')) AS ActivitiesJson
FROM            
    dbo.Trip 
INNER JOIN
    dbo.TripDetail ON dbo.Trip.ID = dbo.TripDetail.TripID 
INNER JOIN
    dbo.TripCity ON dbo.Trip.ID = dbo.TripCity.TripID 
INNER JOIN
    dbo.City ON dbo.TripCity.CityID = dbo.City.ID 
INNER JOIN
    dbo.Country ON dbo.City.CountryID = dbo.Country.ID

SSMMS 警报框

鉴于这个相同的 SQL 块在设计器之外工作，我想知道是否有什么我特别需要做的事情来让视图以与存储过程相同的方式返回这个字符串？

标签： sqlsql-serverxmlviewsubquery

解决方案

尝试将您的子查询移动到OUTER APPLY

SELECT        
    dbo.Trip.Title AS Heading,
    dbo.Trip.Description, 
    dbo.Trip.TripLengthDays AS DayAmount, 
    dbo.TripDetail.PriceTo, 
    dbo.City.Name AS CityName, 
    dbo.Country.Name AS CountryName,
    Activities.[Json] AS ActivitiesJson
FROM            
    dbo.Trip 
INNER JOIN
    dbo.TripDetail ON dbo.Trip.ID = dbo.TripDetail.TripID 
INNER JOIN
    dbo.TripCity ON dbo.Trip.ID = dbo.TripCity.TripID 
INNER JOIN
    dbo.City ON dbo.TripCity.CityID = dbo.City.ID 
INNER JOIN
    dbo.Country ON dbo.City.CountryID = dbo.Country.ID
OUTER APPLY (

    SELECT (
    
        SELECT 
            e.Id, e.[Name] AS [ActivityName]
        FROM [dbo].[MyDatabase] e
        INNER JOIN [trip].[ActivityLookup] l ON l.ActivityId = e.Id
        WHERE 
            e.Trip.id = l.TripId
        FOR XML PATH('Activity'), ROOT('Activities')
    
    ) AS [Json]

) AS Activities

但是，我对您为什么将 XML 命名为 JSON 感到有些困惑。

推荐阅读