请注意，与前一个不同了：
直接用例子说我的需求吧：
假设我有数据表：
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的有多少个..依次到9
然后，重点：
A1中个位为1的里面，十位为1的有多少个，十位为2的又有多少个...
A1中个位为2的里面，十位为1的有多少个，十位为2的又有多少个...
依次到9，结果类似这样：
A2 Total_A1 Total_A1_01 Total_A1_02 ... Total_A1_11 Total_A1_21 ... Total_A1_12 Total_A1_22 ...
100 6 2 3 .. 1 1 .. 1 2 ..
101 3 1 2 .. 1 0 .. 1 1 ..
我不想用循环计算太多次，希望能用一条sql搜索实现，请教这个sql该怎么写？
已解决 送分！！
------解决方案--------------------
上一个帖子不是解决了么，还发帖子干啥
------解决方案--------------------
- SQL code
------------------------------ Author :TravyLee(物是人非事事休，欲语泪先流！)-- Date :2012-10-26 10:34:50-- 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,101goselect a.ID, a.A1, a.A2, b.number as 个位, c.number as 十位from test ainner join master..spt_values bon RIGHT(LTRIM(a.A1),1)=b.numberinner join master..spt_values con LEFT(LTRIM(a.A1),1)=c.numberwhere b.number between 0 and 9 and b.type='p' and c.number between 0 and 9 and c.type='p'----------------结果----------------------------/* ID A1 A2 个位 十位----------- ----------- ----------- ----------- -----------1 11 100 1 12 12 100 2 13 21 100 1 24 22 100 2 25 22 100 2 26 23 100 3 27 11 101 1 18 22 101 2 29 12 101 2 1(9 行受影响)*/这是我用系统表把数字给你分开来了 但是也要写好大一堆代码才可以实现 不知道别人有什么好的方法 不过我觉得你还是得写代码才可以的
------解决方案--------------------
- SQL code
SELECT [A2],COUNT(*)Total_A1 ,SUM(CASE RIGHT([A1],1) WHEN 1 THEN 1 ELSE 0 END ) Total_A1_01,SUM(CASE LEFT([A1],1) WHEN 1 THEN 1 ELSE 0 END )Total_A1_02.......FROM #tbGROUP BY [A2]
------解决方案--------------------
- SQL code
SELECT [A2],COUNT(*)Total_A1 ,SUM(CASE WHEN RIGHT([A1],1)=1 AND LEFT([A1],1)=1 THEN 1 ELSE 0 END ) Total_A1_01,SUM(CASE WHEN RIGHT([A1],1)=1 AND LEFT([A1],1)=2 THEN 1 ELSE 0 END )Total_A1_02,SUM(CASE WHEN RIGHT([A1],1)=1 AND LEFT([A1],1)=3 THEN 1 ELSE 0 END )Total_A1_03,SUM(CASE WHEN RIGHT([A1],1)=1 AND LEFT([A1],1)=4 THEN 1 ELSE 0 END )Total_A1_04,SUM(CASE WHEN RIGHT([A1],1)=1 AND LEFT([A1],1)=5 THEN 1 ELSE 0 END )Total_A1_05,SUM(CASE WHEN RIGHT([A1],1)=1 AND LEFT([A1],1)=6 THEN 1 ELSE 0 END )Total_A1_06,SUM(CASE WHEN RIGHT([A1],1)=1 AND LEFT([A1],1)=7 THEN 1 ELSE 0 END )Total_A1_07,SUM(CASE WHEN RIGHT([A1],1)=1 AND LEFT([A1],1)=8 THEN 1 ELSE 0 END )Total_A1_08,SUM(CASE WHEN RIGHT([A1],1)=1 AND LEFT([A1],1)=9 THEN 1 ELSE 0 END )Total_A1_09,SUM(CASE WHEN RIGHT([A1],1)=2 AND LEFT([A1],1)=1 THEN 1 ELSE 0 END )Total_A1_01,SUM(CASE WHEN RIGHT([A1],1)=2 AND LEFT([A1],1)=2 THEN 1 ELSE 0 END )Total_A1_02,SUM(CASE WHEN RIGHT([A1],1)=2 AND LEFT([A1],1)=3 THEN 1 ELSE 0 END )Total_A1_03,SUM(CASE WHEN RIGHT([A1],1)=2 AND LEFT([A1],1)=4 THEN 1 ELSE 0 END )Total_A1_04,SUM(CASE WHEN RIGHT([A1],1)=2 AND LEFT([A1],1)=5 THEN 1 ELSE 0 END )Total_A1_05,SUM(CASE WHEN RIGHT([A1],1)=2 AND LEFT([A1],1)=6 THEN 1 ELSE 0 END )Total_A1_06,SUM(CASE WHEN RIGHT([A1],1)=2 AND LEFT([A1],1)=7 THEN 1 ELSE 0 END )Total_A1_07,SUM(CASE WHEN RIGHT([A1],1)=2 AND LEFT([A1],1)=8 THEN 1 ELSE 0 END )Total_A1_08,SUM(CASE WHEN RIGHT([A1],1)=2 AND LEFT([A1],1)=9 THEN 1 ELSE 0 END )Total_A1_09FROM #tbGROUP BY [A2]