选择合并的 SQL 过程

Question

我的程序是：

create procedure "news"
as
select newsdate,COUNT(B.id) as total from news B
where B.newsyear < GETDATE()
Group by B.newsdate

select newsdate,COUNT(B.id) as total from news B 
where B.status='WAITING' and B.cancel='1'
Group by B.newsdate

结果：

newsdate   total
2011       4
2010       8

newsdate   total
2011       2
2010       3

如何合并年份总计以获得此结果集：

newsdate   total
2011       6       {4 + 2}
2010       11      {8 + 3}

Answer 1

试试这个：

select newsdate,COUNT(B.id) as total 
from news B 
where ( B.newsyear < GETDATE() )
or ( B.status='WAITING' and B.cancel='1' )
Group by B.newsdate

Answer 2

使用简单的 or 语句（如果确实是同一个表）：

select newsdate,COUNT(B.id) as total
from news B
where B.newsyear < GETDATE()
   or B.status='WAITING' and B.cancel='1'
Group by B.newsdate

或使用 union all + sum 聚合（如果是不同的表）：

select newsdate, sum(total) as total from (
  select newsdate,COUNT(B.id) as total from news B where B.newsyear < GETDATE()
  Group by B.newsdate
  union all
  select newsdate,COUNT(B.id) as total from news B where B.status='WAITING' and B.cancel='1'
  Group by B.newsdate
  ) as rows
group by newsdate