我的Sql:检索有3条记录
select ri_2.remind_Info_Id as ownerId,rrt_2.remind_Resource_Type_Name as typeName ,
ri_2.content as content,emp_2.email_Address as address ,emp_2.name as addressee,emp_2.name as name
from Remind_Info ri_2, Employee emp_2, Remind_Resource_Type rrt_2
where ri_2.create_Id = emp_2.login_Id and ri_2.remind_Type_Id = 1
and ri_2.close_Flag = 0 and ri_2.remind_Way_Id = 1 and ri_2.overtime_Remind_Owner_Flag = 1
and rrt_2.remind_Resource_Type_Id = 2
and TO_CHAR(ri_2.end_Time - 1/24,'yyyy-mm-dd hh24:mi')<=TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi')
and
(
(select count(*) from Remind_Target rt_2 where ri_2.remind_Info_Id = rt_2.remind_Info_Id)=2
and
(select count(*) from Read_Remind readR_2 where ri_2.remind_Info_Id = readR_2.remind_Entity_Id
and readR_2.remind_Resource_Type_Id = 1 and readR_2.close_Flag = 1 )=0
)
修改后:一条记录也没有,怎么回事?将Selec子句包上To_number()有记录,是3条,但是不知道原理是什么!如果不用To_number可以实现吗,因为在Hql中To_number中的子句不加载。select ri_2.remind_Info_Id as ownerId,rrt_2.remind_Resource_Type_Name as typeName ,
ri_2.content as content,emp_2.email_Address as address ,emp_2.name as addressee,emp_2.name as name
from Remind_Info ri_2, Employee emp_2, Remind_Resource_Type rrt_2
where ri_2.create_Id = emp_2.login_Id and ri_2.remind_Type_Id = 1
and ri_2.close_Flag = 0 and ri_2.remind_Way_Id = 1 and ri_2.overtime_Remind_Owner_Flag = 1
and rrt_2.remind_Resource_Type_Id = 2
and TO_CHAR(ri_2.end_Time - 1/24,'yyyy-mm-dd hh24:mi')<=TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi')
and
(
(select count(*) from Remind_Target rt_2 where ri_2.remind_Info_Id = rt_2.remind_Info_Id)
>
(select count(*) from Read_Remind readR_2 where ri_2.remind_Info_Id = readR_2.remind_Entity_Id
and readR_2.remind_Resource_Type_Id = 1 and readR_2.close_Flag = 1 )
)
select ri_2.remind_Info_Id as ownerId,rrt_2.remind_Resource_Type_Name as typeName ,
ri_2.content as content,emp_2.email_Address as address ,emp_2.name as addressee,emp_2.name as name
from Remind_Info ri_2, Employee emp_2, Remind_Resource_Type rrt_2
where ri_2.create_Id = emp_2.login_Id and ri_2.remind_Type_Id = 1
and ri_2.close_Flag = 0 and ri_2.remind_Way_Id = 1 and ri_2.overtime_Remind_Owner_Flag = 1
and rrt_2.remind_Resource_Type_Id = 2
and TO_CHAR(ri_2.end_Time - 1/24,'yyyy-mm-dd hh24:mi')<=TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi')
and
(
(select count(*) from Remind_Target rt_2 where ri_2.remind_Info_Id = rt_2.remind_Info_Id)=2
and
(select count(*) from Read_Remind readR_2 where ri_2.remind_Info_Id = readR_2.remind_Entity_Id
and readR_2.remind_Resource_Type_Id = 1 and readR_2.close_Flag = 1 )=0
)
修改后:一条记录也没有,怎么回事?将Selec子句包上To_number()有记录,是3条,但是不知道原理是什么!如果不用To_number可以实现吗,因为在Hql中To_number中的子句不加载。select ri_2.remind_Info_Id as ownerId,rrt_2.remind_Resource_Type_Name as typeName ,
ri_2.content as content,emp_2.email_Address as address ,emp_2.name as addressee,emp_2.name as name
from Remind_Info ri_2, Employee emp_2, Remind_Resource_Type rrt_2
where ri_2.create_Id = emp_2.login_Id and ri_2.remind_Type_Id = 1
and ri_2.close_Flag = 0 and ri_2.remind_Way_Id = 1 and ri_2.overtime_Remind_Owner_Flag = 1
and rrt_2.remind_Resource_Type_Id = 2
and TO_CHAR(ri_2.end_Time - 1/24,'yyyy-mm-dd hh24:mi')<=TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi')
and
(
(select count(*) from Remind_Target rt_2 where ri_2.remind_Info_Id = rt_2.remind_Info_Id)
>
(select count(*) from Read_Remind readR_2 where ri_2.remind_Info_Id = readR_2.remind_Entity_Id
and readR_2.remind_Resource_Type_Id = 1 and readR_2.close_Flag = 1 )
)
还有你2个sql的不同就是前1个sql中count(*)=2 and count(*)=0
后1个是 count(*) > count(*)
后1个结果应该多了,怎么会少了?
不明白
and
(
(select count(*) from Remind_Target rt_2 where ri_2.remind_Info_Id = rt_2.remind_Info_Id)=2
and
(select count(*) from Read_Remind readR_2 where ri_2.remind_Info_Id = readR_2.remind_Entity_Id
and readR_2.remind_Resource_Type_Id = 1 and readR_2.close_Flag = 1 )=0
)
修改后代码:
and
(
(select count(*) from Remind_Target rt_2 where ri_2.remind_Info_Id = rt_2.remind_Info_Id)
>
(select count(*) from Read_Remind readR_2 where ri_2.remind_Info_Id = readR_2.remind_Entity_Id
and readR_2.remind_Resource_Type_Id = 1 and readR_2.close_Flag = 1 )
)
为什么用前一个条件能检索到结果(检索结果正确),而换上后一个检索条件一条记录都检索不出来?
and
(
to_number( (select count(*) from Remind_Target rt_2 where ri_2.remind_Info_Id = rt_2.remind_Info_Id) )
>
to_number( (select count(*) from Read_Remind readR_2 where ri_2.remind_Info_Id = readR_2.remind_Entity_Id
and readR_2.remind_Resource_Type_Id = 1 and readR_2.close_Flag = 1 ) )
)
是不是将子句的COUNT(*)包上To_number()?
如果不行的话看看这么写行不?
select To_number(*) from (select count(*) from Remind_Target rt_2 where ri_2.remind_Info_Id = rt_2.remind_Info_Id)
>
select To_number(*) from (select count(*) from Read_Remind readR_2 where ri_2.remind_Info_Id = readR_2.remind_Entity_Id
and readR_2.remind_Resource_Type_Id = 1 and readR_2.close_Flag = 1 )
where
(select count(*) from COUNTRIES where REGION_ID=2) <
(select count(*) from COUNTRIES where REGION_ID=3);
COUNTRY_ID COUNTRY_NAME REGION_ID
AR Argentina 2
AU Australia 3
BE Belgium 1
BR Brazil 2
CA Canada 2
CH Switzerland 1
CN China 3
DE Germany 1
DK Den 1
EG Egypt 4
FR France 1
HK HongKong 3
IL Israel 4
IN India 3
IT Italy 1
JP Japan 3
KW Kuwait 4
MX Mexico 2
NG Nigeria 4
NL Netherlands 1
COUNTRY_ID COUNTRY_NAME REGION_ID
SG Singapore 3
UK United Kingdom 1
US United States of America 2
ZM Zambia 4
ZW Zimbabwe 4
25 rows 。
and
(
(select count(*) from Remind_Target rt_2 where ri_2.remind_Info_Id = rt_2.remind_Info_Id)=2
and
(select count(*) from Read_Remind readR_2 where ri_2.remind_Info_Id = readR_2.remind_Entity_Id
and readR_2.remind_Resource_Type_Id = 1 and readR_2.close_Flag = 1 )=0
)
修改后代码:
and
(
(select count(*) from Remind_Target rt_2 where ri_2.remind_Info_Id = rt_2.remind_Info_Id)
>
(select count(*) from Read_Remind readR_2 where ri_2.remind_Info_Id = readR_2.remind_Entity_Id
and readR_2.remind_Resource_Type_Id = 1 and readR_2.close_Flag = 1 )
)
为什么用前一个条件能检索到结果(检索结果正确),而换上后一个检索条件一条记录都检索不出来?