我现在的sql如下:
select t.a, t.b, t.c count(1) d, sum(decode(t.e, 'Y', 1, 0)) e,
from tab t
where .... .
group by t.a, t.b, t.c现在我要根据表中的f字段,如果f字段相同,那么d,e就算作一条进行计数
select t.a, t.b, t.c count(distinct t.f) d, sum(decode(t.e, 'Y', 1, 0)) e,
from tab t
where .... .
group by t.a, t.b, t.c
d字段是可以实现,但是e字段我不知道怎么能够和d字段一样如果是f相同的字段作为一天来进行统计
select t.a, t.b, t.c count(1) d, sum(decode(t.e, 'Y', 1, 0)) e,
from tab t
where .... .
group by t.a, t.b, t.c现在我要根据表中的f字段,如果f字段相同,那么d,e就算作一条进行计数
select t.a, t.b, t.c count(distinct t.f) d, sum(decode(t.e, 'Y', 1, 0)) e,
from tab t
where .... .
group by t.a, t.b, t.c
d字段是可以实现,但是e字段我不知道怎么能够和d字段一样如果是f相同的字段作为一天来进行统计
1000 1 10 100 Y one
1001 2 20 200 Y two
1002 2 20 200 Y two
1003 2 20 200 N two
a b c d e
1 10 100 1 1
2 20 200 1 1
但是根据第一条sql是
a b c d e
1 10 100 1 1
2 20 200 3 2但是根据第二条sql是
a b c d e
1 10 100 1 1
2 20 200 1 2
select a,b,c, count(1) d,sum(decode(t.e, 'Y', 1, 0)) e,
from
(select row_number() over(partion by a,f order by KEY) RN, T.* FROM T)
where rn=1
group by a,b,c
from
(select row_number() over(partition by a,f order by KEY) RN, T.* FROM T)
where rn=1
group by a,b,c
from
(select row_number() over(partition by a,f order by decode(e, 'Y', 1, 0) desc) rn, t_temp.* FROM t_temp)
where rn=1
group by a,b,c
/