表有字段no,问怎样分类统计出不同no的记录条数,并且要横向排列,要的结果如下:
no的值 1 2 3 4
记录条数 20 15 26 50
我的要求是用一条语句实现
---------------------------------------------------------------
select no,sum(no) from table_name group by no
---------------------------------------------------------------
select * from
(select count(no) as n1 from a001 where no = '1') as a,
(select count(no) as n2 from a001 where no = '2') as b,
(select count(no) as n3 from a001 where no = '3') as c,
(select count(no) as n4 from a001 where no = '4') as d
---------------------------------------------------------------
begin transaction
declare @sql varchar(200)
declare @sql1 varchar(500)
set @sql1='insert into tmp1 values ('
set @sql= 'create table tmp1 ('
select @sql=@sql+'A'+convert(varchar(4),i)+' int,' from
(
select i from #tmp group by i
) a
select @sql1=@sql1+convert(varchar(4),num)+',' from
(
select count(i) as num from #tmp group by i
) a
set @sql=left(@sql,len(@sql)-1)+')'
set @sql1=left(@sql1,len(@sql1)-1)+')'
exec(@sql)
exec(@sql1)
select * from tmp1
rollback transaction
虽说不是一个语句，但是比较通用，