SQL Server中如何让Union生成的小计置于对应CompanyID末尾？

我在SQL Server里用Union语句计算小计，已经成功生成小计，但小计没出现在对应CompanyID数据行的末尾，而是混在中间。想让每个CompanyID的小计行显示在该公司所有数据行的最后。

当前使用的SQL代码：

select distinct 
    Personid, 
    concat(rtrim(LastName),+ ', ',+ rtrim(FirstName)) as PersonName, 
    CompanyName, CompanyId, OrderId, JobTitle, 
    cast(TSPayRate as varchar) as PayRate, 
    cast(TSBillRate as varchar) as BillRate, 
    cast(RegHours as varchar) as TotalRegHours, 
    cast(OTHours as varchar) as TotalOTHours  
from
    timesheets TSV
where 
    CompanyId in ('000000001', '000000002') 
--group by Personid, Concat(rtrim(LastName),+ ', ',+ rtrim(FirstName)), CompanyName, CompanyId, orderid,JobTitle, PayRate, BillRate, reghours, othours

union all

select distinct  
    '  ', ' ', ' ', companyid, ' ', ' ', ' ', 
    cast('SUBTOTAL' as varchar),
    sum(RegHours) as TotalRegHours, 
    sum(OTHours) as TotalOTHours  
from
    timesheets TSV
where 
    CompanyId in ('000000001', '000000002')     
group by 
    companyid
order by 
    companyid 

当前结果 #

小计行混在对应CompanyID的数据行中间，未排在该公司数据的末尾。

预期结果 #

每个CompanyID的所有数据行展示完毕后，紧接着显示该公司的小计行。

核心思路是给数据行和小计行添加排序标识字段，让数据行优先排序，小计行排在后面。修改后的代码如下：

select 
    Personid, 
    PersonName, 
    CompanyName, 
    CompanyId, 
    OrderId, 
    JobTitle, 
    PayRate, 
    BillRate, 
    TotalRegHours, 
    TotalOTHours,
    SortKey = 1  -- 数据行标识，排序优先级高
from (
    select distinct 
        Personid, 
        concat(rtrim(LastName), ', ', rtrim(FirstName)) as PersonName, 
        CompanyName, 
        CompanyId, 
        OrderId, 
        JobTitle, 
        cast(TSPayRate as varchar) as PayRate, 
        cast(TSBillRate as varchar) as BillRate, 
        cast(RegHours as varchar) as TotalRegHours, 
        cast(OTHours as varchar) as TotalOTHours  
    from timesheets TSV
    where CompanyId in ('000000001', '000000002')
) t

union all

select 
    '  ' as Personid, 
    ' ' as PersonName, 
    ' ' as CompanyName, 
    companyid, 
    ' ' as OrderId, 
    ' ' as JobTitle, 
    ' ' as PayRate, 
    'SUBTOTAL' as BillRate,
    cast(sum(RegHours) as varchar) as TotalRegHours, 
    cast(sum(OTHours) as varchar) as TotalOTHours,
    SortKey = 2  -- 小计行标识，排序优先级低
from timesheets TSV
where CompanyId in ('000000001', '000000002')     
group by companyid

order by CompanyId, SortKey  -- 先按公司分组，再按标识排序

说明 #

1. 给两个查询分别添加SortKey字段：数据行设为1，小计行设为2，确保排序时数据行在前。
2. 最终排序规则改为CompanyId, SortKey，先按公司分组，再按标识升序，让每个公司的小计行紧跟在数据行之后。
3. 修正了原代码中concat函数的多余+号，同时将小计行的TotalRegHours和TotalOTHours转为varchar，保证与数据行字段类型一致，避免Union时的类型冲突。

内容的提问来源于stack exchange，提问作者jin