oracle 如何防止在选择查询中选择重复的行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6064431/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can i prevent duplicate rows being selected in a select query?
提问by LaDante Riley
I have been given the task of selecting key data from an Oracle database, but I am noticing that my select is returning duplicate rows. I don't need them for my report yet I don't want them to delete them. Could someone help to get only the data that I need. I have tried the following code but this doesn't help.
我的任务是从 Oracle 数据库中选择关键数据，但我注意到我的选择返回了重复的行。我的报告不需要它们，但我不希望他们删除它们。有人可以帮助只获取我需要的数据。我已经尝试了以下代码，但这没有帮助。
SELECT distinct bbp.SUBCAR "Treadwell",
bbp.BATCH_ID "Batch ID",
bcs.SILICON "Si",
bcs.SULPHUR "S",
bcs.MANGANESE "Mn",
bcs.PHOSPHORUS "P",
to_char(bcs.SAMPLE_TIME, 'dd-MON-yy hh24:MI') "Sample Time",
to_char(bbp.START_POUR, 'dd-MON-yy hh24:MI') "Start Pour Time",
to_char(bbp.END_POUR, 'dd-MON-yy hh24:MI') "End pour Time",
bofcs.temperature "Temperature"
FROM bof_chem_sample bcs, bof_batch_pour bbp, bof_celox_sample bofcs
WHERE bcs.SAMPLE_CODE= to_char('D1')
AND bbp.BATCH_ID=bcs.BATCH_ID
AND bcs.SAMPLE_TIME>=to_date('01-jan-10')
采纳答案by bleepzter
If you look at the query translated to SQL Server type SQL you will see that there is no relation between your bofcstable and the rest of your data. Basically it is returning every record in the bofcs' temperature field, and that may be producing duplicate results?.
如果您查看转换为 SQL Server 类型 SQL 的查询，您会发现您的bofcs表与其余数据之间没有关系。基本上它正在返回 bofcs 温度场中的每条记录，这可能会产生重复的结果？。
SELECT
bbp.SUBCAR "Treadwell",
bbp.BATCH_ID "Batch ID",
bcs.SILICON "Si",
bcs.SULPHUR "S",
bcs.MANGANESE "Mn",
bcs.PHOSPHORUS "P",
to_char(bcs.SAMPLE_TIME,'dd-MON-yy hh24:MI') "Sample Time",
to_char(bbp.START_POUR, 'dd-MON-yy hh24:MI') "Start Pour Time",
to_char(bbp.END_POUR, 'dd-MON-yy hh24:MI') "End pour Time",
bofcs.temperature "Temperature"
FROM
bof_chem_sample bcs,
INNER JOIN
bof_batch_pour bbp,
ON
bbp.BATCH_ID=bcs.BATCH_ID
INNER JOIN
bof_celox_sample bofcs
ON
**-- NO RELATION B/N BOFCS and the other tables????**
WHERE
bcs.SAMPLE_CODE= to_char('D1') AND
bcs.SAMPLE_TIME>=to_date('01-jan-10')
回答by Datajam
If your SELECT statement has a DISTINCT in it, then all of the returned records have a unique combination of values across the columns you are selecting. You need to identify which columns return different values across the records you deem to be duplicated.
如果您的 SELECT 语句中包含 DISTINCT，则所有返回的记录在您选择的列中都具有唯一的值组合。您需要确定哪些列在您认为重复的记录中返回不同的值。
