SELECT "MR_ON_LINE"."PATIENT_ID",
"MR_ON_LINE"."VISIT_ID",
"PAT_MASTER_INDEX"."NAME",
................ WHERE ("PAT_MASTER_INDEX"."PATIENT_ID" = "MR_ON_LINE"."PATIENT_ID")
....................UNION
SELECT "MR_ON_LINE"."PATIENT_ID",
"MR_ON_LINE"."VISIT_ID",
"PAT_MASTER_INDEX"."NAME",
.................
WHERE ("PAT_MASTER_INDEX"."PATIENT_ID" = "MR_ON_LINE"."PATIENT_ID")
.......................报错:单行子查询返回多行
"MR_ON_LINE"."VISIT_ID",
"PAT_MASTER_INDEX"."NAME",
................ WHERE ("PAT_MASTER_INDEX"."PATIENT_ID" = "MR_ON_LINE"."PATIENT_ID")
....................UNION
SELECT "MR_ON_LINE"."PATIENT_ID",
"MR_ON_LINE"."VISIT_ID",
"PAT_MASTER_INDEX"."NAME",
.................
WHERE ("PAT_MASTER_INDEX"."PATIENT_ID" = "MR_ON_LINE"."PATIENT_ID")
.......................报错:单行子查询返回多行
在查询中会遇到 UNION ALL,它的用法和union一样,只不过union含有distinct的功能,它会把两张表了重复的记录去掉,而union all不会,所以从效率上,union all 会高一点,但在实际中用到的并不是很多.
和我发得问题有冲突啊
请教各位老大了
from tb1
where tb1.column_name=(select tb2.column_name from tb2 where ... )-- 因为外层查询是用的等于号(tb1.column_name=),
-- 所以对应的子查询(select tb2.column_name from tb2 where ... )
-- 就应该有且只有一条记录行返回对正确,
-- 此时若对应的子查询有多条记录,就会返回像楼主所说的错误:单行子查询返回多行
"MR_ON_LINE"."VISIT_ID",
"PAT_MASTER_INDEX"."NAME",
"PAT_MASTER_INDEX"."SEX",
"MR_ON_LINE"."STATUS",
"MR_ON_LINE"."REQUEST_DOCTOR_ID",
"MR_ON_LINE"."REQUEST_DATE_TIME",
"PATS_IN_HOSPITAL"."PATIENT_CONDITION",
"PATS_IN_HOSPITAL"."DOCTOR_IN_CHARGE",
"PAT_MASTER_INDEX"."DATE_OF_BIRTH",
"PATS_IN_HOSPITAL"."DIAGNOSIS",
"PAT_MASTER_INDEX"."CHARGE_TYPE",
"PAT_MASTER_INDEX"."IDENTITY",
"PAT_MASTER_INDEX"."UNIT_IN_CONTRACT",
(SELECT "PAT_VISIT"."ADMISSION_DATE_TIME"
FROM "PAT_VISIT"
WHERE "PAT_VISIT"."PATIENT_ID" = "MR_ON_LINE"."PATIENT_ID"
AND "PAT_VISIT"."VISIT_ID" = "MR_ON_LINE"."VISIT_ID") ADMISSION_DATE_TIME,
TO_DATE('3000-01-01', 'YYYY-MM-DD') DISCHARGE_DATE_TIME,
Coalesce("PATS_IN_HOSPITAL"."DEPT_CODE_LEND",
"PATS_IN_HOSPITAL"."DEPT_CODE") DEPT_CODE,
"PATS_IN_HOSPITAL"."BED_NO" BED_NO,
(SELECT BED_LABEL
FROM BED_REC
WHERE BED_REC.BED_NO = "PATS_IN_HOSPITAL"."BED_NO"
AND BED_REC.WARD_CODE = "PATS_IN_HOSPITAL"."WARD_CODE") BED_LABEL,
(SELECT DEPT_NAME
FROM DEPT_DICT
WHERE DEPT_DICT.DEPT_CODE =
Coalesce("PATS_IN_HOSPITAL"."DEPT_CODE_LEND",
"PATS_IN_HOSPITAL"."DEPT_CODE")) "DEPT_NAME",
"PATS_IN_HOSPITAL"."ADM_WARD_DATE_TIME" "ADM_WARD_DATE_TIME",
"PAT_MASTER_INDEX"."INP_NO",
'在院' INOUT_HOS,
(SELECT "PAT_VISIT"."INP_SERIAL_NO"
FROM "PAT_VISIT"
WHERE "PAT_VISIT"."PATIENT_ID" = "MR_ON_LINE"."PATIENT_ID"
AND "PAT_VISIT"."VISIT_ID" = "MR_ON_LINE"."VISIT_ID") INP_SERIAL_NO,
'0' selected,
'' add_over
FROM "PAT_MASTER_INDEX", "MR_ON_LINE", "PATS_IN_HOSPITAL"
WHERE ("PAT_MASTER_INDEX"."PATIENT_ID" = "MR_ON_LINE"."PATIENT_ID")
and ("MR_ON_LINE"."PATIENT_ID" = "PATS_IN_HOSPITAL"."PATIENT_ID")
and ("MR_ON_LINE"."VISIT_ID" = "PATS_IN_HOSPITAL"."VISIT_ID")
and ("MR_ON_LINE"."STATUS" = '0')
and (("MR_ON_LINE"."PATIENT_ID", "MR_ON_LINE"."VISIT_ID") NOT IN
(SELECT mr_spotcheck_detail.PATIENT_ID, mr_spotcheck_detail.VISIT_ID
FROM mr_spotcheck_detail))
这个是union左边的 单独执行OK
"MR_ON_LINE"."VISIT_ID",
"PAT_MASTER_INDEX"."NAME",
"PAT_MASTER_INDEX"."SEX",
"MR_ON_LINE"."STATUS",
"MR_ON_LINE"."REQUEST_DOCTOR_ID",
"MR_ON_LINE"."REQUEST_DATE_TIME",
' ' AS PATIENT_CONDITION,
"PAT_VISIT"."DOCTOR_IN_CHARGE",
"PAT_MASTER_INDEX"."DATE_OF_BIRTH",
' ' AS DIAGNOSIS,
"PAT_MASTER_INDEX"."CHARGE_TYPE",
"PAT_MASTER_INDEX"."IDENTITY",
"PAT_MASTER_INDEX"."UNIT_IN_CONTRACT",
"PAT_VISIT"."ADMISSION_DATE_TIME" ADMISSION_DATE_TIME,
"PAT_VISIT"."DISCHARGE_DATE_TIME",
"PAT_VISIT"."DEPT_DISCHARGE_FROM",
"PAT_VISIT"."DISCHARGE_BED_NO" BED_NO,
(SELECT BED_LABEL
FROM BED_REC
WHERE BED_REC.BED_NO = "PAT_VISIT"."DISCHARGE_BED_NO"
AND BED_REC.WARD_CODE = "PAT_VISIT"."DISCHARGE_WARD_CODE") BED_LABEL,
"DEPT_DICT"."DEPT_NAME",
(SELECT "TRANSFER"."ADMISSION_DATE_TIME"
FROM "TRANSFER"
WHERE "PAT_VISIT"."PATIENT_ID" = "TRANSFER"."PATIENT_ID"
AND "PAT_VISIT"."VISIT_ID" = "TRANSFER"."VISIT_ID"
and DEPT_TRANSFERED_TO is null) ADM_WARD_DATE_TIME,
"PAT_MASTER_INDEX"."INP_NO",
'已出院' INOUT_HOS,
(SELECT "PAT_VISIT"."INP_SERIAL_NO"
FROM "PAT_VISIT"
WHERE "PAT_VISIT"."PATIENT_ID" = "MR_ON_LINE"."PATIENT_ID"
AND "PAT_VISIT"."VISIT_ID" = "MR_ON_LINE"."VISIT_ID") INP_SERIAL_NO,
'0' selected,
'' add_over
FROM "PAT_MASTER_INDEX", "MR_ON_LINE", "PAT_VISIT", DEPT_DICT
WHERE ("PAT_MASTER_INDEX"."PATIENT_ID" = "MR_ON_LINE"."PATIENT_ID")
and ("MR_ON_LINE"."PATIENT_ID" = "PAT_VISIT"."PATIENT_ID")
and ("MR_ON_LINE"."VISIT_ID" = "PAT_VISIT"."VISIT_ID")
and ("MR_ON_LINE"."STATUS" = '0')
and "DEPT_DICT"."DEPT_CODE" = "PAT_VISIT"."DEPT_DISCHARGE_FROM"
and (("MR_ON_LINE"."PATIENT_ID", "MR_ON_LINE"."VISIT_ID") NOT IN
(SELECT mr_spotcheck_detail.PATIENT_ID, mr_spotcheck_detail.VISIT_ID
FROM mr_spotcheck_detail))
and (("MR_ON_LINE"."PATIENT_ID", "MR_ON_LINE"."VISIT_ID") NOT IN
(SELECT PATIENT_ID, VISIT_ID FROM PATS_IN_HOSPITAL))这个是union右边的 单独执行也OK
"这个是union左边的 单独执行OK这个是union右边的 单独执行也OK”
那用union是不会出现错误的、仔细看下
union all 没问题
这些代码是实现一个软件的一个功能,但是我们一个同事他那里就不报错,就我报错后来我把他用得软件拷过来还是报这个错看来是数据库的问题了,可以从这代码上看不出来啊,2边运行都正常,怎么一union就给我报错呢纠结...........
union肯定出问题 确定2个查询返回的列的类型一定要对齐
两边是字段好像没对齐,不过我同事那里又是对的 "MR_ON_LINE"."REQUEST_DATE_TIME",
' ' AS PATIENT_CONDITION,
"PAT_VISIT"."DOCTOR_IN_CHARGE",
"PAT_MASTER_INDEX"."DATE_OF_BIRTH",
' ' AS DIAGNOSIS,这' ' as 是什么意思啊, 以前没用过oracle啊,边工作边学