用户会话记录，去除重复

w_hair 2016-03-29 11:48:28

表结构

目前搜索语句是：

select COUNT(*) as Num,SenderID,ReceiverID from tb_Letter where SenderID in(select distinct SenderID as sid from tb_Letter where ReceiverID=2 ) and ( ReceiverID=2 and Receiver_Del_State=0) or ( SenderID=2 and Sender_Del_State=0) group by ReceiverID,SenderID

结果是：

我想把第四条记录去掉（2-5,5-2是两个用户之间的，实际上我只需要最后一个）
只要1、2、3、5，这个Num字段不要也可以……我只要获取两个用户之间有会话的记录并去除重复即可

表达能力有限……希望大家看懂了

...全文

281 11 打赏收藏转发到动态举报

写回复

用AI写文章

11 条回复

切换为时间正序

请发表友善的回复…

发表回复

w_hair 2016-03-31

打赏
举报

引用 10 楼 ch21st 的回复:

[quote=引用 7 楼 w_hair 的回复:] [quote=引用 6 楼 ch21st 的回复:] [quote=引用 5 楼 w_hair 的回复:] [quote=引用 1 楼 ch21st 的回复:]
with t(num,SenderID,ReceiverID) as (
  select 1,1,2 union all
  select 1,2,3 union all
  select 5,2,5 union all
  select 1,5,3
)
select distinct case when SenderID<ReceiverID then SenderID else ReceiverID end ,case when SenderID<ReceiverID then ReceiverID else SenderID end
from t 

大神……你这方法好取巧，把SenderID和ReceiverID都搞混了不过也没关系，反正我只要获取到对话间的这俩用户ID就行，每次查他们的会话记录我都要(SenderID=2 and RenderID=5) or (SenderID=5 and RenderID=2) 然后时间排序找最新的。所以位置反了也无所谓，只要记录唯一就好，感谢[/quote] 如果你想保留原来的所有其他列的数据，你可以这样处理，取序号的函数中，如果有要保留哪行的规则，放到order by后


 with t(num,SenderID,ReceiverID) as (
  select 1,1,2 union all
  select 1,2,3 union all
  select 5,2,5 union all
  select 1,5,2
 )
 SELECT * FROM (
select *, ROW_NUMBER()OVER(PARTITION BY case when SenderID<ReceiverID then SenderID else ReceiverID end ,case when SenderID<ReceiverID then ReceiverID else SenderID END ORDER BY GETDATE()) AS rn
from t 
 ) tt WHERE tt.rn=1

[/quote] 这个分区函数不太明白我的一次对话的多条记录中ReceiverID和SendreID都有可能是一样的，要是把这俩字段中值都为2的也加到order by后面，那最后的tt.rn这里我不知道怎么根据条件来取，因为我的参数就是SenderID或ReceiverID，但是这里条件是ROW_NUMBER 我不知道怎么去查了。这是我改的你之前的那个写法

select distinct case when tt.SenderID<tt.ReceiverID then tt.SenderID else tt.ReceiverID end as SenderID,case when tt.SenderID<tt.ReceiverID then tt.ReceiverID else tt.SenderID end as ReceiverID
  from (select COUNT(*) as Num,SenderID,ReceiverID from tb_Letter where SenderID in(select distinct SenderID as sid from tb_Letter where SenderID=2 or ReceiverID=2 ) and ( ReceiverID=2 and Receiver_Del_State=0) or ( SenderID=2 and Sender_Del_State=0) group by ReceiverID,SenderID) tt

[/quote] 那个Row_number实现的功能是：先将SenderID和ReceiverID从新按照同一个规则（小的在前，大的在后）重新生成两个值，其实就是不考虑收和发，单纯读取双方的值，那么2---->5和5---->2的到的是相同的值然后在这个基础上，为相同的值生成序号（这个序号的范围是具有相同的值，如果值变了，重新排序）而你需要的相同的值总仅取一条，那么就加上rn=1 （如果仅有一条，rn也是等于1）比如将你的数据弄多点：


with t(num,SenderID,ReceiverID) as (
  select 1,1,2 union all
  select 1,2,3 union ALL
  select 1,2,2 union ALL
  select 10,2,2 union ALL
    select 1,2,1 union all
  select 5,2,5 union ALL
  select 15,2,5 union ALL
    select 10,5,2 union all
  select 1,5,2
)
 SELECT * FROM (
select *, ROW_NUMBER()OVER(PARTITION BY case when SenderID<ReceiverID then SenderID else ReceiverID end ,case when SenderID<ReceiverID then ReceiverID else SenderID END ORDER BY GETDATE()) AS rn
from t 
 ) tt 

/*
1--2和2--1 认为相同，有两条。所以序号是1到2，2和5之间有4条，所以序号是1到4，最终你每种就取一条，所以再加个rn=1就行了（哪一条你想保留，就让它的rn=1,在ROW_Number的Order By里做文章）
num	SenderID	ReceiverID	rn
1	1	2	1
1	2	1	2
10	2	2	1
1	2	2	2
1	2	3	1
15	2	5	1
10	5	2	2
1	5	2	3
5	2	5	4
*/

[/quote] 噢，我明白了，先过滤分区具有相同值的记录，刚好就是两个用户间的会话，再根据这个相同值的基础生成序号，再根据序号来确保唯一性相同的只取（or仅有一条）都是rn=1，要是需要确保用户间有互动会话的话就是rn=2。这种貌似判断的条件更多了，真不错。我之前想的思路都反了

厉害厉害，灰常感谢大神

道素 2016-03-30

打赏
举报

引用 7 楼 w_hair 的回复:

[quote=引用 6 楼 ch21st 的回复:] [quote=引用 5 楼 w_hair 的回复:] [quote=引用 1 楼 ch21st 的回复:]


with t(num,SenderID,ReceiverID) as (
  select 1,1,2 union all
  select 1,2,3 union all
  select 5,2,5 union all
  select 1,5,3
)
select distinct case when SenderID<ReceiverID then SenderID else ReceiverID end ,case when SenderID<ReceiverID then ReceiverID else SenderID end
from t


 with t(num,SenderID,ReceiverID) as (
  select 1,1,2 union all
  select 1,2,3 union all
  select 5,2,5 union all
  select 1,5,2
 )
 SELECT * FROM (
select *, ROW_NUMBER()OVER(PARTITION BY case when SenderID<ReceiverID then SenderID else ReceiverID end ,case when SenderID<ReceiverID then ReceiverID else SenderID END ORDER BY GETDATE()) AS rn
from t 
 ) tt WHERE tt.rn=1

select distinct case when tt.SenderID<tt.ReceiverID then tt.SenderID else tt.ReceiverID end as SenderID,case when tt.SenderID<tt.ReceiverID then tt.ReceiverID else tt.SenderID end as ReceiverID
  from (select COUNT(*) as Num,SenderID,ReceiverID from tb_Letter where SenderID in(select distinct SenderID as sid from tb_Letter where SenderID=2 or ReceiverID=2 ) and ( ReceiverID=2 and Receiver_Del_State=0) or ( SenderID=2 and Sender_Del_State=0) group by ReceiverID,SenderID) tt


with t(num,SenderID,ReceiverID) as (
  select 1,1,2 union all
  select 1,2,3 union ALL
  select 1,2,2 union ALL
  select 10,2,2 union ALL
    select 1,2,1 union all
  select 5,2,5 union ALL
  select 15,2,5 union ALL
    select 10,5,2 union all
  select 1,5,2
)
 SELECT * FROM (
select *, ROW_NUMBER()OVER(PARTITION BY case when SenderID<ReceiverID then SenderID else ReceiverID end ,case when SenderID<ReceiverID then ReceiverID else SenderID END ORDER BY GETDATE()) AS rn
from t 
 ) tt 

/*
1--2和2--1 认为相同，有两条。所以序号是1到2，2和5之间有4条，所以序号是1到4，最终你每种就取一条，所以再加个rn=1就行了（哪一条你想保留，就让它的rn=1,在ROW_Number的Order By里做文章）
num	SenderID	ReceiverID	rn
1	1	2	1
1	2	1	2
10	2	2	1
1	2	2	2
1	2	3	1
15	2	5	1
10	5	2	2
1	5	2	3
5	2	5	4
*/

Ginnnnnnnn 2016-03-30

打赏
举报

这个是给你保留了原来的 SenderID 和 ReceiverID 的顺序的


;with t(num,SenderID,ReceiverID) as (
  select 1,1,2 union all
  select 1,2,3 union all
  select 5,2,5 union all
  select 1,5,3 union all
  select 1,3,5 union all
  select 1,3,5 
)
SELECT DISTINCT a.SenderID,a.ReceiverID
	FROM t a
	WHERE NOT EXISTS(SELECT * FROM t WHERE a.SenderID = t.ReceiverID AND a.ReceiverID = t.SenderID)

Ginnnnnnnn 2016-03-30

打赏
举报


;with t(num,SenderID,ReceiverID) as (
  select 1,1,2 union all
  select 1,2,3 union all
  select 5,2,5 union all
  select 1,5,3 union all
  select 1,3,5 
)
SELECT CASE WHEN SenderID > ReceiverID THEN SenderID ELSE ReceiverID END AS SenderID,
		CASE WHEN SenderID < ReceiverID THEN SenderID ELSE ReceiverID END AS ReceiverID
	FROM t
	GROUP BY CASE WHEN SenderID > ReceiverID THEN SenderID ELSE ReceiverID END,
			 CASE WHEN SenderID < ReceiverID THEN SenderID ELSE ReceiverID END

w_hair 2016-03-30

打赏
举报

引用 6 楼 ch21st 的回复:

[quote=引用 5 楼 w_hair 的回复:] [quote=引用 1 楼 ch21st 的回复:]


with t(num,SenderID,ReceiverID) as (
  select 1,1,2 union all
  select 1,2,3 union all
  select 5,2,5 union all
  select 1,5,3
)
select distinct case when SenderID<ReceiverID then SenderID else ReceiverID end ,case when SenderID<ReceiverID then ReceiverID else SenderID end
from t


 with t(num,SenderID,ReceiverID) as (
  select 1,1,2 union all
  select 1,2,3 union all
  select 5,2,5 union all
  select 1,5,2
 )
 SELECT * FROM (
select *, ROW_NUMBER()OVER(PARTITION BY case when SenderID<ReceiverID then SenderID else ReceiverID end ,case when SenderID<ReceiverID then ReceiverID else SenderID END ORDER BY GETDATE()) AS rn
from t 
 ) tt WHERE tt.rn=1

select distinct case when tt.SenderID<tt.ReceiverID then tt.SenderID else tt.ReceiverID end as SenderID,case when tt.SenderID<tt.ReceiverID then tt.ReceiverID else tt.SenderID end as ReceiverID
  from (select COUNT(*) as Num,SenderID,ReceiverID from tb_Letter where SenderID in(select distinct SenderID as sid from tb_Letter where SenderID=2 or ReceiverID=2 ) and ( ReceiverID=2 and Receiver_Del_State=0) or ( SenderID=2 and Sender_Del_State=0) group by ReceiverID,SenderID) tt

道素 2016-03-29

打赏
举报

引用 5 楼 w_hair 的回复:

[quote=引用 1 楼 ch21st 的回复:]


with t(num,SenderID,ReceiverID) as (
  select 1,1,2 union all
  select 1,2,3 union all
  select 5,2,5 union all
  select 1,5,3
)
select distinct case when SenderID<ReceiverID then SenderID else ReceiverID end ,case when SenderID<ReceiverID then ReceiverID else SenderID end
from t


 with t(num,SenderID,ReceiverID) as (
  select 1,1,2 union all
  select 1,2,3 union all
  select 5,2,5 union all
  select 1,5,2
 )
 SELECT * FROM (
select *, ROW_NUMBER()OVER(PARTITION BY case when SenderID<ReceiverID then SenderID else ReceiverID end ,case when SenderID<ReceiverID then ReceiverID else SenderID END ORDER BY GETDATE()) AS rn
from t 
 ) tt WHERE tt.rn=1

w_hair 2016-03-29

打赏
举报

引用 1 楼 ch21st 的回复:


with t(num,SenderID,ReceiverID) as (
  select 1,1,2 union all
  select 1,2,3 union all
  select 5,2,5 union all
  select 1,5,3
)
select distinct case when SenderID<ReceiverID then SenderID else ReceiverID end ,case when SenderID<ReceiverID then ReceiverID else SenderID end
from t

w_hair 2016-03-29

打赏
举报

引用 1 楼 ch21st 的回复:


with t(num,SenderID,ReceiverID) as (
  select 1,1,2 union all
  select 1,2,3 union all
  select 5,2,5 union all
  select 1,5,3
)
select distinct case when SenderID<ReceiverID then SenderID else ReceiverID end ,case when SenderID<ReceiverID then ReceiverID else SenderID end
from t

经测这位大神的貌似可行，我再多加点数据测试下

w_hair 2016-03-29

打赏
举报

引用 2 楼 yupeigu 的回复:

改成这样，加上括号；

SELECT  COUNT(*) AS Num ,

        SenderID ,

        ReceiverID

FROM    tb_Letter

WHERE   SenderID IN ( SELECT DISTINCT

                                SenderID AS sid

                      FROM      tb_Letter

                      WHERE     ReceiverID = 2 )

        AND (( ReceiverID = 2

              AND Receiver_Del_State = 0

            )

        OR ( SenderID = 2

             AND Sender_Del_State = 0

           ))

你把这里
WHERE SenderID IN ( SELECT DISTINCT
SenderID AS sid
FROM tb_Letter
WHERE ReceiverID = 2 )
的where后面直接少了个or SenderID=2所以distinct 出来的结果就不正确了，在后面继续搜的结果也是不对的

下面是你改过的，上面的是原来的

LongRui888 2016-03-29

打赏
举报

改成这样，加上括号；

SELECT  COUNT(*) AS Num ,
        SenderID ,
        ReceiverID
FROM    tb_Letter
WHERE   SenderID IN ( SELECT DISTINCT
                                SenderID AS sid
                      FROM      tb_Letter
                      WHERE     ReceiverID = 2 )
        AND (( ReceiverID = 2
              AND Receiver_Del_State = 0
            )
        OR ( SenderID = 2
             AND Sender_Del_State = 0
           ))

道素 2016-03-29

打赏
举报


with t(num,SenderID,ReceiverID) as (
  select 1,1,2 union all
  select 1,2,3 union all
  select 5,2,5 union all
  select 1,5,3
)
select distinct case when SenderID<ReceiverID then SenderID else ReceiverID end ,case when SenderID<ReceiverID then ReceiverID else SenderID end
from t