All geek questions in one place

Get a list of records with multiple records on the same date

I need to return a list of record identifiers from a table that may / may not have multiple records with this record identifier on the same date. The same date criterion is the key - if an entry has three entries on 09/10/2008, then I need all three to be returned. If a record has only one record on 09/12/2008, then I do not need it.

+3
source share
15 answers
SELECT id, datefield, count(*) FROM tablename GROUP BY datefield
  HAVING count(*) > 1
+3
source

The top position (Leigh Caldwell) will not return duplicate entries and must be changed. It will identify duplicate keys. In addition, it will not work if your database does not allow the group not to include all the selection fields (many of them do not work).

, , , ( : dateadd (dd, 0, dateiff (dd, 0, @DateTime))).

, , script, :

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)
+2
SELECT RecordID
FROM aTable
WHERE SameDate IN
    (SELECT SameDate
    FROM aTable
    GROUP BY SameDate
    HAVING COUNT(SameDate) > 1)
+2

GROUP BY HAVING :

select id, count(*) from records group by date having count(*) > 1
+1
select id from tbl where date in
(select date from tbl group by date having count(*)>1)
+1

Datetime:

select * from Table
where id in (
    select alias1.id from Table alias1, Table alias2
    where alias1.id != alias2.id
        and datediff(day, alias1.date, alias2.date) = 0
)

. , , , Group by. , "". ?

+1

, - :

select
     recordID
from
    tablewithrecords as a
    left join (
        select
          count(recordID) as recordcount
        from
          tblwithrecords
        where
          recorddate='9/10/08'
     ) as b on a.recordID=b.recordID
where
     b.recordcount>1
+1
SELECT * FROM the_table WHERE ROW(record_id,date) IN 
  ( SELECT record_id, date FROM the_table 
    GROUP BY record_id, date WHERE COUNT(*) > 1 )
+1

, , . , . ,

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
+1

, , , , - :

SELECT id, COUNT(*) AS same_date FROM foo GROUP BY id, date HAVING same_date = 3;

. GROUP BY HAVING . , , .

+1

, SQL DateTime . , . DateTime , .

SQL Server :

SELECT CAST(FLOOR(CAST(CURRENT_TIMESTAMP AS float)) AS DATETIME)

DateTime float, Date , Time - , . , DateTime, .

+1
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
+1

, , . , 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

, .

+1

TrickyNixon :

(Leigh Caldwell) .

. ...

GROUP-BY, , . .

.

-, :

SELECT... FROM... GROUP BY... HAVING count (*) > 1

O (NlogN). . (Select is O (N), sort - O (NlogN), - O (N), O (N) - . , .)

.

... ...,.... a.data = b.date

. O (N ^ 2). . , 10 . , !

Ob link: http://en.wikipedia.org/wiki/Join_(SQL)

+1

Source: https://habr.com/ru/post/1696993/

More articles:


All Articles