两种查询库中重复值的SQL

转载于 2008-11-04 14:53:00 发布 · 71 阅读

本文介绍了一种使用SQL进行复杂数据查询的方法，通过嵌套查询和窗口函数实现对特定字段的排名，并进一步筛选出符合特定条件的数据记录。这种方法适用于需要对数据进行排名并找出重复答案的情况。

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)))