SQL到Access转换：Case When Then 语句改写教程

sql数据库 转 Access SQL语句改写之 Case When Then When Then Else End 修改篇2008-06-22 16:26修改前

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)