求一个存储过程，急呀！！！
有一个表UserCodes 列Code:CHAR(11),Used:BIT
测试数据
Code Used
668CT0A5M6P1
668MLOWTVCY0
668S9KNXU610
868EMG6LXMP1
868LER010TP0
868M66JTULO0
868RS1H9K8V1
968K122UKXG1
968V1IAD0B71
Code的前三位是号码段，统计出每个号码段的总数、使用的数量和未使用的数量，以及三项的合计
DB高手，我来了，你在哪里?
[解决办法]
我假设0为未使用
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-05-22 14:31:49
-- 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.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------
--> 测试数据：[UserCodes]
if object_id('[UserCodes]') is not null drop table [UserCodes]
go
create table [UserCodes]([Code] varchar(11),[Used] int)
insert [UserCodes]
select '668CT0A5M6P',1 union all
select '668MLOWTVCY',0 union all
select '668S9KNXU61',0 union all
select '868EMG6LXMP',1 union all
select '868LER010TP',0 union all
select '868M66JTULO',0 union all
select '868RS1H9K8V',1 union all
select '968K122UKXG',1 union all
select '968V1IAD0B7',1
--------------开始查询--------------------------
select LEFT([code],3) 号码段,COUNT(1)[号码段总数],SUM(CASE WHEN USED=0 THEN 1 ELSE 0 END )[未使用数量],
SUM(CASE WHEN USED=1 THEN 1 ELSE 0 END )[使用数量]
from [UserCodes]
GROUP BY LEFT([code],3)
----------------结果----------------------------
/*
号码段 号码段总数 未使用数量 使用数量
------ ----------- ----------- -----------
668 3 2 1
868 4 2 2
968 2 0 2
*/
with tb(a,b) as (
select '668CT0A5M6P', 1 union all
select '668MLOWTVCY', 0 union all
select '668S9KNXU61', 0 union all
select '868EMG6LXMP', 1 union all
select '868LER010TP', 0 union all
select '868M66JTULO', 0 union all
select '868RS1H9K8V', 1 union all
select '968K122UKXG', 1 union all
select '968V1IAD0B7', 1)
select left(a,3),count(1),
count(case when b=1 then 1 else null end),
count(case when b=0 then 1 else null end)
from tb a
group by left(a,3)
union all
select distinct '总计',(select count(1) from tb),(select count(1) from tb where b=1)
,(select count(1) from tb where b=0) from tb
