表格如下:id1 id2 id3 id4 id5 id6 n1 n2 n3 n4 n5 n6
1 0 0 1 1 1 22 1 12 4 6 7
1 1 1 1 0 0 3 5 6 7 8 9
1 0 0 1 1 1 2 1 12 4 6 7
1 1 1 1 0 0 3 5 6 7 8 19.. .. .. .. .. .. 。 。 。 。 。 。id列只有0和1两个值,n列是正整数。
求 各种不同id排列的平均值如:
100111 (22+1+12+4+6+7+2+1+12+4+6+7)/2
111100 (3+5+6+7+8+9+3+5+6+7+8+19)/2
1 0 0 1 1 1 22 1 12 4 6 7
1 1 1 1 0 0 3 5 6 7 8 9
1 0 0 1 1 1 2 1 12 4 6 7
1 1 1 1 0 0 3 5 6 7 8 19.. .. .. .. .. .. 。 。 。 。 。 。id列只有0和1两个值,n列是正整数。
求 各种不同id排列的平均值如:
100111 (22+1+12+4+6+7+2+1+12+4+6+7)/2
111100 (3+5+6+7+8+9+3+5+6+7+8+19)/2
insert into tb select 1 , 1 , 1 , 1 ,0 ,0 , 3 , 5, 6 , 7 , 8 , 9
insert into tb select 1 , 0 , 0 , 1 ,1 , 1 , 2, 1, 12 , 4, 6 , 7
insert into tb select 1 , 1 , 1 , 1 , 0 , 0 , 3, 5 , 6 , 7, 8 , 19 select cast(id1 as varchar(1))+cast(id3 as varchar(1))+
cast(id3 as varchar(1))+cast(id4 as varchar(1))+
cast(id5 as varchar(1))+cast(id6 as varchar(1)) as id,
平均值=avg(n1+n2+n3+n4+n5+n6) from tb
group by cast(id1 as varchar(1))+cast(id3 as varchar(1))+
cast(id3 as varchar(1))+cast(id4 as varchar(1))+
cast(id5 as varchar(1))+cast(id6 as varchar(1))
/*
id 平均值
------------
100111 42
111100 43
*/
drop table tb
insert into tb select 1 , 1 , 1 , 1 ,0 ,0 , 3 , 5, 6 , 7 , 8 , 9
insert into tb select 1 , 0 , 0 , 1 ,1 , 1 , 2, 1, 12 , 4, 6 , 7
insert into tb select 1 , 1 , 1 , 1 , 0 , 0 , 3, 5 , 6 , 7, 8 , 19 select rtrim(id1)+rtrim(id2)+rtrim(id3)+rtrim(id4)+rtrim(id5)+rtrim(id6) id
,avg(n1+n2+n3+n4+n5+n6) v from tb
group by rtrim(id1)+rtrim(id2)+rtrim(id3)+rtrim(id4)+rtrim(id5)+rtrim(id6)
/*
id v
------------------------------------------------------------------------ -----------
100111 42
111100 43
*?
insert into tb select 1 , 1 , 1 , 1 ,0 ,0 , 3 , 5, 6 , 7 , 8 , 9
insert into tb select 1 , 0 , 0 , 1 ,1 , 1 , 2, 1, 12 , 4, 6 , 7
insert into tb select 1 , 1 , 1 , 1 , 0 , 0 , 3, 5 , 6 , 7, 8 , 19 select rtrim(id1)+rtrim(id2)+rtrim(id3)+rtrim(id4)+rtrim(id5)+rtrim(id6) id
,avg(n1+n2+n3+n4+n5+n6) v from tb
group by rtrim(id1)+rtrim(id2)+rtrim(id3)+rtrim(id4)+rtrim(id5)+rtrim(id6)