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是传进去了
请问哪位知道原因吗?谢谢
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>