select sum(case when isnumeric(字段)=1 then 字段 else 0 end) from 表
sql="SELECT month(receive.sgsj) as month, sum(case when zrrd.fc1='未填' or zrrd.fc1='为空' then 0 else clng(zrrd.fc1) end) as fc1 FROM zrrd INNER JOIN receive ON receive.ajbh=zrrd.ajbh WHERE year(receive.sgsj)='"&year&"' AND receive.cjr='"&cjr&"' group by month(receive.sgsj)" 我的代码是这样的,意思是如果字段为未填或为空, 统计时取0, 否则将其转化为数字统计。 一位大哥说access不支持case when ,请问大家有没有好的解决方法,
sql="SELECT month(receive.sgsj) as month, sum(iif(zrrd.fc1='未填' or zrrd.fc1='为空',0,clng(zrrd.fc1)) as fc1 FROM zrrd INNER JOIN receive ON receive.ajbh=zrrd.ajbh WHERE year(receive.sgsj)='"&year&"' AND receive.cjr='"&cjr&"' group by month(receive.sgsj)"用iif代替case
sql="SELECT month(receive.sgsj) as month, sum(iif(zrrd.fc1='未填' or zrrd.fc1='为空',0,clng(zrrd.fc1)) as fc1 FROM zrrd INNER JOIN receive ON receive.ajbh=zrrd.ajbh WHERE year(receive.sgsj)='"&year&"' AND receive.cjr='"&cjr&"' group by month(receive.sgsj)"用iif代替case
SELECT month(receive.sgsj) as month,sum(iif(zrrd.fc1='未填' or zrrd.fc1='为空',0,clng(zrrd.fc1)) as fc1 FROM zrrd INNER JOIN receive ON receive.ajbh=zrrd.ajbh WHERE year(receive.sgsj)='2003' AND receive.cjr='小王' group by month(receive.sgsj) Microsoft OLE DB Provider for ODBC Drivers 错误 '80040e14' [Microsoft][ODBC Microsoft Access Driver] 语法错误 (操作符丢失) 在查询表达式 'sum(iif(zrrd.fc1='未填' or zrrd.fc1='为空',0,clng(zrrd.fc1)) as fc1 FROM zrrd INNER JOIN receive ON receive.ajbh=zrrd.ajbh WHERE year(receive.sgsj)='2003' AND receive.cjr='小王' group by month(receive.sgsj)' 中。 /work/index/fc_year.asp,行 32 还是不行呀, pengdali(大力 V2.0) ,能不能帮我看看呀,我很急呀,马上要交工了
sql="SELECT month(receive.sgsj) as month, sum(iif(zrrd.fc1 not in('未填','为空')),clng(zrrd.fc1),0) as fc1 FROM zrrd INNER JOIN receive ON receive.ajbh=zrrd.ajbh WHERE year(receive.sgsj)='"&year&"' AND receive.cjr='"&cjr&"' group by month(receive.sgsj)"用这种方法输出的sql语句如下: SELECT month(receive.sgsj) as month,sum(iif(zrrd.fc1 not in('未填','为空')),clng(zrrd.fc1),0) as fc1 FROM zrrd INNER JOIN receive ON receive.ajbh=zrrd.ajbh WHERE year(receive.sgsj)='2003' AND receive.cjr='小王' group by month(receive.sgsj) 报错如下: Microsoft OLE DB Provider for ODBC Drivers 错误 '80040e14' [Microsoft][ODBC Microsoft Access Driver] 用于函数参数的个数不对 在查询表达式 'sum(iif(zrrd.fc1 not in('未填','为空')),clng(zrrd.fc1),0)' 中。 /work/index/fc_year.asp,行 32
sql="SELECT month(receive.sgsj) as month, sum(iif(zrrd.fc1 in ('未填','为空'),0,zrrd.fc1) as fc1 FROM zrrd INNER JOIN receive ON receive.ajbh=zrrd.ajbh WHERE year(receive.sgsj)='"&year&"' AND receive.cjr='"&cjr&"' group by month(receive.sgsj)"呢?
也不行,输出的sql语句: SELECT month(receive.sgsj) as month, sum(iif(zrrd.fc1 in ('未填','为空'),0,clng(zrrd.fc1)) as fc1 FROM zrrd INNER JOIN receive ON receive.ajbh=zrrd.ajbh WHERE year(receive.sgsj)='2003' AND receive.cjr='小王' group by month(receive.sgsj) 报错如下上: Microsoft OLE DB Provider for ODBC Drivers 错误 '80040e14' [Microsoft][ODBC Microsoft Access Driver] 语法错误 (操作符丢失) 在查询表达式 'sum(iif(zrrd.fc1 in ('未填','为空'),0,clng(zrrd.fc1)) as fc1 FROM zrrd INNER JOIN receive ON receive.ajbh=zrrd.ajbh WHERE year(receive.sgsj)='2003' AND receive.cjr='小王' group by month(receive.sgsj)' 中。 /work/index/fc_year.asp,行 34
sql="SELECT month(receive.sgsj) as month, sum(iif(zrrd.fc1 not in ('未填','为空'),0,1)) as fc1 FROM zrrd INNER JOIN receive ON receive.ajbh=zrrd.ajbh WHERE year(receive.sgsj)='"&year&"' AND receive.cjr='"&cjr&"' group by month(receive.sgsj)" 这个语句是对的, 但将iif里的1改为clng(zrrd.fc1)时会出错 输出的sql语句: SELECT month(receive.sgsj) as month,sum(iif(zrrd.fc1 not in ('未填','为空'),0,cint(zrrd.fc1))) as fc1 FROM zrrd INNER JOIN receive ON receive.ajbh=zrrd.ajbh WHERE year(receive.sgsj)='2003' AND receive.cjr='小王' group by month(receive.sgsj) 报错: Microsoft OLE DB Provider for ODBC Drivers 错误 '80040e07' [Microsoft][ODBC Microsoft Access Driver] 标准表达式中数据类型不匹配。 /work/index/fc_year.asp,行 34
sum(case when zrrd.fc1='未填' or zrrd.fc1='为空' then 0 else clng(zrrd.fc1) end) as fc1
FROM zrrd
INNER JOIN receive ON receive.ajbh=zrrd.ajbh
WHERE year(receive.sgsj)='"&year&"' AND receive.cjr='"&cjr&"'
group by month(receive.sgsj)"
我的代码是这样的,意思是如果字段为未填或为空,
统计时取0,
否则将其转化为数字统计。
一位大哥说access不支持case when
,请问大家有没有好的解决方法,
sum(iif(zrrd.fc1='未填' or zrrd.fc1='为空',0,clng(zrrd.fc1)) as fc1
FROM zrrd
INNER JOIN receive ON receive.ajbh=zrrd.ajbh
WHERE year(receive.sgsj)='"&year&"' AND receive.cjr='"&cjr&"'
group by month(receive.sgsj)"用iif代替case
sum(iif(zrrd.fc1='未填' or zrrd.fc1='为空',0,clng(zrrd.fc1)) as fc1
FROM zrrd
INNER JOIN receive ON receive.ajbh=zrrd.ajbh
WHERE year(receive.sgsj)='"&year&"' AND receive.cjr='"&cjr&"'
group by month(receive.sgsj)"用iif代替case
Microsoft OLE DB Provider for ODBC Drivers 错误 '80040e14' [Microsoft][ODBC Microsoft Access Driver] 语法错误 (操作符丢失) 在查询表达式 'sum(iif(zrrd.fc1='未填' or zrrd.fc1='为空',0,clng(zrrd.fc1)) as fc1 FROM zrrd INNER JOIN receive ON receive.ajbh=zrrd.ajbh WHERE year(receive.sgsj)='2003' AND receive.cjr='小王' group by month(receive.sgsj)' 中。
/work/index/fc_year.asp,行 32 还是不行呀,
pengdali(大力 V2.0) ,能不能帮我看看呀,我很急呀,马上要交工了
sum(iif(zrrd.fc1 not in('未填','为空')),clng(zrrd.fc1),0) as fc1
FROM zrrd INNER JOIN receive ON receive.ajbh=zrrd.ajbh
WHERE year(receive.sgsj)='"&year&"' AND receive.cjr='"&cjr&"'
group by month(receive.sgsj)"用这种方法输出的sql语句如下:
SELECT month(receive.sgsj) as month,sum(iif(zrrd.fc1 not in('未填','为空')),clng(zrrd.fc1),0) as fc1 FROM zrrd INNER JOIN receive ON receive.ajbh=zrrd.ajbh WHERE year(receive.sgsj)='2003' AND receive.cjr='小王' group by month(receive.sgsj) 报错如下:
Microsoft OLE DB Provider for ODBC Drivers 错误 '80040e14' [Microsoft][ODBC Microsoft Access Driver] 用于函数参数的个数不对 在查询表达式 'sum(iif(zrrd.fc1 not in('未填','为空')),clng(zrrd.fc1),0)' 中。 /work/index/fc_year.asp,行 32
sum(iif(zrrd.fc1 in ('未填','为空'),0,zrrd.fc1) as fc1
FROM zrrd
INNER JOIN receive ON receive.ajbh=zrrd.ajbh
WHERE year(receive.sgsj)='"&year&"' AND receive.cjr='"&cjr&"'
group by month(receive.sgsj)"呢?
SELECT month(receive.sgsj) as month,
sum(iif(zrrd.fc1 in ('未填','为空'),0,clng(zrrd.fc1)) as fc1 FROM zrrd
INNER JOIN receive ON receive.ajbh=zrrd.ajbh
WHERE year(receive.sgsj)='2003' AND receive.cjr='小王'
group by month(receive.sgsj)
报错如下上:
Microsoft OLE DB Provider for ODBC Drivers 错误 '80040e14' [Microsoft][ODBC Microsoft Access Driver] 语法错误 (操作符丢失) 在查询表达式 'sum(iif(zrrd.fc1 in ('未填','为空'),0,clng(zrrd.fc1)) as fc1 FROM zrrd INNER JOIN receive ON receive.ajbh=zrrd.ajbh WHERE year(receive.sgsj)='2003' AND receive.cjr='小王' group by month(receive.sgsj)' 中。 /work/index/fc_year.asp,行 34
sum(iif(zrrd.fc1 not in ('未填','为空'),0,1)) as fc1
FROM zrrd INNER JOIN receive ON receive.ajbh=zrrd.ajbh
WHERE year(receive.sgsj)='"&year&"' AND receive.cjr='"&cjr&"'
group by month(receive.sgsj)"
这个语句是对的,
但将iif里的1改为clng(zrrd.fc1)时会出错
输出的sql语句:
SELECT month(receive.sgsj) as month,sum(iif(zrrd.fc1 not in ('未填','为空'),0,cint(zrrd.fc1))) as fc1 FROM zrrd INNER JOIN receive ON receive.ajbh=zrrd.ajbh WHERE year(receive.sgsj)='2003' AND receive.cjr='小王' group by month(receive.sgsj) 报错:
Microsoft OLE DB Provider for ODBC Drivers 错误 '80040e07' [Microsoft][ODBC Microsoft Access Driver] 标准表达式中数据类型不匹配。 /work/index/fc_year.asp,行 34