这是一张表的内容:
A B C D
1 0 2 3
3 1 0 0
2 2 1 3
0 3 4 2
2 3 0 1
------------------------
我想要的结果是:
A B C D
0 1 1 2 1
1 1 1 1 1
2 2 1 1 1
3 1 2 0 2
4 0 0 1 0请大家帮帮忙....
A B C D
1 0 2 3
3 1 0 0
2 2 1 3
0 3 4 2
2 3 0 1
------------------------
我想要的结果是:
A B C D
0 1 1 2 1
1 1 1 1 1
2 2 1 1 1
3 1 2 0 2
4 0 0 1 0请大家帮帮忙....
(select count(*) from table where a=AA.a) A,
(select count(*) from table where b=AA.a) B,
(select count(*) from table where c=AA.a) C,
(select count(*) from table where d=AA.a) D
from table AA
group by AA.a
order by AA.a
我所要的结果是:统计
A :中有多少个:0,1,2,3,4
b:中有多少个:0,1,2,3,4
c:中有多少个:0,1,2,3,4
d:中有多少个:0,1,2,3,4
我所要的结果是:统计
A 中有多少个0,1,2,3,4
b中有多少个 0,1,2,3,4
c中有多少个 0,1,2,3,4
d:中有多少个 0,1,2,3,4
tab_a as (
select level id from dual
connect by level<(
select max(id) from (
select max(a) id from table
union
select max(b) id from table
union
select max(c) id from table
union
select max(d) id from table
)
)
)
select id ,
(select count(*) from table where a=tab_a.id) A,
(select count(*) from table where b=tab_a.id) B,
(select count(*) from table where c=tab_a.id) C,
(select count(*) from table where d=tab_a.id) D
from tab_a
group by id
order by id
(select count(*) from table where (rownum-1)=AA.a) A,
(select count(*) from table where (rownum-1)=AA.b) B,
(select count(*) from table where (rownum-1)=AA.c) C,
(select count(*) from table where (rownum-1)=AA.d) D
from table AA
group by AA.a
order by AA.a
(select count(*) from table where a='0') as A,
(select count(*) from table where b='0') as B,
(select count(*) from table where c='0') as C,
(select count(*) from table where d='0') as D from table
union all
select 1,
(select count(*) from table where a='1') as A,
(select count(*) from table where b='1') as B,
(select count(*) from table where c='1') as C,
(select count(*) from table where d='1') as D from table
union all
select 2,
(select count(*) from table where a='2') as A,
(select count(*) from table where b='2') as B,
(select count(*) from table where c='2') as C,
(select count(*) from table where d='2') as D from table
union all
select 3,
(select count(*) from table where a='3') as A,
(select count(*) from table where b='3') as B,
(select count(*) from table where c='3') as C,
(select count(*) from table where d='3') as D from table
union all
select 4,
(select count(*) from table where a='4') as A,
(select count(*) from table where b='4') as B,
(select count(*) from table where c='4') as C,
(select count(*) from table where d='4') as D from table
是用左联....
代码如下:
Select abc.P ,a1.a,b1.b,c1.c,d1.d
From abc
Left Join (
Select decode(a,0,0,1,1,2,2,3,3,4,4) As p ,Count(a) As a
From aa
Group by decode(a,0,0,1,1,2,2,3,3,4,4) )a1
On abc.p=a1.p
Left join
(Select decode(b,0,0,1,1,2,2,3,3,4) As p ,Count(b) As b
From aa
Group by decode(b,0,0,1,1,2,2,3,3,4) ) b1
On abc.p=b1.p
Left Join
(
Select decode(c,0,0,1,1,2,2,3,3,4) As p ,Count(c) As c
From aa
Group by decode(c,0,0,1,1,2,2,3,3,4) )c1
On abc.p=c1.p
Left Join
(
Select decode(d,0,0,1,1,2,2,3,3,4) As p ,Count(d) As d
From aa
Group by decode(d,0,0,1,1,2,2,3,3,4) )d1
On abc.p=d1.p
---------------------
请问大家有没有简单一点的方法呀???