主键ID(int) 其他9个字段Letter1~Letter9(char(1)),存储的是26个字母中的其中9个(每一行的字母不重复) 可以实现: declare @num1 int ....... declare @num26 int
select case when Letter1='A' then @num1=@num1 ,,,end as 'ANumber',... from table 应该就差不多吧。 这个跟你的ID在10到100没有关系。多少行都可以计算!
这非常繁琐啊。。跟Union差不多。。 select count(*) from table where ID>10 And ID<100 And ( Letter1='A'||Letter2='A'||……) Union select count(*) from table where ID>10 And ID<100 And ( Letter1='B'||Letter2='B'||……) Union ……
一个语句估计有难度, 还有,建议楼主发到SQL版去,那里大牛多,说不定能给你整出来.
用循环,下面以3列为例 要统计9列和ID大于10小于100,26个字母分别出现的次数 自己照着改就行 [code=SQL]--> Test Data: [tb] if object_id('[tb]') is not null drop table [tb] create table [tb] (id int identity(1,1),[Letter1] varchar(1),[Letter2] varchar(1),[Letter3] varchar(1),c4 varchar(1)) insert into [tb] select 'A','B','C','D' union all select 'G','W','G','I' union all select 'J','K','L','U'select * from [tb] --Code declare @i int declare @T table(letter varchar(1),lc bigint) set @i=65 while @i<91 begin --print CHAR(@i) insert into @T select letter,COUNT(*) from ( select id,Letter1 as letter from [tb] union all select id,Letter2 from [tb] union all select id,Letter3 from [tb] ) t where letter=CHAR(@i) group by letterset @i=@i+1 endselect * from @T --Drop drop table [tb] --Result /* letter lc ------ -------------------- A 1 B 1 C 1 G 2 J 1 K 1 L 1 W 1 */[/code]
就是把9列字母数据合并成1列就好处理多了 26字母可用char进行循环取值
只知道Uniondeclare @Sql varchar(5000),@i int,@letter char(1) select @i=0 select @Sql='' while @i<26 begin if @i>0 select @Sql=@Sql+' union all ' select @letter=char(@i+65) select @Sql=@Sql+'select Letter='''+@letter+''',Nums=count(*) from Table1 where id>10 and id<100 and (Letter1='''+@letter+''' or Letter2='''+@letter+''' ...到Letter9 )' select @i=@i+1 end exec(@Sql)
.......
declare @num26 int
select case when Letter1='A' then @num1=@num1 ,,,end as 'ANumber',... from table 应该就差不多吧。
这个跟你的ID在10到100没有关系。多少行都可以计算!
这非常繁琐啊。。跟Union差不多。。
select count(*) from table where ID>10 And ID<100 And ( Letter1='A'||Letter2='A'||……)
Union
select count(*) from table where ID>10 And ID<100 And ( Letter1='B'||Letter2='B'||……)
Union
……
还有,建议楼主发到SQL版去,那里大牛多,说不定能给你整出来.
要统计9列和ID大于10小于100,26个字母分别出现的次数
自己照着改就行
[code=SQL]--> Test Data: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (id int identity(1,1),[Letter1] varchar(1),[Letter2] varchar(1),[Letter3] varchar(1),c4 varchar(1))
insert into [tb]
select 'A','B','C','D' union all
select 'G','W','G','I' union all
select 'J','K','L','U'select * from [tb]
--Code
declare @i int
declare @T table(letter varchar(1),lc bigint)
set @i=65
while @i<91
begin
--print CHAR(@i)
insert into @T
select letter,COUNT(*) from
(
select id,Letter1 as letter from [tb]
union all
select id,Letter2 from [tb]
union all
select id,Letter3 from [tb]
) t
where letter=CHAR(@i) group by letterset @i=@i+1
endselect * from @T
--Drop
drop table [tb]
--Result
/*
letter lc
------ --------------------
A 1
B 1
C 1
G 2
J 1
K 1
L 1
W 1
*/[/code]
26字母可用char进行循环取值
select @i=0
select @Sql=''
while @i<26
begin
if @i>0
select @Sql=@Sql+' union all '
select @letter=char(@i+65)
select @Sql=@Sql+'select Letter='''+@letter+''',Nums=count(*) from Table1 where id>10 and id<100 and (Letter1='''+@letter+''' or Letter2='''+@letter+''' ...到Letter9 )'
select @i=@i+1
end
exec(@Sql)