解决方案 »

  1.   

    类似于Week3的,则select出来的是两行该列的平均数,week3的输出85--> 请问80.00与84.00的平均数是85?
      

  2.   

    参考下:; 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
      

  3.   

    不用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,
      

  4.   

    转换成空值就可以了。
    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 行受影响)
      

  5.   

    你看错了 85是week4的平均数啊。格式有点不对齐
      

  6.   

    你看错了 85是week4的平均数啊。格式有点不对齐
    我引用的是LZ的原话.
      

  7.   

    你看错了 85是week4的平均数啊。格式有点不对齐
    我引用的是LZ的原话.这个就别纠结了吧,我打错了呗~~~