SQL 如何删除选择查询中的重复项？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3695369/
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
SQL How to remove duplicates within select query?
提问by Tony
I have a table which looks like that:
我有一张看起来像这样的表：
As You see, there are some date duplicates, so how to select only one row for each date in that table?
如您所见，有一些日期重复，那么如何为该表中的每个日期只选择一行？
the column 'id_from_other_table' is from INNER JOIN with the table above
“id_from_other_table”列来自与上表的 INNER JOIN
采纳答案by p.campbell
You mention that there are date duplicates, but it appears they're quite unique down to the precision of seconds.
你提到有日期重复，但看起来它们在秒的精度上非常独特。
Can you clarify what precision of date you start considering dates duplicate - day, hour, minute?
您能否澄清您开始考虑重复日期的日期精度 - 天，小时，分钟？
In any case, you'll probably want to floor your datetime field. You didn't indicate which field is preferredwhen removing duplicates, so this query will prefer the last name in alphabetical order.
在任何情况下，您可能都希望将datetime 字段设置为地板。在删除重复项时，您没有指明首选哪个字段，因此此查询将优先按字母顺序显示姓氏。
SELECT MAX(owner_name),
--floored to the second
dateadd(second,datediff(second,'2000-01-01',start_date),'2000-01-01') AS StartDate
From MyTable
GROUP BY dateadd(second,datediff(second,'2000-01-01',start_date),'2000-01-01')
回答by Thomas Mueller
There are multiple rows with the same date, but the time is different. Therefore, DISTINCT start_date will not work. What you need is: cast the start_date to a DATE (so the TIME part is gone), and then do a DISTINCT:
有多行具有相同的日期，但时间不同。因此， DISTINCT start_date 将不起作用。您需要的是：将 start_date 转换为 DATE（因此 TIME 部分消失了），然后执行 DISTINCT：
SELECT DISTINCT CAST(start_date AS DATE) FROM table;
Depending on what database you use, the type name for DATE is different.
根据您使用的数据库，DATE 的类型名称是不同的。
回答by amorfis
Do you need any other information except the date? If not:
除了日期，您还需要其他信息吗？如果不：
SELECT DISTINCT start_date FROM table;
回答by ADB
Select Distinct CAST(FLOOR( CAST(start_date AS FLOAT ) )AS DATETIME) from Table
回答by Akshay
If you want to select any random single row for particular day, then
如果要选择特定日期的任何随机单行，则
SELECT * FROM table_name GROUP BY DAY(start_date)
If you want to select single entry for each user per day, then
如果您想每天为每个用户选择一个条目，那么
SELECT * FROM table_name GROUP BY DAY(start_date),owner_name
回答by Dharmesh Porwal
here is the solution for your query returning only one row for each date in that table here in the solution 'tony' will occur twice as two different start dates are there for it
这是您的查询的解决方案，该表中的每个日期仅返回一行，解决方案中的“tony”将出现两次，因为它有两个不同的开始日期
SELECT * FROM
(
SELECT T1.*, ROW_NUMBER() OVER(PARTITION BY TRUNC(START_DATE),OWNER_NAME ORDER BY 1,2 DESC ) RNM
FROM TABLE T1
)
WHERE RNM=1
回答by M T Head
You have to convert the "DateTime" to a "Date". Then you can easier select just one for the given date no matter the time for that date.
您必须将“DateTime”转换为“Date”。然后，无论该日期的时间如何，您都可以更轻松地为给定日期选择一个。
