SQL在特定日期选择重复项

Question

我正在尝试查找同一天发生的重复条目。我有一个数据库表，基本上只包含 ID、USERNAME 和 DATE_CREATED。

我需要一个 select，大致可以做到这一点:

SELECT USERNAME,DATE_CREATED 
FROM THE_TABLE WHERE {more than one USERNAME exists on date TRUNC(DATE_CREATED)}

是否可以不创建仅通过 SELECT 的过程来实现？感谢您的建议。

Answer 1

SELECT  USERNAME, TRUNC(DATE_CREATED)
FROM    THE_TABLE
GROUP   BY
        USERNAME, TRUNC(DATE_CREATED)
HAVING  COUNT(*) > 1;

例子:

SELECT  USERNAME, TRUNC(DATE_CREATED)
FROM    
(
        SELECT  'a' username, sysdate date_created from dual union all
        SELECT  'a' username, sysdate date_created from dual union all
        SELECT  'b' username, sysdate date_created from dual union all
        SELECT  'b' username, sysdate date_created from dual
)
GROUP   BY
        USERNAME, TRUNC(DATE_CREATED)
HAVING  COUNT(*) > 1;
/*
a   2013-06-18 00:00:00
b   2013-06-18 00:00:00
*/

要在输出中获取完整日期，它稍微复杂一些:

SELECT  DISTINCT
        username
,       date_created
FROM    the_table ot
WHERE   EXISTS
        (
            SELECT  1
            FROM    the_table it
            WHERE   TRUNC(ot.date_created) = TRUNC(it.date_created)
            AND     ot.username            = it.username
            GROUP   BY
                    USERNAME, TRUNC(DATE_CREATED)
            HAVING  COUNT(*) > 1
        )
;
/*
a   2013-06-18 12:48:40
b   2013-06-18 12:48:40
*/

必须访问表两次 + 需要 DISTINCT 关键字。是的，性能可能会下降。