试试这个:
select *
from
(
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
)
where amount <> 0
select *
from
(
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
)
where amount <> 0
--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
select * from fact_xs_week_code where key_code=t.key_code and amount<>0
)
这个语句速度不如上面那位xd,, 我用他的了 ths~
这个不错啊 比 in 速度快!
额 刚才看错了 这个语句不行。。 我是要过滤整组都=0的, 不是剔除掉单条记录=0的。。修改一下:
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
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