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)
也就是说按每个字段中的数字分组查询出出现的次数和百分比。。比如插入全为1的5条数据和全为2的5条数据
那么结果应该是
a1_1_count ---------- 5
a1_1_percentage ----- 0.5
a1_2_count ---------- 5
a1_2_percentage ----- 0.5
a1_3_count ---------- 0
a1_3_percentage ----- 0.0
.....
a30_1_count --------- 5
a30_1_percentage ---- 0.5
a30_n_count --------- 0
a30_n_percentage ---- 0.0
这样够清楚了吧。
`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)
也就是说按每个字段中的数字分组查询出出现的次数和百分比。。比如插入全为1的5条数据和全为2的5条数据
那么结果应该是
a1_1_count ---------- 5
a1_1_percentage ----- 0.5
a1_2_count ---------- 5
a1_2_percentage ----- 0.5
a1_3_count ---------- 0
a1_3_percentage ----- 0.0
.....
a30_1_count --------- 5
a30_1_percentage ---- 0.5
a30_n_count --------- 0
a30_n_percentage ---- 0.0
这样够清楚了吧。
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