SQL 获取同一日期有多个条目的记录列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/76724/
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
Get list of records with multiple entries on the same date
提问by
I need to return a list of record id's from a table that may/may not have multiple entries with that record id on the same date. The same date criteria is key - if a record has three entries on 09/10/2008, then I need all three returned. If the record only has one entry on 09/12/2008, then I don't need it.
我需要从一个表中返回一个记录 ID 列表，该表在同一日期可能/可能没有多个具有该记录 ID 的条目。相同的日期标准是关键——如果记录在 2008 年 9 月 10 日有三个条目，那么我需要返回所有三个条目。如果记录只有 09/12/2008 的一个条目，那么我不需要它。
回答by Leigh Caldwell
SELECT id, datefield, count(*) FROM tablename GROUP BY datefield
HAVING count(*) > 1
回答by Scott Nichols
Since you mentioned needing all three records, I am assuming you want the data as well. If you just need the id's, you can just use the group by query. To return the data, just join to that as a subquery
既然您提到需要所有三个记录，我假设您也需要这些数据。如果您只需要 id，则可以使用 group by 查询。要返回数据，只需将其作为子查询加入
select * from table
inner join (
select id, date
from table
group by id, date
having count(*) > 1) grouped
on table.id = grouped.id and table.date = grouped.date
回答by Bob Probst
The top post (Leigh Caldwell) will not return duplicate records and needs to be down modded. It will identify the duplicate keys. Furthermore, it will not work if your database doesn't allows the group by to not include all select fields (many do not).
顶帖 (Leigh Caldwell) 不会返回重复记录，需要向下修改。它将识别重复的键。此外，如果您的数据库不允许 group by 不包含所有选择字段（许多不包含），则它将不起作用。
If your date field includes a time stamp then you'll need to truncate that out using one of the methods documented above ( I prefer: dateadd(dd,0, datediff(dd,0,@DateTime)) ).
如果您的日期字段包含时间戳，那么您需要使用上面记录的方法之一将其截断（我更喜欢： dateadd(dd,0, datediff(dd,0,@DateTime)) ）。
I think Scott Nichols gave the correct answer and here's a script to prove it:
我认为 Scott Nichols 给出了正确答案，这里有一个脚本可以证明这一点：
declare @duplicates table (
id int,
datestamp datetime,
ipsum varchar(200))
insert into @duplicates (id,datestamp,ipsum) values (1,'9/12/2008','ipsum primis in faucibus')
insert into @duplicates (id,datestamp,ipsum) values (1,'9/12/2008','Vivamus consectetuer. ')
insert into @duplicates (id,datestamp,ipsum) values (2,'9/12/2008','condimentum posuere, quam.')
insert into @duplicates (id,datestamp,ipsum) values (2,'9/13/2008','Donec eu sapien vel dui')
insert into @duplicates (id,datestamp,ipsum) values (3,'9/12/2008','In velit nulla, faucibus sed')
select a.* from @duplicates a
inner join (select id,datestamp, count(1) as number
from @duplicates
group by id,datestamp
having count(1) > 1) b
on (a.id = b.id and a.datestamp = b.datestamp)
回答by Bob Probst
SELECT RecordID
FROM aTable
WHERE SameDate IN
(SELECT SameDate
FROM aTable
GROUP BY SameDate
HAVING COUNT(SameDate) > 1)
回答by Bob Probst
TrickyNixon writes;
TrickyNixon 写道；
The top post (Leigh Caldwell) will not return duplicate records and needs to be down modded.
顶帖 (Leigh Caldwell) 不会返回重复记录，需要向下修改。
Yet the question doesn't ask about duplicate records. It asks about duplicate record-ids on the same date...
然而，这个问题并没有询问重复记录。它询问同一日期的重复记录 ID...
GROUP-BY,HAVING seems good to me. I've used it in production before.
GROUP-BY,HAVING 对我来说似乎很好。我以前在生产中使用过它。
.
.
Something to watch out for:
需要注意的事项：
SELECT ... FROM ... GROUP BY ... HAVING count(*)>1
SELECT ... FROM ... GROUP BY ... HAVING count(*)>1
Will, on most database systems, run in O(NlogN) time. It's a good solution. (Select is O(N), sort is O(NlogN), group by is O(N), having is O(N) -- Worse case. Best case, date is indexed and the sort operation is more efficient.)
在大多数数据库系统上，将在 O(NlogN) 时间内运行。这是一个很好的解决方案。（选择是 O(N)，排序是 O(NlogN)，分组是 O(N)，有是 O(N)——最坏的情况。最好的情况，日期被索引并且排序操作更有效。）
.
.
Select ... from ..., .... where a.data = b.date
选择 ... from ..., .... 其中 a.data = b.date
Granted only idiots do a Cartesian join. But you're looking at O(N^2) time. For some databases, this also creates a "temporary" table. It's all insignificant when your table has only 10 rows. But it's gonna hurt when that table grows!
当然，只有白痴才会进行笛卡尔连接。但是您正在查看 O(N^2) 时间。对于某些数据库，这还会创建一个“临时”表。当您的表只有 10 行时，这一切都无关紧要。但是当那张桌子变大时它会受伤！
回答by jkramer
I'm not sure I understood your question, but maybe you want something like this:
我不确定我是否理解你的问题，但也许你想要这样的东西：
SELECT id, COUNT(*) AS same_date FROM foo GROUP BY id, date HAVING same_date = 3;
This is just written from my mind and not tested in any way. Read the GROUP BY and HAVING section here. If this is not what you meant, please ignore this answer.
这只是从我的脑海中写出来的，并没有以任何方式进行测试。阅读此处的 GROUP BY 和 HAVING 部分。如果这不是您的意思，请忽略此答案。
回答by Chris Wuestefeld
Note that there's some extra processing necessary if you're using a SQL DateTime field. If you've got that extra time data in there, then you can't just use that column as-is. You've got to normalize the DateTime to a single value for all records contained within the day.
请注意，如果您使用 SQL 日期时间字段，则需要进行一些额外的处理。如果您在那里获得了额外的时间数据，那么您就不能按原样使用该列。对于当天包含的所有记录，您必须将 DateTime 规范化为单个值。
In SQL Server here's a little trick to do that:
在 SQL Server 中，这里有一个小技巧可以做到这一点：
SELECT CAST(FLOOR(CAST(CURRENT_TIMESTAMP AS float)) AS DATETIME)
You cast the DateTime into a float, which represents the Date as the integer portion and the Time as the fraction of a day that's passed. Chop off that decimal portion, then cast that back to a DateTime, and you've got midnight at the beginning of that day.
您将 DateTime 转换为浮点数，它将日期表示为整数部分，将时间表示为过去一天的分数。砍掉那个小数部分，然后将其转换回 DateTime，并且当天开始时您已经有午夜了。
回答by Eduardo Campa?ó
SELECT id, count(*) INTO #tmp FROM tablename WHERE date = @date GROUP BY id HAVING count(*) > 1 SELECT * FROM tablename t WHERE EXISTS (SELECT 1 FROM #tmp WHERE id = t.id) DROP TABLE tablename
回答by Dave Lievense
Without knowing the exact structure of your tables or what type of database you're using it's hard to answer. However if you're using MS SQL and if you have a true date/time field that has different times that the records were entered on the same date then something like this should work:
如果不知道您的表的确切结构或您使用的数据库类型，就很难回答。但是，如果您使用的是 MS SQL，并且您有一个真实的日期/时间字段，其中记录在同一日期输入的时间不同，那么这样的事情应该可以工作：
select record_id,
convert(varchar, date_created, 101) as log date,
count(distinct date_created) as num_of_entries
from record_log_table
group by convert(varchar, date_created, 101), record_id
having count(distinct date_created) > 1
Hope this helps.
希望这可以帮助。
回答by Dave Lievense
GROUP BYwith HAVINGis your friend:
GROUP BY有HAVING是你的朋友：
select id, count(*) from records group by date having count(*) > 1
