CREATE TABLE `vote` (
`a1` char(1) default NULL,
`a2` char(1) default NULL,
......
`a29` char(1) default NULL,
`a30` char(1) default NULL,
`time` char(100) default NULL,
`id` int(50) unsigned NOT NULL auto_increment,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;a1--a30中存储的都是数字,1,2,3,4.。。13==但不确定而且不知道最大值。
需要的结果是:
a1到a30每个字段中出现的每个数字的次数和在本表数据条数中的百分比(有100条数据,那么分母就是100)
也就是说按每个字段中的数字分组查询出出现的次数和百分比。。这样够清楚了吧。
`a1` char(1) default NULL,
`a2` char(1) default NULL,
......
`a29` char(1) default NULL,
`a30` char(1) default NULL,
`time` char(100) default NULL,
`id` int(50) unsigned NOT NULL auto_increment,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;a1--a30中存储的都是数字,1,2,3,4.。。13==但不确定而且不知道最大值。
需要的结果是:
a1到a30每个字段中出现的每个数字的次数和在本表数据条数中的百分比(有100条数据,那么分母就是100)
也就是说按每个字段中的数字分组查询出出现的次数和百分比。。这样够清楚了吧。
union all
select 'a2',a2,count(*)/100 from tb group by a2
....
....
select 'a30',a30,count(*)/100 from tb group by a30
那么结果应该是
a1_1_count | a1_1_percentage | a1_2_count | a1_2_percentage ...| a30_1_count | a30_1_percentage |
10 | 0.10 | 0 | 0.00 ...| 1 | 0.10 |
那么结果应该是
a1_1_count 10
a1_1_percentage 0.1
a1_2_count 0
a1_2_percentage 0.0
a1_3_count 0
a1_3_percentage 0.0
.....
a30_1_count 10
a30_1_percentage 0.1
a30_n_count 0
a30_n_percentage 0.0
[/Quote]
From (
Select a1 as n From vote
Union all
Select a2 From vote
Union all
Select a3 From vote
Union all
Select a4 From vote
Union all
Select a5 From vote
Union all
Select a6 From vote
Union all
Select a7 From vote
Union all
Select a8 From vote
Union all
Select a9 From vote
Union all
Select a10 From vote
Union all
Select a11 From vote
Union all
Select a12 From vote
Union all
Select a13 From vote
Union all
Select a14 From vote
Union all
Select a15 From vote
Union all
Select a16 From vote
Union all
Select a17 From vote
Union all
Select a18 From vote
Union all
Select a19 From vote
Union all
Select a20 From vote
Union all
Select a21 From vote
Union all
Select a22 From vote
Union all
Select a23 From vote
Union all
Select a24 From vote
Union all
Select a25 From vote
Union all
Select a26 From vote
Union all
Select a27 From vote
Union all
Select a28 From vote
Union all
Select a29 From vote
Union all
Select a30 From vote
) t
Group by n