SELECT
IPADDRESS ,
count(case STATUS when 0 then 1 else 0 end) as suc,
count(case STATUS when not 0 then 1 else 0 end) as ero,
COLLECTTIME
FROM
P_TABLE 这个第一个count 是统计这个字段=0的有多少个,第2个 是统计这个字段不等于0的有多少个 但是 不等于0的这个语法是错误的 报错。问如何解决?
IPADDRESS ,
count(case STATUS when 0 then 1 else 0 end) as suc,
count(case STATUS when not 0 then 1 else 0 end) as ero,
COLLECTTIME
FROM
P_TABLE 这个第一个count 是统计这个字段=0的有多少个,第2个 是统计这个字段不等于0的有多少个 但是 不等于0的这个语法是错误的 报错。问如何解决?
IPADDRESS ,
count(case when STATUS=0 then 1 else 0 end) as suc,
count(case when STATUS!=0 then 1 else 0 end) as ero,
COLLECTTIME
FROM
P_TABLE
group by IPADDRESS,COLLECTTIME;
IPADDRESS ,
count(case when STATUS=0 then 1 else 0 end) as suc,
count(case when STATUS<>0 then 1 else 0 end) as ero,
COLLECTTIME
FROM
P_TABLEoracle里没有!=
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - ProductionSQL> select ename from emp where empno=7788;ENAME
----------
SCOTTSQL> select ename from emp where empno!=7788;ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
KING
TURNER
ADAMS
JAMESENAME
----------
FORD
MILLER已选择13行。SQL>奇闻…………
count(field_name)统计所有非null记录
如果COUNT,必须把不想计数的值设置为null
SELECT
IPADDRESS ,
count(case when STATUS=0 then 1 else null end) as suc,
count(case when STATUS!=0 then 1 else null end) as ero,
COLLECTTIME
FROM
P_TABLE
group by IPADDRESS,COLLECTTIME;另外,可使用decode
SELECT
IPADDRESS ,
count(decode(STATUS,0,1,null)) as suc,
count(decode(sign(STATUS),0,null,1)) as ero,
COLLECTTIME
FROM
P_TABLE
group by IPADDRESS,COLLECTTIME;
SELECT
IPADDRESS,
count(case when STATUS=0 then 1 else 0 end) as suc,
count(case when STATUS<>0 then 1 else 0 end) as ero,
COLLECTTIME
FROM
P_TABLE
group by IPADDRESS,COLLECTTIME
IPADDRESS ,
count(case when STATUS=0 then 1 else 0 end) as suc,
count(case when STATUS!=0 then 1 else 0 end) as ero,
COLLECTTIME
FROM
P_TABLE