在T-sql中使用FOR XML的Order By问题（ORDER BY子句在视图，内联函数，派生表中无效）

select a.Hall, a.Title, 
  STUFF((SELECT ', ' + '[' + CONVERT(varchar(2),DATEPART(Hour, b.StartFilm))  
  + ':' + CONVERT(varchar(2),DATEPART(Minute, b.StartFilm)) 
  + ' ' + CONVERT(varchar(2),DATEPART(Hour, b.EndTime))  
  + ':' + CONVERT(varchar(2),DATEPART(Minute, b.EndTime)) 
  + ']' 
FROM (select c.Name as Hall, b.Title, 
    Convert(time,a.StartFilmTime) as StartFilm,  
    Convert(time,a.EndFilmTime) as EndTime
  from FilmSchedule a 
    left join Film b on a.FilmId = b.Id 
    left join Room c on a.RoomId = c.Id 
  where a.ApproveStatus = 1 and a.Status = 1 
        and CONVERT(date, a.StartFilmTime) =  '05-06-2015'
) b 
Where a.Hall = b.Hall and a.Title = b.Title 
FOR XML PATH('')),1,1,'') As ShowTime  
from (select c.Name as Hall, b.Title, 
  Convert(time,a.StartFilmTime) as StartFilm,  
  Convert(time,a.EndFilmTime) as EndTime
  from FilmSchedule a 
  left join Film b on a.FilmId = b.Id 
  left join Room c on a.RoomId = c.Id 
  where a.ApproveStatus = 1 and a.Status = 1 
     and CONVERT(date, a.StartFilmTime) =  '05-06-2015'
  Order by a.StartFilmTime
) a 
group by a.Hall, a.Title

I get the error:

我收到错误:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

除非还指定了TOP或FOR XML,否则ORDER BY子句在视图,内联函数,派生表,子查询和公用表表达式中无效。

Help please! (I have used FOR XML?)

请帮忙! (我用过FOR XML?)

1 个解决方案

#1

Although your query does use FOR XML (for the GROUP_CONCAT workaround), you are applying the order by outside of the derived table that uses FOR XML, hence the error.

虽然您的查询确实使用FOR XML(对于GROUP_CONCAT变通方法),但您在使用FOR XML的派生表外部应用顺序,因此出现错误。

Given that you aren't including start date directly in the final select (although you are composing it as part of the STUFF ShowTime column), you also can't ORDER BY StartFilm in the final GROUP BY either, as the column would otherwise need to be included in the GROUP BY or as an aggregated column.

鉴于您没有直接在最终选择中包含开始日期(尽管您将其作为STUFF ShowTime列的一部分进行编写),您也不能在最终GROUP BY中对ORDER BY StartFilm进行编辑,因为该列将需要包含在GROUP BY中或作为聚合列。

What you can do is move the ORDER BY into the STUFF and then order by the derived column ShowTime (since your query only runs for one given day, and StartFilmTime is the first part of the STUFFED composed column).

您可以做的是将ORDER BY移动到STUFF中,然后按派生列ShowTime排序(因为您的查询仅运行一天,StartFilmTime是STUFFED组合列的第一部分)。

At the same time, I would DRY up the repetition on the derived table with a CTE:

与此同时,我会用CTE干掉派生表上的重复:

WITH cteFiltered AS
    (select c.Name as Hall, b.Title, 
        Convert(time,a.StartFilmTime) as StartFilm,  
        Convert(time,a.EndFilmTime) as EndTime
    from FilmSchedule a 
        left join Film b on a.FilmId = b.Id 
        left join Room c on a.RoomId = c.Id 
    where a.ApproveStatus = 1 and a.Status = 1 
          and CONVERT(date, a.StartFilmTime) =  '05-06-2015'
    )
select 
    a.Hall, 
    a.Title, 
    STUFF((SELECT ', ' + '[' + CONVERT(varchar(2),DATEPART(Hour, b.StartFilm))  
    + ':' + CONVERT(varchar(2),DATEPART(Minute, b.StartFilm)) 
    + ' ' + CONVERT(varchar(2),DATEPART(Hour, b.EndTime))  
    + ':' + CONVERT(varchar(2),DATEPART(Minute, b.EndTime)) 
    + ']' 
        FROM
            cteFiltered b
        Where 
            a.Hall = b.Hall and a.Title = b.Title 
        order by b.StartFilm -- ***
        FOR XML PATH('')),1,1,'') As ShowTime  
from 
    cteFiltered a 
group by a.Hall, a.Title
order by ShowTime; -- *** Hour is first (assuming 24H format) and only one day

#1