有这样一个表：
customer_id registration_year
1 2014
2 2012
2 2014
2 2014
3 2012
3 2013
3 2014
2 2014
2 2005
希望得到的结果集是统计各个customer本年度登记的次数以及往年登记的次数（customer_id不重复）：
customer_id 本年度数量统计（2014） 往年数量统计（<2014）
1 1 0
2 3 2
3 1 2
请教有何高效的实现方法，非常感谢！
------解决方案--------------------
declare @currentYear int
select @currentYear = datepart(YYYY, GETDATE());
select customer_id, sum( case when registration_year >= @currentYear then 1 else 0 end) CurrentSum,
sum( case when registration_year < @currentYear then 1 else 0 end) PastSum
from tableName
group by customer_id;
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-06-24 07:40:16
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([customer_id] int,[registration_year] int)
insert [huang]
select 1,2014 union all
select 2,2012 union all
select 2,2014 union all
select 2,2014 union all
select 3,2012 union all
select 3,2013 union all
select 3,2014 union all
select 2,2014 union all
select 2,2005
--------------生成数据--------------------------
DECLARE @year INT --本年
SET @year=(SELECT MAX([registration_year]) from huang )--取表中最大一年
select customer_id,COUNT(CASE WHEN [registration_year]=@year THEN 1 ELSE NULL END )[ 本年度数量统计（2014）],--统计本年没必要大于等于