有个表taba
ID GL_CODE CURRENCY
00121 USD1001 USD
00121 USD1002 USD
00121 0001001 CNY
00121 0001002 CNY
00122 HKD2001 HKD
00122 HKD2020 HKD
00122 0002001 CNY
00122 0002020 CNY请教如何根据CURRENCY为‘USD’选出ID相同的一组数据?
00121 USD1001 USD
00121 USD1002 USD
00121 0001001 CNY
00121 0001002 CNY
ID GL_CODE CURRENCY
00121 USD1001 USD
00121 USD1002 USD
00121 0001001 CNY
00121 0001002 CNY
00122 HKD2001 HKD
00122 HKD2020 HKD
00122 0002001 CNY
00122 0002020 CNY请教如何根据CURRENCY为‘USD’选出ID相同的一组数据?
00121 USD1001 USD
00121 USD1002 USD
00121 0001001 CNY
00121 0001002 CNY
typecode ID GL_CODE CURRENCY
01 00121 USD1001 USD
01 00121 USD1002 USD
01 00121 0001001 CNY
01 00121 0001002 CNY
01 00122 HKD2001 HKD
01 00122 HKD2020 HKD
01 00122 0002001 CNY
01 00122 0002020 CNY
02 00121 USD1001 USD
02 00121 USD1002 USD
02 00121 0001001 CNY
02 00121 0001002 CNY
ID GL_CODE CURRENCY
---------- ---------- ----------
00121 USD1001 USD
00121 USD1002 USD
00121 0001001 CNY
00121 0001002 CNY
00122 HKD2001 HKD
00122 HKD2020 HKD
00122 0002001 CNY
00122 0002020 CNY
8 rows selected
SQL> select * from taba a where exists (select id from taba b where a.id = b.id and b.currency = 'USD');
ID GL_CODE CURRENCY
---------- ---------- ----------
00121 USD1001 USD
00121 USD1002 USD
00121 0001001 CNY
00121 0001002 CNY
SQL> select * from taba a where exists (select id from taba b where a.id = b.id and b.currency = 'HKD');
ID GL_CODE CURRENCY
---------- ---------- ----------
00122 HKD2001 HKD
00122 HKD2020 HKD
00122 0002001 CNY
00122 0002020 CNY
SQL>
TYPECODE ID GL_CODE CURRENCY
-------- ---------- ---------- ----------
01 00121 USD1001 USD
01 00121 USD1002 USD
01 00121 0001001 CNY
01 00121 0001002 CNY
01 00122 HKD2001 HKD
01 00122 HKD2020 HKD
02 00122 0002001 CNY
02 00122 0002020 CNY
8 rows selectedSQL> select typecode,ID,GL_CODE,CURRENCY from taba a where exists (select id from taba b where a.id = b.id and b.typecode ='01' and b.currency = 'USD');
TYPECODE ID GL_CODE CURRENCY
-------- ---------- ---------- ----------
01 00121 USD1001 USD
01 00121 USD1002 USD
01 00121 0001001 CNY
01 00121 0001002 CNY
1 01 00121 USD1001 USD
2 01 00121 USD1002 USD
3 01 00121 0001001 CNY
4 01 00121 0001002 CNY
5 01 00122 HKD1001 HKD
6 01 00122 HKD1002 HKD
7 01 00122 0001001 CNY
8 01 00122 0001002 CNY
9 02 00121 USD1001 USD
10 02 00121 USD1002 USD
11 02 00121 0001001 CNY
12 02 00121 0001002 CNY
13 02 00122 CNY1001 CNY
14 02 00122 CNY1002 CNY
15 02 00122 0001001 CNY
16 02 00122 0001002 CNY怎样选出CNY的数呢?
13 02 00122 CNY1001 CNY
14 02 00122 CNY1002 CNY
15 02 00122 0001001 CNY
16 02 00122 0001002 CNY
select typecode,ID,GL_CODE,CURRENCY from taba a where exists (select id from taba b where a.id = b.id and a.typecode = b.typecode and b.typecode ='01' and b.currency = 'CNY');
谢谢你!