解决方案 »
- 简单的Sql查询
- SQL SERVER 中数据的处理
- 监控数据库的使用情况
- 错误的批量修改数据库后的恢复问题,急!!!
- 献上100分数,求一个sql语句,各位高手帮帮忙!
- 需要扣除一个字段然后扣到0为止
- 问:MS-SQL触发器与事务问题,请高人来帮帮忙!
- 关于CASE的问题,急,在线等!
- 高手么:怎么用ADO或ADO DATE连接到光盘上的只读文件?
- 我建议增加 oracle Developer 2000开发工具的问题讨论
- 安装CMS 提示access denied for user odbc@localhost(using password:no)
- 使用U8执行某个功能,如何查看执行了哪些SQL语句,或者存储过程(在线等)
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的原话.这个就别纠结了吧,我打错了呗~~~