select count(*) from v_userinfo where userid not in
(select Expr1 from jiaofei_select where chaoyear='2009' and chaomouth='6月份')
and AddAreaID=11 and AddCommID=16
(select Expr1 from jiaofei_select where chaoyear='2009' and chaomouth='6月份')
and AddAreaID=11 and AddCommID=16
from v_userinfo a
where not exists
(select 1 from jiaofei_select b where chaoyear='2009' and chaomouth='6月份'
and b.Expr1=a.userid) and AddAreaID=11 and AddCommID=16
可以吗?
select *
from v_userinfo a
where not exists
(select DISTINCT 1 from jiaofei_select b where chaoyear='2009' and chaomouth='6月份'
and b.Expr1=a.userid) and AddAreaID=11 and AddCommID=16
--应该没问题
--据说用INNER JOIN 比WHERE 快
select * from v_userinfo a
INNER JOIN v_userinfo A1 A.USERID=A1.USERID AND A. A.AddAreaID=11 and A.AddCommID=16
AND not exists
(select DISTINCT 1 from jiaofei_select b where chaoyear='2009' and chaomouth='6月份'
and b.Expr1=a.userid)
INNER JOIN v_userinfo ON A1 A.USERID=A1.USERID
left join
(select Expr1 from jiaofei_select where chaoyear='2009' and chaomouth='6月份') b
on a.userid = b.Exprl where b.Exprl is null and AddAreaID=11 and AddCommID=16
from V_userinfo a
left join
(select Expr1 from jiaofei_select where chaoyear <> '2009' and chaomouth <> '6月份') B
on A.userid = B.Expr1
where B.expr1 is not null
and AddAreaID=11 and AddCommID=16
(select Expr1 from jiaofei_select where chaoyear = '2009' And chaomouth = '6月份')
and AddAreaID = 11 and AddCommID = 16
v_UserInfo 优化
索引
where userid not exists
(
select 1 from jiaofei_select where tb.userid= Expr1 and chaoyear='2009' and chaomouth='6月份'
)
and AddAreaID=11 and AddCommID=16
from emp a ,emp_bill b
where a.emp_no=b.emp_no(+)
and b.emp_no is null
据说没有no 或是not更快。高手们指点下。上面的答案。语句没问题结果不对。谢了
from emp a ,emp_bill b
where a.emp_no=b.emp_no(+)
and NVL(b.emp_no, '1') = '1'
(select Expr1 from jiaofei_select where chaoyear='2009' and chaomouth='6月份')
and AddAreaID=11 and AddCommID=16