查询一直给我重复的记录。我怎么才能解决这个问题？

Question

我编写了一个使用2个临时表的查询。然后加入到1中。然而，我在学生访问临时表中看到了重复的记录。(查询如下)。如何修改它以删除访问临时表的重复记录？

with clientbridge as (Select *
                     from (Select visitorid, --Visid
                                  roomnumber,
                                  room_id,
                                  profid,
                                  student_id,
                                  ambc.datekey,
                                  RANK() over(PARTITION BY visitorid,student_id,profid ORDER BY ambc.datekey desc) as rn
                          from university.course_office_hour_bridge cohd
                           --where student_id = '9999999-aaaa-6634-bbbb-96fa18a9046e'
                           )
                    where rn = 1 --visitorid = '999999999999999999999999999999'---'1111111111111111111111111111111' --and pai.datekey is not null --- 00000000000000000000000000
),

-----------------Data Header Table
studentvisit as
(SELECT
                   --Visit key will allow us to track everything they did within that visit.
          distinct visid_visitorid,
                   --calcualted_visitorid,
                   uniquevisitkey,
                   --channel, -- says the room they're in. Channel might not be reliable would need to see how that operates
                   --office_list, -- add 7 to exact
                   --user_college,
                   --first_office_hour_name,
                   --first_question_time_attended,
                   studentaccountid_5,
                   profid_officenumber_8,
                   studentvisitstarttime,
                   room_id_115,
                   --date_time,
                   qqq144, --Course Name
                   qqq145, -- Course Office Hour Benefit
                   qqq146, --Course Office Hour ID
                   datekey
      FROM university.office_hour_details ohd
           --left_join niversity.course_office_hour_bridge cohd on ohd.visid_visitorid
      where DateKey >='2022-10-01' --between '2022-10-01' and '2022-10-27'
        and (qqq146 <> '')
    )
select
    *
   from clientbridge ab inner join studentvisit sv on sv.visid_visitorid = cb.visitorid

我编写了一个使用2个临时表的查询。然后加入到1中。然而，我在学生访问临时表中看到了重复的记录。(查询如下)。如何修改它以删除访问临时表的重复记录？

Answer 1

我认为，如果将两个数据集加入到您希望对数据进行排序的同一个查询中，您可能会有更好的机会，否则在第二个查询的结果中，您将忽略来自查询的排名。也许，就像->一样

;with studentvisit as
(SELECT
                   --Visit key will allow us to track everything they did within that visit.
          distinct visid_visitorid,
                   --calcualted_visitorid,
                   uniquevisitkey,
                   --channel, -- says the room they're in. Channel might not be reliable would need to see how that operates
                   --office_list, -- add 7 to exact
                   --user_college,
                   --first_office_hour_name,
                   --first_question_time_attended,
                   studentaccountid_5,
                   profid_officenumber_8,
                   studentvisitstarttime,
                   room_id_115,
                   --date_time,
                   qqq144, --Course Name
                   qqq145, -- Course Office Hour Benefit
                   qqq146, --Course Office Hour ID
                   datekey
      FROM university.office_hour_details ohd
      
           --left_join niversity.course_office_hour_bridge cohd on ohd.visid_visitorid
      where DateKey >='2022-10-01' --between '2022-10-01' and '2022-10-27'
        and (qqq146 <> '')
)
,clientbridge as (

Select 
    sv.*,
    university.course_office_hour_bridge cohd, --Visid
                                  roomnumber,
                                  room_id,
                                  profid,
                                  student_id,
                                  ambc.datekey,
                                  RANK() over(PARTITION BY sv.visitorid,sv.student_id,sv,profid ORDER BY ambc.datekey desc) as rn
                          from university.course_office_hour_bridge cohd
                          inner join studentvisit sv on sv.visid_visitorid = cohd.visitorid
)

select
    *
   from clientbridge  WHERE rn=1