单行子查询返回多行Oracle数据库[已解决] [英] single-row subquery returns more than one row Oracle Database [solved]

查看:885
本文介绍了单行子查询返回多行Oracle数据库[已解决]的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有两张桌子,即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 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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆