如下图,
1: 我想把红色框框里的2组 amount=0的记录过滤掉不查出来(要1组全=0才过滤掉, 如果1组内任何1条记录的amount<>0 就都要显示出来), 要怎么写语句呢? 语句要高效点的
2: 整组按sum(amount)的排名 比如key_code=286的组能排第几名 就是11+312+1009+361 能排到第几名
这是我现在select语句:
select key_code, the_week, sum(amount) as amount, sum(amount_add) as amount_add
from fact_xs_week_code a
where a.the_week>=201301 and a.the_week<=201304
group by key_code, the_week
不知道描述清楚了没有, 有问题我再回帖回复 thx~~
------解决方案--------------------
--1)
select * from fact_xs_week_code t where not exists(
select * from fact_xs_week_code where key_code=t.key_code and amount=0
)
--2)
select *,row_number() over(partition by key_code order by total) number
from (select *,sum(amount) over(partition by key_code) total
from fact_xs_week_code) t
------解决方案--------------------
修改一下:
with t
as
(
select key_code, the_week, sum(amount) as amount, sum(amount_add) as amount_add
from fact_xs_week_code a
where a.the_week>=201301 and a.the_week<=201304
group by key_code, the_week
)
select t.*
from t
inner join
(
select key_code
from t
group by key_code
having COUNT(*) = COUNT(case when amount = 0 then 1 else null end)
)tt
on t.key_code <> tt.key_code