数据
列1 列2 列3 列4
1 001 A
1 001 A
1 001 B
2 002 A
3 003 A
4 004 B
5 005 A
5 005
6 006 B
7 007说明 列1和列2是对应的(相当于一个列1是名称,列2是ID) 现在我要统计这个表中 比如:列2=‘001’的A有多少个 ,列2=‘001’的B有多少个,统计列2=‘007‘的A,B的个数则都是’0‘
列1 列2 列3 列4
1 001 A
1 001 A
1 001 B
2 002 A
3 003 A
4 004 B
5 005 A
5 005
6 006 B
7 007说明 列1和列2是对应的(相当于一个列1是名称,列2是ID) 现在我要统计这个表中 比如:列2=‘001’的A有多少个 ,列2=‘001’的B有多少个,统计列2=‘007‘的A,B的个数则都是’0‘
Select a.NUM1,count (a.NUM1),
( DECODE ( a.NUM3,'A', a.NUM3,0 )) as A,
( DECODE ( a.NUM3,'B', a.NUM3,0 )) as BFROM YZC_TEST a
group by a.NUM1,a.NUM3你看下是不是你想要的结果
1 2 A 0
1 1 0 B
2 1 A 0
3 1 A 0
4 1 0 B
5 1 0 0
5 1 A 0
6 1 0 B
7 1 0 0
( DECODE ( a.NUM3,'A', a.NUM3,0)) as A,
( DECODE ( a.NUM3,'B', a.NUM3,0 )) as BFROM YZC_TEST a
group by a.NUM1,a.NUM3修正了下 嘿嘿,你可以做个view来查询你要的结果
SQL:select sum(case when tt.f2 = '001' and tt.f3 = 'A'
then 1
else 0
end) as f2_001_A,
sum(case when tt.f2 = '001' and tt.f3 = 'B'
then 1
else 0
end) as f2_001_B,
sum(case when tt.f2 = '007' and tt.f3 = 'A'
then 1
else 0
end) as f2_007_A,
sum(case when tt.f2 = '007' and tt.f3 = 'B'
then 1
else 0
end) as f2_007_B
from tablename tt;
result: F2_001_A F2_001_B F2_007_A F2_007_B
---------- ---------- ---------- ----------
2 1 0 0
select max(l1),l2,l3,count(l2) from lie group by L2,l3;M L2 L COUNT(L2)
- --- - ----------
1 001 A 2
1 001 B 1
2 002 A 1
3 003 A 1
4 004 B 1
5 005 1
5 005 A 1
6 006 B 1
7 007 1已选择9行。
001 2 1
002 1 0
同时这个表也是一个查询产生的结果集合按照mantisXF的方法 有可能已经将我的结果列定死 请大家继续支持
select l2,sum(decode(l3,'A',1,0)) as A,sum(decode(l3,'B',1,0)) as B from lie
group by l2L2 A B
--- ---------- ----------
001 2 1
002 1 0
003 1 0
004 0 1
005 1 0
006 0 1
007 0 0
select n.f2,sum(n.numA) as numA,sum(n.numB) as numB from
(
select t.f2, t.num as numA, 0 numB from
(
select f2,f3,count(f3) as num from b group by f2,f3
) t
where t.f3 = 'A' or t.f3 is null
union
select f.f2,0 as numA, f.num as numB from
(
select f2,f3,count(f3) as num from b group by f2,f3
) f
where f.f3 = 'B' or f.f3 is null) n
group by n.f2结果如下:001 2 1
002 1 0
003 1 0
005 1 0
006 0 1
007 0 0
看看这个是不是你要的结果。
(select y.emp_id,y.emp_name,y.emp_dept,y.enter_date,y.last_hols,y.current_hols,y.total_hols,y.left_hols
,sum(m1) m1,sum(m2) m2,sum(m3) m3,sum(m4) m4,sum(m5) m5,sum(m6) m6,sum(m7) m7,sum(m8) m8,sum(m9) m9,sum(m10) m10,sum(m11) m11,sum(m12) m12 from
(select a.*,case when EXTRACT(MONTH FROM b.to_date)=1 then sum(b.total_days) end m1
,case when EXTRACT(MONTH FROM b.to_date)=2 then sum(b.total_days) end m2
,case when EXTRACT(MONTH FROM b.to_date)=3 then sum(b.total_days) end m3
,case when EXTRACT(MONTH FROM b.to_date)=4 then sum(b.total_days) end m4
,case when EXTRACT(MONTH FROM b.to_date)=5 then sum(b.total_days) end m5
,case when EXTRACT(MONTH FROM b.to_date)=6 then sum(b.total_days) end m6
,case when EXTRACT(MONTH FROM b.to_date)=7 then sum(b.total_days) end m7
,case when EXTRACT(MONTH FROM b.to_date)=8 then sum(b.total_days) end m8
,case when EXTRACT(MONTH FROM b.to_date)=9 then sum(b.total_days) end m9
,case when EXTRACT(MONTH FROM b.to_date)=10 then sum(b.total_days) end m10
,case when EXTRACT(MONTH FROM b.to_date)=11 then sum(b.total_days) end m11
,case when EXTRACT(MONTH FROM b.to_date)=12 then sum(b.total_days) end m12
from t_manager_hols a,t_leave b where a.emp_id=b.emp_id and b.annual_leave=1 and b.approve_yes=1 and EXTRACT(year FROM b.to_date)=EXTRACT(year FROM sysdate)
group by a.emp_id,a.emp_name,a.emp_dept,a.enter_date,a.last_hols,a.current_hols,a.total_hols,a.left_hols, EXTRACT(MONTH FROM b.to_date)) y
group by y.emp_id,y.emp_name,y.emp_dept,y.enter_date,y.last_hols,y.current_hols,y.total_hols,y.left_hols)x, t_manager_hols t where x.emp_id(+)=t.emp_id