两种查询库中重复值的SQL

原创于 2008-11-04 14:53:00 发布 · 91 阅读

CC 4.0 BY-SA版权

本文介绍了一种复杂的SQL查询方法，该方法通过使用子查询、窗口函数（如RANK()）和聚合函数（如COUNT()）来找出在数据库表中重复回答问题的用户。通过两层嵌套的子查询实现对特定字段进行分组并计数，同时利用窗口函数对记录进行排序。

SELECT *
FROM   (
           SELECT *
                  ,RANK() OVER(ORDER BY who_answ) c2
           FROM   Ft_Question
       ) b
WHERE b.c2 IN (
                SELECT a.c1
                FROM   (
                           SELECT RANK() OVER(ORDER BY who_answ) AS c1
                           FROM   Ft_Question
                       ) a
                GROUP BY c1
                HAVING Count(c1) > 1)

----------------------
SELECT     id, Tid, question, answer, who_quest, IP_quest, who_answ, state, time_quest, time_answer
FROM         Ft_Question
WHERE     (who_answ IN
                          (SELECT     who_answ
                            FROM          Ft_Question AS Ft_Question_1
                            GROUP BY who_answ
                            HAVING      (COUNT(*) > 1)))