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屋!