我有一张统计表keguan结构如下
st1 st2 st3 st4 .......
a b c d .......
b a d a .......
d c c c .......我想统计 st1中a有几个,b有几个,c有几个,d有几个,st2中a有几个,b有几个,c有几个,d有几个,..........,请问这个sql语句该怎么写?
st1 st2 st3 st4 .......
a b c d .......
b a d a .......
d c c c .......我想统计 st1中a有几个,b有几个,c有几个,d有几个,st2中a有几个,b有几个,c有几个,d有几个,..........,请问这个sql语句该怎么写?
a b c d .......
b a d a .......
d c c c .......select st1,count(*) from tb
select st2,count(*) from tb
....................
(
st1 varchar(10),
st2 varchar(10),
st3 varchar(10),
st4 varchar(10)
)insert T select 'a','b','c','d'
insert T select 'b','a','d','a'
insert T select 'd','c','c','c'declare @T_SQL varchar(8000)
set @T_SQL=''
select @T_SQL=@T_SQL + 'select ''' + a.name +''' as F_Name,' + a.name + ' from T union all '
from syscolumns a,sysobjects d
where a.id=d.id
and d.name='T'set @T_SQL=left(@T_SQL,len(@T_SQL)-len(' union all '))
set @T_SQL='select F_Name,st1,count(1) as number from ( ' + @T_SQL + ' ) T group by F_Name,st1 order by F_Name'
exec (@T_SQL)