如何在Group嵌套Group?

Question

直接用例子说我的需求吧：
假设我有数据表：
ID  A1(int)   A2(int)
1   11       100
2   12       100
3   21       100 
4   22       100 
5   22       100
6   23       100
7   11       101
8   22       101
9   12       101我需要统计 每个不同的A2里面有多少个A1(这个直接用group就可以)，其中A1个位为1的有多少个，为2的有多少个，十位为1的有多少个，十位为2的又有多少个,依次到9，结果类似这样：
A2  Total_A1  Total_A1_01 Total_A1_02 ...   Total_A1_10 Total_A1_20 ...
100 6         2           3                  2           4
101 3         1           2                  2           1我不想用循环计算太多次，希望能用一条sql搜索实现，请教这个sql该怎么写？

Answer 1

with T as (select A2,right(A1,1),count(1)  from TB group by A2);
select A2, (case then ....end )   from T   grop by A2

Answer 2

select A2,Total_A1=count(*),
       Total_A1_01=sum(case when A1%10=1 then 1 else 0 end),
       Total_A1_02=sum(case when A1%10=2 then 1 else 0 end),
       .............
       Total_A1_10=sum(case when A1/10=1 then 1 else 0 end),
       Total_A1_20=sum(case when A1/10=2 then 1 else 0 end),
       .............       
from tb
group by A2

Answer 3

--用case when实现
SELECT   a2,
         Count(* ) AS total_a1,
         Sum(CASE 
               WHEN a1%10 = 1 THEN 1
               ELSE 0
             END) AS total_a1_01,
         Sum(CASE 
               WHEN a1%10 = 2 THEN 1
               ELSE 0
             END) AS total_a1_02
FROM     tb
GROUP BY a2

Answer 4

ID A1(int) A2(int)
1 11 100
2 12 100
3 21 100  
4 22 100  
5 22 100
6 23 100
7 11 101
8 22 101
9 12 101--创建表
IF(OBJECT_ID('A')IS NOT NULL) drop table A
create table a
(
id int,
A1 int,
A2 int
)
go
--插入测试数据
insert into a
select 1,11,100 union all
select 2,12,100 union all
select 3,21,100 union all
select 4,22,100 union all
select 5,22,100 union all
select 6,23,100 union all
select 7,11,101 union all
select 8,22,101 union all
select 9,12,101
--测试插入结果/*-----------------------------
select * from a
-----------------------------*/
/*
id A1 A2
1 11 100
2 12 100
3 21 100
4 22 100
5 22 100
6 23 100
7 11 101
8 22 101
9 12 101（所影响的行数为 9 行）
*//*
A2 A1 countnum
100 1 2
100 2 3
100 3 1
101 1 1
101 2 2（所影响的行数为 5 行）
*/--下面进行行转列select  A2,
          isnull(sum(case A1 when 0 then isnull(countnum,0) end),0) as total_A1_00,
          isnull(sum(case A1 when 1 then isnull(countnum,0) end),0) as total_A1_01,
          isnull(sum(case A1 when 2 then isnull(countnum,0) end),0) as total_A1_02, 
          isnull(sum(case A1 when 3 then isnull(countnum,0) end),0) as total_A1_03,
          isnull(sum(case A1 when 4 then isnull(countnum,0) end),0) as total_A1_04, 
          isnull(sum(case A1 when 5 then isnull(countnum,0) end),0) as total_A1_05, 
          isnull(sum(case A1 when 6 then isnull(countnum,0) end),0) as total_A1_06, 
          isnull(sum(case A1 when 7 then isnull(countnum,0) end),0) as total_A1_07, 
          isnull(sum(case A1 when 8 then isnull(countnum,0) end),0) as total_A1_08, 
          isnull(sum(case A1 when 9 then isnull(countnum,0) end),0)as total_A1_09
from  (select A2,right(A1,1)as A1,count(*) as countnum from a
group by A2,right(A1,1)) as b
group by A2
/*
A2          total_A1_00 total_A1_01 total_A1_02 total_A1_03 total_A1_04 total_A1_05 total_A1_06 total_A1_07 total_A1_08 total_A1_09 
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
100         NULL        2           3           1           NULL        NULL        NULL        NULL        NULL        NULL
101         NULL        1           2           NULL        NULL        NULL        NULL        NULL        NULL        NULL（所影响的行数为 2 行）*/

Answer 5

----------------------------------------------------------------
-- Author  :TravyLee(物是人非事事休，欲语泪先流！)
-- Date    :2012-10-26 09:19:44
-- Version:
--      Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
-- Jul  9 2008 14:43:34 
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 6.1 <X86> (Build 7600: )
--
----------------------------------------------------------------
--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
go 
create table [test]([ID] int,[A1] int,[A2] int)
insert [test]
select 1,11,100 union all
select 2,12,100 union all
select 3,21,100 union all
select 4,22,100 union all
select 5,22,100 union all
select 6,23,100 union all
select 7,11,101 union all
select 8,22,101 union all
select 9,12,101
go
 select
[A2],
COUNT(1) as [A1个数],
SUM(case when RIGHT(ltrim([A1]),1)=1 then 1 else 0 end) as [A1个位为1],
SUM(case when RIGHT(ltrim([A1]),1)=2 then 1 else 0 end) as [A1个位为1],
SUM(case when left(ltrim([A1]),1)=1 then 1 else 0 end) as [A1十位为1],
SUM(case when left(ltrim([A1]),1)=2 then 1 else 0 end) as [A1十位为2]
from
test
group by
[A2]
----------------结果----------------------------
/* A2          A1个数        A1个位为1      A1个位为1      A1十位为1      A1十位为2
----------- ----------- ----------- ----------- ----------- -----------
100         6           2           3           2           4
101         3           1           2           2           1(2 行受影响)--其它的以此类推的写法即可
*/