Oracle中重复的行 [英] Duplicate rows in Oracle

问题描述

如何防止在选择查询中选择重复的行？

How can i prevent duplicate rows being selected in a select query?

我有一个包含以下字段的表：

I have a table with the following fields:

• 名称


• 类型


• user1


• user2


• user3


• date

• name
• type
• user1
• user2
• user3
• date

我的查询需要我仅在用户在前端输入的时间选择特定用户的数据。假设用户输入1，则select查询应仅检索user1的数据。

My query requires me to select data for a particular user only at a time which is entered by the user at front end.. Say user enters 1, then the select query should retreive data for user1 only.

我目前正在这样做：

select name,type,date from table1 order by user1;

但是结果中出现了多余的行？

But I'm getting redundant rows in the result??

我在做什么错？如何避免这些重复的行？

what i am doing wrong? How to avoid these duplicate rows?

请帮助...

推荐答案

您有两个选择SELECT DISTINCT或使用GROUP BY子句。您还具有日期列，在Oracle中这也意味着有时间，因此为了安全起见，应截断日期列。即使您知道时间也不会被插入截断。优良作法是，当您看到日期时，就认为它有时间。

You have two options SELECT DISTINCT or use a GROUP BY clause. You also have a date column and in Oracle that also means there is time so to be safe you should truncate the date column. Even if you know time is no part of the insert truncate anyways. It good practice that when you see date you consider it has time.

您将遇到的下一个问题是，您不能在没有日期的列上使用ORDER BY

The next issue you'll encounter is you can not have an ORDER BY on a column that is not part of the SELECT.

我的建议是GROUP BY。

My recommendation is a GROUP BY.

SELECT user1, name, type, TRUNC(date) date
FROM Table1
GROUP BY user1, name, type, TRUNC(date)
ORDER BY user1

这篇关于Oracle中重复的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！