有机构表sys_organ
记录编号 V_REC_ID Varchar2 (10) PK
机构ID V_ORGAN_ID Varchar2 (10)
机构名称 V_ORGAN_NM Varchar2 (20) 预约单表order
预约单编号 Order_id Number(6)
预约日期 Order_date Char(8)
预约类型 Order_type Char(1) 1:信用卡2:理财业务3:信贷业务
所属机构ID Brh_id Char(9) 与表sys_organ对应 现在想用sql查询做下统计,查询结果是 机构ID,机构名,信用卡件数,理财件数,信贷件数
原以为这个count解决了,其他限制条件我就会加了,现在我错了
select b.V_ORGAN_ID,b.V_ORGAN_NM,
count(case when a.Order_type=1 then 1 end),
count(case when a.Order_type=2 then 1 end),
count(case when a.Order_type=3 then 1 end)
from t_busi_order_dtl a,sys_organ b
where a.Brh_id(+) =b.V_ORGAN_ID
and ( b.V_ORGAN_ID = '350101901' or b.V_ORGAN_ID = '350121901' or b.V_ORGAN_ID = '350122901' or b.V_ORGAN_ID = '350123901' or b.V_ORGAN_ID = '350124901' or b.V_ORGAN_ID = '350125901' or b.V_ORGAN_ID = '350128901' or b.V_ORGAN_ID = '350181901' or b.V_ORGAN_ID = '350182901')
and a.Order_date>='20001105' and a.Order_date<='20091106'
group by b.V_ORGAN_ID,b.V_ORGAN_NM 问题出来后面时间限制条件上,加了这个条件,查询的结果就只剩“数量统计结果不为0”(有任何一个类型不为0就能读出来)的记录了
现在我是希望能把,b.V_ORGAN_ID所有机构的记录都要读取出来,即使3个类型的数量统计都为0,也要给我显示查询结果
记录编号 V_REC_ID Varchar2 (10) PK
机构ID V_ORGAN_ID Varchar2 (10)
机构名称 V_ORGAN_NM Varchar2 (20) 预约单表order
预约单编号 Order_id Number(6)
预约日期 Order_date Char(8)
预约类型 Order_type Char(1) 1:信用卡2:理财业务3:信贷业务
所属机构ID Brh_id Char(9) 与表sys_organ对应 现在想用sql查询做下统计,查询结果是 机构ID,机构名,信用卡件数,理财件数,信贷件数
原以为这个count解决了,其他限制条件我就会加了,现在我错了
select b.V_ORGAN_ID,b.V_ORGAN_NM,
count(case when a.Order_type=1 then 1 end),
count(case when a.Order_type=2 then 1 end),
count(case when a.Order_type=3 then 1 end)
from t_busi_order_dtl a,sys_organ b
where a.Brh_id(+) =b.V_ORGAN_ID
and ( b.V_ORGAN_ID = '350101901' or b.V_ORGAN_ID = '350121901' or b.V_ORGAN_ID = '350122901' or b.V_ORGAN_ID = '350123901' or b.V_ORGAN_ID = '350124901' or b.V_ORGAN_ID = '350125901' or b.V_ORGAN_ID = '350128901' or b.V_ORGAN_ID = '350181901' or b.V_ORGAN_ID = '350182901')
and a.Order_date>='20001105' and a.Order_date<='20091106'
group by b.V_ORGAN_ID,b.V_ORGAN_NM 问题出来后面时间限制条件上,加了这个条件,查询的结果就只剩“数量统计结果不为0”(有任何一个类型不为0就能读出来)的记录了
现在我是希望能把,b.V_ORGAN_ID所有机构的记录都要读取出来,即使3个类型的数量统计都为0,也要给我显示查询结果
a.Brh_id(+) =b.V_ORGAN_ID 这个应该是显示等号右边所有记录,就是b.V_ORGAN_ID
a.Order_date(+)>='20001105' 这样又怎么解释呢?
如果你写成a.Order_date>='20001105'
这个查询中外连接就没有意义了
或者你可以写成
select b.V_ORGAN_ID,b.V_ORGAN_NM,
count(case when a.Order_type=1 then 1 end),
count(case when a.Order_type=2 then 1 end),
count(case when a.Order_type=3 then 1 end)
from t_busi_order_dtl a right join sys_organ b
where
on a.Brh_id= b.V_ORGAN_ID
and ( b.V_ORGAN_ID = '350101901' or b.V_ORGAN_ID = '350121901' or b.V_ORGAN_ID = '350122901' or b.V_ORGAN_ID = '350123901' or b.V_ORGAN_ID = '350124901' or b.V_ORGAN_ID = '350125901' or b.V_ORGAN_ID = '350128901' or b.V_ORGAN_ID = '350181901' or b.V_ORGAN_ID = '350182901')
and a.Order_date>='20001105' and a.Order_date<='20091106'
group by b.V_ORGAN_ID,b.V_ORGAN_NM
是sql标准的通用写法. (+)是oracle写法
from a right join b on a.id=b.id
相当于
from a,b where a.id(+)=b.idleft join同理