SQL order by on UNION

Question

我有一个问题如下:

SELECT CONVERT(VARCHAR(10),[start_of_week],103) AS WEEK_START, VisitorCount FROM mytable2
UNION 
SELECT 'Average' AS WEEK_START,AVG(VisitorCount) from mytable2
ORDER BY WEEK_START

如果我运行最上面的查询，我会得到所有排序的 WEEK_START，但是当我联合查询“平均”时，排序顺序会变得困惑。谁能告诉我如何对所有 WEEK_Start 进行排序并将“平均”放在最后？

更新完整的查询是

DECLARE @From DATE = '2015-07-01'
DECLARE @To DATE = (SELECT GETDATE())
DECLARE @dt DATE = '1905-01-02';
WITH mytable as(
SELECT 

    1 as visitor,
    DATEDIFF(week, '', date) AS WeekNumber,
    DATEADD(WEEK, DATEDIFF(WEEK, @dt, [Date]), @dt) AS [start_of_week]
    FROM Showroom_perf
    Where [Date] <= @To

) , mytable2 AS ( 
Select 
[start_of_week],
COUNT(visitor) as VisitorCount
from mytable WHERE WeekNumber > '0'
GROUP BY
[start_of_week]

)
SELECT CONVERT(VARCHAR(10),[start_of_week],103) AS WEEK_START, VisitorCount, 1 as tag FROM mytable2 
UNION 
SELECT 'Average' , AVG(VisitorCount), 2 as tag  from mytable2

下面是我看到的结果:

Answer 1

更好的方法

使用ROLLUP

SELECT CASE
         WHEN Grouping(CONVERT(VARCHAR(10), start_of_week, 103)) = 1 THEN 'Average'
         ELSE CONVERT(VARCHAR(10), start_of_week, 103)
       END AS WEEK_START,
       Avg(VisitorCount)
FROM   Yourtable
GROUP  BY CONVERT(VARCHAR(10), start_of_week, 103) WITH rollup 
Order by start_of_week

• DEMO