参考下:; WITH cte AS ( SELECT 1 AS uid, 100 AS week1,65.00 AS week2,80.00 AS week3,90.00 AS week4,83.00 AS week5 UNION ALL SELECT 1,0.00,0.00,84.00,80.00,85.00 ) SELECT uid, sum(Week1)/SUM(CASE WHEN week1>0 THEN 1 ELSE 0 end) Week1, sum(Week2)/SUM(CASE WHEN week2>0 THEN 1 ELSE 0 end) Week2, sum(Week3)/SUM(CASE WHEN week3>0 THEN 1 ELSE 0 end) Week3, sum(Week4)/SUM(CASE WHEN week4>0 THEN 1 ELSE 0 end) Week4, sum(Week5)/SUM(CASE WHEN week5>0 THEN 1 ELSE 0 end) Week5 FROM cte GROUP BY uid
不用avg,而是自己用和除以数量,如果等于0的不算数就可以了。 如果还可能有负数,将 sum(Week1)/SUM(CASE WHEN week1>0 THEN 1 ELSE 0 end) Week1, 改为 sum(Week1)/SUM(CASE WHEN week1=0 THEN 0 ELSE 1 end) Week1,
转换成空值就可以了。 with cte(week1,week2,week3,week4,week5) as (select 100.00,65.00,80.00,90.00,83.00 union all select 0.00,0.00,84.00,80.00,85.00),
cte1 as (select (case week1 when '0.00' then null else week1 end) as week1, (case week2 when '0.00' then null else week2 end) as week2, (case week3 when '0.00' then null else week3 end) as week3, (case week4 when '0.00' then null else week4 end) as week4, (case week5 when '0.00' then null else week5 end) as week5 from cte) select AVG(week1) as avg1,AVG(week2)as avg2,AVG(week3)as avg3,AVG(week4) as avg4, AVG(week5)as avg5 from cte1avg1 avg2 avg3 avg4 avg5 --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- 100.000000 65.000000 82.000000 85.000000 84.000000 警告: 聚合或其他 SET 操作消除了 Null 值。(1 行受影响)
SELECT 1 AS uid, 100 AS week1,65.00 AS week2,80.00 AS week3,90.00 AS week4,83.00 AS week5
UNION ALL
SELECT 1,0.00,0.00,84.00,80.00,85.00
)
SELECT uid,
sum(Week1)/SUM(CASE WHEN week1>0 THEN 1 ELSE 0 end) Week1,
sum(Week2)/SUM(CASE WHEN week2>0 THEN 1 ELSE 0 end) Week2,
sum(Week3)/SUM(CASE WHEN week3>0 THEN 1 ELSE 0 end) Week3,
sum(Week4)/SUM(CASE WHEN week4>0 THEN 1 ELSE 0 end) Week4,
sum(Week5)/SUM(CASE WHEN week5>0 THEN 1 ELSE 0 end) Week5
FROM cte GROUP BY uid
如果还可能有负数,将
sum(Week1)/SUM(CASE WHEN week1>0 THEN 1 ELSE 0 end) Week1,
改为
sum(Week1)/SUM(CASE WHEN week1=0 THEN 0 ELSE 1 end) Week1,
with cte(week1,week2,week3,week4,week5) as
(select 100.00,65.00,80.00,90.00,83.00
union all
select 0.00,0.00,84.00,80.00,85.00),
cte1 as
(select (case week1 when '0.00' then null else week1 end) as week1,
(case week2 when '0.00' then null else week2 end) as week2,
(case week3 when '0.00' then null else week3 end) as week3,
(case week4 when '0.00' then null else week4 end) as week4,
(case week5 when '0.00' then null else week5 end) as week5
from cte)
select AVG(week1) as avg1,AVG(week2)as avg2,AVG(week3)as avg3,AVG(week4) as avg4,
AVG(week5)as avg5 from cte1avg1 avg2 avg3 avg4 avg5
--------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
100.000000 65.000000 82.000000 85.000000 84.000000
警告: 聚合或其他 SET 操作消除了 Null 值。(1 行受影响)
我引用的是LZ的原话.
我引用的是LZ的原话.这个就别纠结了吧,我打错了呗~~~