两列中任一列中的唯一值

Question

我有一个非常简单的表，其中包含几个列：

ID           (int)
Sender ID    (int)
Recipient ID (int)
Date Time    (DateTime)
MessageText  (varchar(255))

问题是，我想从这个表中检索最新的记录，其中发件人ID或收件人ID等于我的用户ID，但我也希望另一个列是唯一的。

让我举个例子，我有几行数据如下：

    ID    Sender   Recipient          Date              Message
    1       1         2       2011-01-01 01:55:00    Test Data
    2       1         2       2011-01-01 01:56:00    Test Data 2
    3       2         1       2011-01-01 01:59:00    Some more test data
    4       3         1       2011-01-02 11:50:00    Test Data 3

我希望我的查询/存储过程在这个数据上返回2行。ID 3的行和ID 4的行，但我似乎无法在单个存储过程中找出这一点。

编辑:找到了，如果有人有任何优化，请告诉我

ALTER PROCEDURE GetRootMessages
    @UserID         int
AS
BEGIN
    DECLARE @OtherUserID int

    CREATE TABLE #TempMessages 
    (
        ID int, 
        SenderID int, 
        RecipientID int, 
        MessageText text, 
        SendDate datetime not null, 
        ReadDate datetime null, 
        ReplyTo int null);

    DECLARE idCursor    Cursor FOR
    (
        SELECT DISTINCT SenderID as OtherUserID FROM Messages WHERE RecipientID = @UserID
        UNION
        SELECT DISTINCT RecipientID as OtherUserID FROM Messages WHERE SenderID = @UserID
    );

    OPEN idCursor
    FETCH NEXT FROM idCursor INTO @OtherUserID
    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO #TempMessages (ID, SenderID, RecipientID, MessageText, SendDate, ReadDate, ReplyTo)
        SELECT TOP 1 * 
        FROM Messages 
        WHERE (SenderID = @UserID AND RecipientID = @OtherUserID) OR (SenderID = @OtherUserID AND RecipientID = @UserID)
        ORDER BY SendDate DESC

        FETCH NEXT FROM idCursor INTO @OtherUserID
    END
    CLOSE idCursor
    DEALLOCATE idCursor

    SELECT * FROM #TempMessages ORDER BY SendDate DESC
    DROP TABLE #TempMessages
END

Answer 1

在Server 2005+中：

WITH ordered AS (
  SELECT
    *,
    Person1 = CASE WHEN Sender > Recipient THEN Recipient ELSE Sender END,
    Person2 = CASE WHEN Sender > Recipient THEN Sender ELSE Recipient END
  FROM atable
),
ranked AS (
  SELECT
    *,
    rank = ROW_NUMBER() OVER (PARTITION BY Person1, Person2 ORDER BY Date DESC)
  FROM ordered
)
SELECT
  ID,
  Sender,
  Recipient,
  Date,
  Message
FROM ranked
WHERE rank = 1

Answer 2

我想这就是你要找的。

;WITH tab (row, ID, Sender, Recipient, Date, Message)
AS (select row_number() over (order by date desc), * 
  from table
 where ([Sender ID] = @id or [Recipient ID] = @id)
   and ID <> [Sender ID]
   and ID <> [Recipient ID]
   and [Sender ID] <> [Recipient ID])
SELECT ID, Sender, Recipient, Date, Message
  FROM tab 
 WHERE row = 1

基本上，您将获得用户id的所有记录的列表，并给出按日期排序的数字。第一种可以作为最近的一种。

下面是我用来复制的完整脚本。创建表邮件( ID int、发件人int、收件人int、Date DateTime、MessageText varchar(255))

insert into mails values
    (1 ,      1  ,       2 ,      '2011-01-01 01:55:00' ,   'Test Data'),
    (2 ,      1  ,       2 ,      '2011-01-01 01:56:00' ,   'Test Data 2'),
    (3 ,      2  ,       1 ,      '2011-01-01 01:59:00' ,   'Some more test data'),
    (4 ,      3   ,      1 ,      '2011-01-02 11:50:00' ,   'Test Data 3')

    declare @id int
    select @id = 1

;WITH tab (row, ID, Sender, Recipient, [Date], Message)
AS (select row_number() over (order by [Date] desc), * 
  from mails
 where ([Sender] = @id or [Recipient] = @id)
   and ID <> [Sender]
   and ID <> [Recipient]
   and [Sender] <> [Recipient])
SELECT ID, Sender, Recipient, [Date], Message
  FROM tab 
 WHERE row = 1

要首先获得按最新消息排序的消息列表，可以使用以下查询：

select * 
  from mails
 where ([Sender] = @id or [Recipient] = @id)
   and ID <> [Sender]
   and ID <> [Recipient]
   and [Sender] <> [Recipient]
 order by [Date] desc

Answer 3

假设我没有错过什么，你就不能这么做吗？你想找到的ID在哪里？

select * 
 from TABLE
where (SENDER=x and RECIPIENT<>x) 
   OR (SENDER<>x and RECIPIENT=x);

或者只买最新的

SELECT * 
  FROM TABLE 
 WHERE SENDER=X 
   AND RECIPIENT <> X
   AND DATE = (SELECT MAX(DATE) 
                 FROM TABLE 
                WHERE SENDER=X)
UNION
SELECT * 
  FROM TABLE 
 WHERE SENDER <> X 
   AND RECIPIENT = X 
   AND DATE = (SELECT MAX(DATE) 
                 FROM TABLE 
                WHERE RECIPIENT=X)