Oracle SQL:SQL查询产生的结果是结果的四倍，并且数据无法正确排序 [英] Oracle SQL: SQL query producing 4 times the results and data wont order correctly

问题描述

Select DISTINCT * From
(
SELECT "WORK_CENTER"."EQNO" AS E1,
"WORK_CENTER"."CNTR_TYPE",
"WORK_CENTER"."CNTR_DESC",
"WORK_CENTER"."MFGCELL",
"WORK_CENTER"."MFG_TYPE",
"WORK_CENTER"."CUSER1",
"WORK_CENTER"."CUSER2",
"WORK_CENTER"."CUSER3",
"WORK_CENTER"."CUSER4",
"WORK_CENTER"."CUSER5",
"WORK_CENTER"."NUSER1",
"WORK_CENTER"."NUSER2",
"WORK_CENTER"."NUSER3",
"WORK_CENTER"."NUSER4",
"WORK_CENTER"."NUSER5",
"UD_DATA"."CUSER"
FROM   "IQMS"."UD_DATA" "UD_DATA" 
 FULL OUTER JOIN "IQMS"."WORK_CENTER" "WORK_CENTER" ON  "UD_DATA"."PARENT_ID"="WORK_CENTER"."ID"
LEFT OUTER JOIN "IQMS"."UD_COLS" "UD_COLS" ON "UD_DATA"."UD_COLS_ID" = "UD_COLS"."ID" 
WHERE  "WORK_CENTER"."MFG_TYPE"='INJECTION' AND "UD_COLS"."ID"='16'),

(SELECT 
"WORK_CENTER"."EQNO" AS E2,
"UD_DATA"."CUSER" AS "U2"
FROM   "IQMS"."UD_DATA" "UD_DATA" 
FULL OUTER JOIN "IQMS"."WORK_CENTER" "WORK_CENTER" ON "UD_DATA"."PARENT_ID"="WORK_CENTER"."ID"
LEFT OUTER JOIN "IQMS"."UD_COLS" "UD_COLS" ON "UD_DATA"."UD_COLS_ID" = "UD_COLS"."ID" 
WHERE "UD_COLS"."ID"='17') ORDER BY E1,E2

E2是我用来组织数据的一种方式，我不希望此列显示. 这是理想的结果(不显示表格格式不正确的空白列和歉意):

E2 is something I tried to use to organise the data, I don't want this column to show. This is the desired result(not showing blank columns and apologies for badly formatted table):

EQNO | CNTR_TYPE | CNTR_DESC | MFGCELL | MFG_TYPE | CUSER | U2

001 | 110T-40MM | DEMAG SYSTEM 110-430 | MOLDING | INJECTION | 1000 | test1

002 | 150T-25MM | DEMAG SYSTEM 150-320 | MOLDING | INJECTION | 2000 | test2

003 | 150T-45MM | DEMAG SYSTEM 150-610 | MOLDING | INJECTION | 3000 | test3

005 | 150T-45MM | DEMAG EXTRA  500-610 | MOLDING | INJECTION | 4000 | test4

但是我得到的是:

EQNO | CNTR_TYPE | CNTR_DESC | MFGCELL | MFG_TYPE | CUSER | U2 | E2

001 | 110T-40MM | DEMAG SYSTEM 110-430 | MOLDING | INJECTION | 1000 | test1 | 001

001 | 110T-40MM | DEMAG SYSTEM 110-430 | MOLDING | INJECTION | 1000 | test2 | 002

001 | 110T-40MM | DEMAG SYSTEM 110-430 | MOLDING | INJECTION | 1000 | test3 | 003

001 | 110T-40MM | DEMAG SYSTEM 110-430 | MOLDING | INJECTION | 1000 | test4 | 005

002 | 150T-25MM | DEMAG SYSTEM 150-320 | MOLDING | INJECTION | 2000 | test1 | 001

002 | 150T-25MM | DEMAG SYSTEM 150-320 | MOLDING | INJECTION | 2000 | test2 | 002

002 | 150T-25MM | DEMAG SYSTEM 150-320 | MOLDING | INJECTION | 2000 | test3 | 003

002 | 150T-25MM | DEMAG SYSTEM 150-320 | MOLDING | INJECTION | 2000 | test4 | 005

003 | 150T-45MM | DEMAG SYSTEM 150-610 | MOLDING | INJECTION | 3000 | test1 | 001

003 | 150T-45MM | DEMAG SYSTEM 150-610 | MOLDING | INJECTION | 3000 | test2 | 002

003 | 150T-45MM | DEMAG SYSTEM 150-610 | MOLDING | INJECTION | 3000 | test3 | 003

003 | 150T-45MM | DEMAG SYSTEM 150-610 | MOLDING | INJECTION | 3000 | test4 | 005

005 | 150T-45MM | DEMAG EXTRA  500-610 | MOLDING | INJECTION | 4000 | test1 | 001 

005 | 150T-45MM | DEMAG EXTRA  500-610 | MOLDING | INJECTION | 4000 | test2 | 002

005 | 150T-45MM | DEMAG EXTRA  500-610 | MOLDING | INJECTION | 4000 | test3 | 003

005 | 150T-45MM | DEMAG EXTRA  500-610 | MOLDING | INJECTION | 4000 | test4 | 005

联盟实际上并不起作用，因为它只是将"CUSER"和"CUSER AS U2"两列放在一起，因为它们实际上是同一列.

Union doesn't really work as it just puts the two columns "CUSER" and "CUSER AS U2" together since they are the same column really.

我该怎么做才能解决此问题和/或我做错了什么?(我想这很有可能)

What can I do to fix this and/or is there something I'm doing very wrong?(I guess that would be most likely)

即使被告知要看什么也将非常有帮助.我已经为此工作了几天，但没有取得太大进展.

Even just being told what to look at would be extremely helpful. I've been working on this for a couple of days and not making much progress.

推荐答案

将创建这两个选择的叉积.结果像独立表一样处理.我删除了，因为无法详细说明. 如果有帮助的话，很好.

The crossproduct of those two selects is created. The results are handled like independent tables. I deleted because I could not elaborate more. If it was helpfull, great.

这篇关于Oracle SQL:SQL查询产生的结果是结果的四倍，并且数据无法正确排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！