递增的列表写法求助
大概结构如下
A B
1 9
1 9
1 29
2 9
2 39
2 9
.. ...
得出如下
A 9 19 29 39 49....99
1 2 0 1 0 0...
2 2 0 0 1 0....
表列B 为9以10为递增 到99
当A＝1 时 统计当时的个数 如当B＝9 统计数为 2 无则为0
...
谢谢帮助..
[解决办法]
--创建测试表及数据create table tablename_tz(a int,b int)insert into tablename_tzselect 1,9 union allselect 1,9 union allselect 1,29 union allselect 2,9 union allselect 2,39 union allselect 2,9declare @sql varchar(8000) set @sql = 'select a 'declare @i int set @i = 1while ( @i <= 10 ) begin set @sql = @sql + ',[' + ltrim(( @i - 1 ) * 10 + 9) + ']=(select count(1) from tablename_tz where a= t.a and b=' + +ltrim(( @i - 1 ) * 10 + 9) + +')' set @i = @i + 1 endset @sql = @sql + 'from tablename_tz t group by a'exec (@sql)/*A 9 19 29 39 49 59 69 79 89 99----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------1 2 0 1 0 0 0 0 0 0 02 2 0 0 1 0 0 0 0 0 0*/
[解决办法]
create table t (A int,B int)insert into Tselect 1,9 union allselect 1,9 union allselect 1,29 union allselect 2,9 union allselect 2,39 union allselect 2,9godeclare @str varchar(max)=''select @str=@str+',['+cast((number) as varchar(10))+']=(select count(1) from t where A=tb.A and b='+cast((number) as varchar(10))+')' +CHAR(10)from master..spt_values b where b.type='p' and b.number<100 AND B.number%10=9set @str='select A'+@str+' from t tb group by A'EXEC (@str)A 9 19 29 39 49 59 69 79 89 991 2 0 1 0 0 0 0 0 0 02 2 0 0 1 0 0 0 0 0 0(2 行受影响)
