单行子查询返回多行Oracle数据库[已解决] [英] single-row subquery returns more than one row Oracle Database [solved]
问题描述
我有两张桌子,即PESANMASUK和KONTAK。在PESANMASUK我有5个文件:IDMASUK,TANGGALMASUK,JAM,NOMERHP,ISIPESAN。和KONTAK我有3个文件:IDKONTAK,NAMA,NOHP。
PESANMASUK 表:
IDMASUK TANGGALMASUK JAM NOMERHP ISIPESAN
1 09/08/2015 09:00 + 62847776 尝试 短信
2 08/08/2015 10:00 + 62856790 plase come in
3 08/08/2015 06:00 + 6283444 温泉n> 你 和 me
KONTAK 表:
IDKONTAK NAMA NOHP
1 RIAN + 62847776
2 ALFIAN + 62856790
这个结果是我想要的:
IDMASUK TANGGALMASUK JAM NOMERHP NAMA ISIPESAN
1 09/08/2015 09:00 + 62847776 RIAN 尝试 sms
2 08/08/2015 10:00 < span class =code-leadattribute> + 62856790 ALFIAN plase 来
3 08/08/2015 < span class =code-leadattribute> 06:00 + 6283444 你 和 me
这是我的oracle查询:
SELECT IDMASUK,TANGGALMASUK,JAM,NOMERHP,ISIPESAN,
( SELECT NAMA FROM KONTAK WHERE NOHP IN ( SELECT NOMERHP FROM PESANMASUK)) AS NAMA
FROM PESANMASUK
我收到这样的错误:
< pre lang =SQL> ORA-01427:单行子查询返回多行
I想要在PESANMASUK的NOMERHP和KONTAK的NAMA之间进行匹配并返回所有行。
如何解决这个问题?
基本上,你需要加入数据 [ ^ ]:
SELECT Table1.Field1,Table2.Field1
FROM 表1 INNER JOIN 表2 ON Table1.PK = Table2.FK
其中
PK
= 主键 [ ^ ],
FK
= 外键 [ ^ ]。
有关详细信息,请参阅: SQL Jo的可视化表示ins [ ^ ]
SELECT IDMASUK,TANGGALMASUK,JAM,NOMERHP,ISIPESAN,
( SELECT NAMA FROM KONTAK WHERE NOHP IN ( SELECT NOMERHP FROM PESANMASUK)< span class =code-keyword>和 ROWNUM< = 1 ) AS NAMA
FROM PESANMASUK
请试试这个
i have two tables namely PESANMASUK anda KONTAK. in PESANMASUK i have 5 fileds : IDMASUK, TANGGALMASUK, JAM, NOMERHP, ISIPESAN . and KONTAK i have 3 fileds : IDKONTAK, NAMA, NOHP.
PESANMASUK table :
IDMASUK TANGGALMASUK JAM NOMERHP ISIPESAN
1 09/08/2015 09:00 +62847776 try sms
2 08/08/2015 10:00 +62856790 plase come in
3 08/08/2015 06:00 +6283444 you and me
KONTAK table :
IDKONTAK NAMA NOHP
1 RIAN +62847776
2 ALFIAN +62856790
and this result is i want :
IDMASUK TANGGALMASUK JAM NOMERHP NAMA ISIPESAN
1 09/08/2015 09:00 +62847776 RIAN try sms
2 08/08/2015 10:00 +62856790 ALFIAN plase come in
3 08/08/2015 06:00 +6283444 you and me
this is my oracle query :
SELECT IDMASUK, TANGGALMASUK, JAM, NOMERHP, ISIPESAN,
(SELECT NAMA FROM KONTAK WHERE NOHP IN (SELECT NOMERHP FROM PESANMASUK)) AS NAMA
FROM PESANMASUK
I got error like this :
ORA-01427: single-row subquery returns more than one row
I wanna matching between NOMERHP in PESANMASUK and NAMA in KONTAK and return all rows.
how can I solve this??
Basically, you need to join data[^]:
SELECT Table1.Field1, Table2.Field1 FROM Table1 INNER JOIN Table2 ON Table1.PK = Table2.FK
Where
PK
= Primary Key[^],
FK
= Foreign Key[^].
For further information, please see: Visual Representation of SQL Joins[^]
SELECT IDMASUK, TANGGALMASUK, JAM, NOMERHP, ISIPESAN, (SELECT NAMA FROM KONTAK WHERE NOHP IN (SELECT NOMERHP FROM PESANMASUK) and ROWNUM <= 1) AS NAMA FROM PESANMASUK
please try this
这篇关于单行子查询返回多行Oracle数据库[已解决]的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!