Oracle SQL - 依靠一列并显示来自其他列/记录的信息

Question

我是一个完全的SQL新手所以这可能是一个愚蠢的问题。我想在TR_VALUE中查找重复值。NEW VALUE并显示其他记录中其他列的附加列信息。例如TR_VALUE.OLD_VALUE。

下面的查询运行良好，如果我删除TR_VALUE。OLD_VALUE，但我想显示关于重复项的其他数据。

SELECT  
"TR_VALUE"."NEW_VALUE", "TR_VALUE"."OLD_VALUE", 
COUNT("TR_VALUE"."NEW_VALUE") 
FROM
(("DB1"."WORKORDER" "WORKORDER" 
LEFT OUTER JOIN 
"DB1"."TR_VALUE" "TR_VALUE" ON "WORKORDER"."FG_LOTNO" = "TR_VALUE"."NEW_VALUE") 
LEFT OUTER JOIN 
"DB1"."STANDARD" "STANDARD" ON "WORKORDER"."STANDARD_ID" = "STANDARD"."ID") 
LEFT OUTER JOIN 
"DB1"."TR_BATCH" "TR_BATCH" ON "TR_VALUE"."TR_BATCH_ID" = "TR_BATCH"."ID"
GROUP BY 
"TR_VALUE"."NEW_VALUE"
HAVING 
COUNT("TR_VALUE"."NEW_VALUE") > 1

Answer 1

如果你想让你的查询工作，只需添加1列组子句-

SELECT  
"TR_VALUE"."NEW_VALUE",
"TR_VALUE"."OLD_VALUE", 
COUNT("TR_VALUE"."NEW_VALUE") 
FROM
(("DB1"."WORKORDER" "WORKORDER" 
LEFT OUTER JOIN 
"DB1"."TR_VALUE" "TR_VALUE" ON "WORKORDER"."FG_LOTNO" = "TR_VALUE"."NEW_VALUE") 
LEFT OUTER JOIN 
"DB1"."STANDARD" "STANDARD" ON "WORKORDER"."STANDARD_ID" = "STANDARD"."ID") 
LEFT OUTER JOIN 
"DB1"."TR_BATCH" "TR_BATCH" ON "TR_VALUE"."TR_BATCH_ID" = "TR_BATCH"."ID"
GROUP BY 
"TR_VALUE"."NEW_VALUE",
"TR_VALUE"."OLD_VALUE"
HAVING 
COUNT("TR_VALUE"."NEW_VALUE") > 1