(select count() )和(select count() )比较条件问题？

high_land 2007-12-06 04:41:35

我的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 )

)

...全文

1024 14 打赏收藏转发到动态举报

写回复

用AI写文章

14 条回复

切换为时间正序

请发表友善的回复…

发表回复

high_land 2007-12-13

打赏
举报

我已经试过了，两个条件换个次序没有用。

qfsb_p 2007-12-12

打赏
举报

把两个条件换个次序试试，可能是=进行了数据类型的转换了

Croatia 2007-12-12

打赏
举报

只是看你给出的这个句子来说，理解起来是比较困难。

你先把这个条件去掉。在SQL里面，把这两个count都作为字段取出来，看看都是一些什么。

high_land 2007-12-11

打赏
举报

主要代码：检索条件
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 )

)
为什么用前一个条件能检索到结果（检索结果正确），而换上后一个检索条件一条记录都检索不出来？

Croatia 2007-12-07

打赏
举报

修改一下你的SQL句子，把两个Count取出来看看呢？

Croatia 2007-12-07

打赏
举报

一个测试的例子：

select * from COUNTRIES
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 Denmark 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 。

Croatia 2007-12-07

打赏
举报

我没有用To_number函数，得到了检索结果。

是不是其他地方有问题？

rexyudl 2007-12-07

打赏
举报

将Selec子句包上To_number()?
是不是将子句的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 )

high_land 2007-12-07

打赏
举报

to_number用的位置但是我不想用to_number，因为在Hql中to_number（）括号里的子句不能加载！
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 ) )

)

high_land 2007-12-07