sql - 对所有结果进行分组 SQL Server

Question

我是 SQL Server 新手。我有一个 SQL 查询，我执行了所有联合操作，两个单独的查询有分组依据。

select top 5  
    Starttime, convert(date,row_date) as Date,
    sum(acdcalls + abncalls) [Offered],
    sum(acdcalls) [Handled],
    sum(abncalls) [Abandoned],
    sum(acdcalls1 + acdcalls2 + acdcalls3 + acdcalls4 + acdcalls5) [Answered within SLA],
    case 
        when sum(acdcalls) != 0 
          then cast((sum(acdcalls1 + acdcalls2 + acdcalls3 + acdcalls4 + acdcalls5)) * 1.0 / sum((acdcalls)) * 1.0 * 100 as decimal(10, 2)) 
          else 0 
    end as [SLA in %]
from 
    db1
where 
    row_date = getdate()
group by 
    Starttime, row_Date

union all

select top 5 
    Starttime, convert(date,row_date) as Date,
    sum(acdcalls + abncalls) [Offered],
    sum(acdcalls) [Handled],
    sum(abncalls) [Abandoned],
    sum(acdcalls1 + acdcalls2 + acdcalls3 + acdcalls4 + acdcalls5) [Answered within SLA],
    case 
       when sum(acdcalls) != 0 
         then cast((sum(acdcalls1 + acdcalls2 + acdcalls3 + acdcalls4 + acdcalls5)) * 1.0 / sum((acdcalls)) * 1.0 * 100 as decimal(10, 2)) 
         else 0 
    end as [SLA in %]
from 
    db2
where 
    row_date = getdate()
group by 
    Starttime, row_Date

Starttime 列具有通用值。我想对结果进行分组 Starttime 。我怎样才能做到这一点？任何帮助将不胜感激

Answer 1

您需要先进行并集，然后再进行聚合。以下示例使用子查询，但如果您愿意，也可以使用临时表:

Select StartTime, Row_Date, sum(acdcalls+abncalls)...[other sums here]
  From (
    select * from db1 
    union all
    select * from db2
  ) a
group by StartTime, RowDate

如果需要，您仍然可以在子查询中包含 where 子句和特定列(上面的示例仅在 db1 和 db2 具有相同顺序的相同列时才有效 - 否则您将需要指定您的列)。不过，如果您将两个选择限制为 Row_Date = GetDate()，我不确定为什么要按 Row_Date 分组。