修改前
SELECT SUM(sum_m) as sum_m, SUM(mem_result) as mem_result, SUM(mem_ds) as mem_ds , SUM(ag_ds) as ag_ds,zong_ds = isnull(SUM(zong_ds), 0), re = COUNT(*) FROM (SELECT sum_m,mem_ds = CASE WHEN banlance = 0 or amount='Alls' THEN 0 ELSE sum_m * rake / 100 END, mem_result = CASE WHEN banlance = 0 or amount='Alls' THEN 0 ELSE CASE WHEN result = 0 THEN - sum_m WHEN result > 0 THEN (CASE WHEN sort = 'likst' THEN sum_m / count_l* rate * result - sum_m ELSE sum_m * (rate - 1) END) END END, ag_ds = CASE WHEN banlance = 0 or amount='Alls' THEN 0 ELSE sum_m * dai_ds / 100 END, zong_ds = CASE WHEN banlance = 0 or amount='Alls' THEN 0 ELSE sum_m * zong_ds / 100 END from table
修改后
SELECT SUM(sum_m) as sum_m, SUM(mem_result) as mem_result, SUM(mem_ds) as mem_ds , SUM(ag_ds) as ag_ds,iif(isnull(SUM(zong_ds)),0,Sum(zong_ds)) as zong_ds1, COUNT(*) as re FROM (SELECT sum_m,iif((banlance = 0 or amount='Alls'),0,sum_m * rake / 100) as mem_ds,iif(( banlance = 0 or amount='Alls' ),0,switch(result=0,-sum_m,result>0,iif(sort='likst',(sum_m / count_l* rate * result - sum_m),sum_m * (rate - 1)))) as mem_result ,iif(banlance = 0 or amount='Alls' ,0 , sum_m * dai_ds / 100 ) as ag_ds ,iif( banlance = 0 , 0, sum_m * zong_ds / 100 ) as zong_ds from table
主要用到2个函数 一个 iif 一个 swith
iif(expr,value1,value2)
iif means
if expr then value1 else value2
switch 函数
计算一组表达式列表的值,然后返回与表达式列表中最先为 true 的表达式所相关的 variant 数值或表达式。
语法
switch(expr-1, value-1[, expr-2, value-2 _ [, expr-n,value-n]])
switch 函数的语法具有以下几个部分:
部分 描述
expr 必要参数。要加以计算的 variant 表达式。
value 必要参数。如果相关的表达式为 true,则返回此部分的数值或表达式。
说明
switch 函数的参数列表由多对表达式和数值组成。表达式是由左至右加以计算的,而数值则会在第一个相关的表达式为 true 时返回。如果其中有部分不成对,则会产生一个运行时错误。如果 expr-1 为 true 则 switch 返回 value-1,如果 expr-1 为 false,但 expr-2 为 true,则 switch 返回 value-2,以此类推。
时间比较 记得 用# 替换'