如何在Group嵌套Group?
直接用例子说我的需求吧：
假设我有数据表：
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该怎么写？
[解决办法]
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
[解决办法]
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
[解决办法]
--用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_02FROM tbGROUP BY a2
[解决办法]
ID A1(int) A2(int)1 11 1002 12 1003 21 100 4 22 100 5 22 1006 23 1007 11 1018 22 1019 12 101--创建表IF(OBJECT_ID('A')IS NOT NULL) drop table Acreate table a(id int,A1 int,A2 int)go--插入测试数据insert into aselect 1,11,100 union allselect 2,12,100 union allselect 3,21,100 union allselect 4,22,100 union allselect 5,22,100 union allselect 6,23,100 union allselect 7,11,101 union allselect 8,22,101 union allselect 9,12,101--测试插入结果/*-----------------------------select * from a-----------------------------*//*id A1 A21 11 1002 12 1003 21 1004 22 1005 22 1006 23 1007 11 1018 22 1019 12 101（所影响的行数为 9 行）*//*A2 A1 countnum100 1 2100 2 3100 3 1101 1 1101 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_09from (select A2,right(A1,1)as A1,count(*) as countnum from a group by A2,right(A1,1)) as bgroup 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 NULL101 NULL 1 2 NULL NULL NULL NULL NULL NULL NULL（所影响的行数为 2 行）*/
[解决办法]
------------------------------ 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 allselect 2,12,100 union allselect 3,21,100 union allselect 4,22,100 union allselect 5,22,100 union allselect 6,23,100 union allselect 7,11,101 union allselect 8,22,101 union allselect 9,12,101go 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 testgroup by [A2]----------------结果----------------------------/* A2 A1个数 A1个位为1 A1个位为1 A1十位为1 A1十位为2----------- ----------- ----------- ----------- ----------- -----------100 6 2 3 2 4101 3 1 2 2 1(2 行受影响)--其它的以此类推的写法即可*/