查询所有
select COUNT(*) from vf;
统计不为空
select COUNT(custid) from vf;
去重统计:
SELECT COUNT(DISTINCT custid) FROM vf;
条件统计
COUNT()函数中条件表达式加 OR null
SELECT COUNT(number > 200 OR null) FROM vf;
COUNT()函数中条件表达式使用 if
SELECT COUNT(if(number > 200, 1, null)) FROM vf;
COUNT()函数中条件表达式使用 case when
SELECT COUNT(case when number > 200 then 1 end) FROM vf;
注意case 前面不要加括号
select vc."custName",vc."phone",vc."custId",
count(distinct TO_char(a.日期,'YYYY-MM')) 总活跃月份,
count(
distinct
case
when a.日期>=to_date('2022-01-01','YYYY-MM-DD') and a.日期<=to_date('2022-12-31','YYYY-MM-DD')
then TO_char(a.日期,'YYYY-MM-DD')
end
) 总活跃天份
from V_CUST_CHANNEL_SEARCH vc
left join
(
select distinct CUSTID 客户Id,cfacctime 日期
from V_FIN_PRO_CHARGEBILL
union all
select distinct "ID" 用户id,"RECORD_DATE" 日期
from V_SML_TREAT_RECORD
) a ON VC."custId"=a.客户id