Hi,各位
今天在开发的时候发现一个问题,sql如下:
select shdexrissu0_.C_SHD_ACTNO,companyiss2_.C_Y
from ShdExrIssuanceMaster shdexrissu0_, CompanyIssuance companyiss2_
where shdexrissu0_.CompanyIssuance_UUID=companyiss2_.UUID
and companyiss2_.C_COMP_CO=84 and companyiss2_.C_SECU_TY='0100'
and shdexrissu0_.C_SHD_ACTNO=31739 and companyiss2_.C_Y=1999 and exists (
select * from
(
select count(*) num from financingcompany c where c.C_COMP_CO=companyiss2_.C_COMP_CO
) ATotal where num=5
)
这里我发现在companyiss2_.C_COMP_CO 没有传给c.C_COMP_CO,因为financingcompany 中C_COMP_CO 的记录数为1,而这里查出来确实5,5是financingcompany 的总记录数,然后我把
select count(*) num from financingcompany c where c.C_COMP_CO=companyiss2_.C_COMP_CO
改成
select count(*) num from financingcompany c where companyiss2_.C_COMP_CO is null
和
select count(*) num from financingcompany c where companyiss2_.C_COMP_CO is not null
发现查处来的结果跟上面执行出来的结果是一样的
所以companyiss2_.C_COMP_CO应该没有传进去
然后我把sql改为select shdexrissu0_.C_SHD_ACTNO,companyiss2_.C_Y
from ShdExrIssuanceMaster shdexrissu0_, CompanyIssuance companyiss2_
where shdexrissu0_.CompanyIssuance_UUID=companyiss2_.UUID
and companyiss2_.C_COMP_CO=84 and companyiss2_.C_SECU_TY='0100'
and shdexrissu0_.C_SHD_ACTNO=31739 and companyiss2_.C_Y=1999 and exists (
select * from
(
select 1 from dual where companyiss2_.C_COMP_CO=84
) ATotal
)
这条sql却能查处数据来,说明companyiss2_.C_COMP_CO是传进去了
请问哪位知道原因吗?谢谢
今天在开发的时候发现一个问题,sql如下:
select shdexrissu0_.C_SHD_ACTNO,companyiss2_.C_Y
from ShdExrIssuanceMaster shdexrissu0_, CompanyIssuance companyiss2_
where shdexrissu0_.CompanyIssuance_UUID=companyiss2_.UUID
and companyiss2_.C_COMP_CO=84 and companyiss2_.C_SECU_TY='0100'
and shdexrissu0_.C_SHD_ACTNO=31739 and companyiss2_.C_Y=1999 and exists (
select * from
(
select count(*) num from financingcompany c where c.C_COMP_CO=companyiss2_.C_COMP_CO
) ATotal where num=5
)
这里我发现在companyiss2_.C_COMP_CO 没有传给c.C_COMP_CO,因为financingcompany 中C_COMP_CO 的记录数为1,而这里查出来确实5,5是financingcompany 的总记录数,然后我把
select count(*) num from financingcompany c where c.C_COMP_CO=companyiss2_.C_COMP_CO
改成
select count(*) num from financingcompany c where companyiss2_.C_COMP_CO is null
和
select count(*) num from financingcompany c where companyiss2_.C_COMP_CO is not null
发现查处来的结果跟上面执行出来的结果是一样的
所以companyiss2_.C_COMP_CO应该没有传进去
然后我把sql改为select shdexrissu0_.C_SHD_ACTNO,companyiss2_.C_Y
from ShdExrIssuanceMaster shdexrissu0_, CompanyIssuance companyiss2_
where shdexrissu0_.CompanyIssuance_UUID=companyiss2_.UUID
and companyiss2_.C_COMP_CO=84 and companyiss2_.C_SECU_TY='0100'
and shdexrissu0_.C_SHD_ACTNO=31739 and companyiss2_.C_Y=1999 and exists (
select * from
(
select 1 from dual where companyiss2_.C_COMP_CO=84
) ATotal
)
这条sql却能查处数据来,说明companyiss2_.C_COMP_CO是传进去了
请问哪位知道原因吗?谢谢
解决方案 »
- 麻烦各位瞧瞧,按指定条数批量更新的问题.(所有分都给了.)
- 现表table中有一字段value ,但字段value的值都为null ,我想给value字段设置默认值都为2 ,请问怎么设置?
- Oracle触发器问题
- 请教一个sql查询的问题
- 求助一下:oracle9i导出某表总是显示"已提交"
- 请问,如何用.net 一次执行2条oracle的sql语句?
- Oracle的官方技术文档在哪里?
- 一个oracle的存储过程,如何调用成功?
- 帮帮忙,BLOB存取过程,哪里有错?
- 想在我的笔记本上安装Oracle8i,事前查了一下安装的贴子,还真麻烦啊!看来安装问题挺普遍,希望高人能发个总结性的贴子置顶
- 疑难杂症,高手速进来...怎样将两列合并在一张表?
- 请教:start with connect by prior 使查询语句效率降低?
exists (
select * from
(
select count(*) num from financingcompany c where c.C_COMP_CO=companyiss2_.C_COMP_CO
) ATotal where num=5
)
想要表达什么意思?有意义吗?
第一个sql可以用having改写的,你试试下面的
select shdexrissu0_.C_SHD_ACTNO,companyiss2_.C_Y
from ShdExrIssuanceMaster shdexrissu0_, CompanyIssuance companyiss2_
where shdexrissu0_.CompanyIssuance_UUID=companyiss2_.UUID
and companyiss2_.C_COMP_CO=84 and companyiss2_.C_SECU_TY='0100'
and shdexrissu0_.C_SHD_ACTNO=31739 and companyiss2_.C_Y=1999 and exists (
select count(*) num from financingcompany c where c.C_COMP_CO=companyiss2_.C_COMP_CO having count(*)=5
)
SQL> SELECT * FROM test;
ID NAME
-- ----
1 aa
1 bb
SQL>
SQL> SELECT * FROM test a
2 WHERE EXISTS(SELECT * FROM (SELECT 1 FROM test b WHERE a.id=b.id)) ;
SELECT * FROM test a
WHERE EXISTS(SELECT * FROM (SELECT 1 FROM test b WHERE a.id=b.id))
ORA-00904: "A"."ID": invalid identifier
SQL>
SQL> SELECT * FROM test;
ID NAME
-- ----
1 aa
1 bb
SQL>
SQL> SELECT * FROM test a
2 WHERE EXISTS(SELECT * FROM (SELECT 1 FROM test b WHERE a.id=b.id)) ;
SELECT * FROM test a
WHERE EXISTS(SELECT * FROM (SELECT 1 FROM test b WHERE a.id=b.id))
ORA-00904: "A"."ID": invalid identifier
SQL>
SQL> SELECT * FROM test a
2 WHERE EXISTS (SELECT 1 FROM test b WHERE a.id=b.id) ;
ID NAME
-- ----
1 bb
1 aa
SQL>
SQL> SELECT * FROM test a
2 WHERE EXISTS (SELECT 1 FROM test b WHERE a.id=b.id AND EXISTS(SELECT 1 FROM test c WHERE c.name=a.name));
ID NAME
-- ----
1 aa
1 bb
SQL>
SQL> SELECT * FROM test a
2 WHERE EXISTS (SELECT 1 FROM/* test b WHERE a.id=b.id AND EXISTS*/(SELECT 1 FROM test c WHERE c.name=a.name));
SELECT * FROM test a
WHERE EXISTS (SELECT 1 FROM/* test b WHERE a.id=b.id AND EXISTS*/(SELECT 1 FROM test c WHERE c.name=a.name))
ORA-00904: "A"."NAME": invalid identifier
SQL>