求教这个查询该如何写呐
Message表中有字段 ID FromID ToID
比如现在有数据是
ID FromID ToID
1 103 102
2 102 103
3 103 102
4 104 102
5 105 102
from代表发送方，to代表接收方
怎么样能按发送方和接收方捆绑分组呢，比如参与者是103和102的时候，不管103发给102，还是102发给103都算是这一组的，然后取出每组最近的，如模拟数据中的话，取出的应该是
ID FromID ToID
1 103 102
4 104 102
5 105 102
求教大家，我想到的都是比较繁琐的方式，用游标什么的。感激不尽。
[解决办法]
create table Message(ID int,FromID int,ToID int)
insert into message select 1,103,102
insert into message select 2,102,103
insert into message select 3,103,102
insert into message select 4,104,102
insert into message select 5,105,102
go
select * from message a
where not exists(select 1 from message where id<a.id and fromid in(a.fromid,a.toid) and toid in(a.fromid,a.toid))
/*
ID FromID ToID
----------- ----------- -----------
1 103 102
4 104 102
5 105 102
(3 行受影响)
*/
go
drop table message
[解决办法]
declare @message table(id int,fromid int,toid int)
insert into @message
select 1 , 103 , 102 union all
select 2 , 102 , 103 union all
select 3 , 103 , 102 union all
select 4 , 104 , 102 union all
select 5 , 105 , 102
--测试
select min(a.id) id ,a.fromid,a.toid
from @message as a inner join @message as b on (a.fromid<>b.toid and a.toid<>b.fromid and a.id<>b.id )
group by a.fromid,a.toid
[解决办法]
USE Tempdb
GO
IF NOT OBJECT_ID(N'Tempdb..#1') IS NULL
DROP TABLE #1
CREATE TABLE #1(ID INT,FromID INT,ToID INT)
INSERT INTO #1
SELECT 1,103,102 UNION ALL
SELECT 2,102,103 UNION ALL
SELECT 3,103,102 UNION ALL
SELECT 4,104,102 UNION ALL
SELECT 5,105,102
GO
WITH CTE AS( SELECT ID,CASE WHEN FromID>=ToID THEN ToID ELSE FromID END MI,CASE WHEN FROMID>=TOID THEN FromID ELSE ToID END MA FROM #1)
,CTE2 AS(SELECT MAX(ID) ID FROM CTE GROUP BY MI,MA)
SELECT T2.* FROM CTE2 T1 LEFT JOIN #1 T2 ON T1.ID=T2.ID
