表结构
id,pp1,pp2,pp3,pp4,pp5
1,30,60,70,80,40
2,40,50,70,60,90
3,90,20,40,30,50
----------------------------------
想设计输出:
30,2
60,2
70,2
80,1
40,3
50,2
90,2......
也就是每个数字,出现的次数。在mySQL里面,用union all 合起来了一个虚拟表,再用select * from 这个表。但行运行不出来
求教应该怎么写????
select pp1,count(pp1) from (
select pp1 from vip
union all
select pp2 from vip
union all
select pp3 from vip
union all
select pp4 from vip
union all
select pp5 from vip
) vtabel
group by pp1
id,pp1,pp2,pp3,pp4,pp5
1,30,60,70,80,40
2,40,50,70,60,90
3,90,20,40,30,50
----------------------------------
想设计输出:
30,2
60,2
70,2
80,1
40,3
50,2
90,2......
也就是每个数字,出现的次数。在mySQL里面,用union all 合起来了一个虚拟表,再用select * from 这个表。但行运行不出来
求教应该怎么写????
select pp1,count(pp1) from (
select pp1 from vip
union all
select pp2 from vip
union all
select pp3 from vip
union all
select pp4 from vip
union all
select pp5 from vip
) vtabel
group by pp1
你就教我在mysql里面
select pp1,count(pp1) from (
select pp1 from vip
union all
select pp2 from vip
union all
select pp3 from vip
union all
select pp4 from vip
union all
select pp5 from vip
) vtabel
group by pp1 怎么改才是对的。
理解错误。
你直接这样干就行了。
create table c (id int not null,pp1 int, pp2 int,pp3 int,pp4 int,pp5 int);insert into c values
(1,30,60,70,80,40),
(2,40,50,70,60,90),
(3,90,20,40,30,50);select pp, count(pp) as total from
(
select pp1 as pp from c
union all
select pp2 from c
union all
select pp3 from c
union all
select pp4 from c
union all
select pp5 from c
) T group by pp;
query result
pp total
20 1
30 2
40 3
50 2
60 2
70 2
80 1
90 2