Блог

У меня есть очень простая таблица, которая содержит пару столбцов:

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

Дело в том, что я хотел бы получить последнюю запись из этой таблицы, где идентификатор отправителя или идентификатор получателя равен моему идентификатору пользователя, НО я также хочу, чтобы другой столбец был уникальным.

Позвольте мне привести вам пример, у меня есть несколько строк со следующими данными:

     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 строки для этих данных. Строки с идентификатором 3 и одна с идентификатором 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
  

В SQL 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
  

Я думаю, это то, что вы ищете.

 ;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
  

По сути, вы получаете список всех записей для вашего идентификатора пользователя и присваиваете им номер, упорядоченный по дате. Затем первое может быть выбрано как самое последнее.

Ниже приведен полный сценарий, который я использовал для воспроизведения. создайте таблицу писем (идентификатор int, отправитель int, получатель int, [Дата] DateTime, переменная MessageText(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
  

Версия 1:

 SELECT Sender, Recipient
FROM Messages
GROUP BY Sender, Recipient
HAVING COUNT(*) = 1
  

В результате вы получите список уникальных пар отправителей и получателей, но вам нужно будет использовать это как подзапрос или как часть объединения, чтобы получить список уникальных сообщений.

Версия 2:

 SELECT *
FROM Messages O
WHERE NOT EXIST
(SELECT *
 FROM Messages I
 WHERE I.ID <> O.ID AND I.Sender = O.Sender AND I.Recipient = O.Recipient)
  

Также возможно записать это как внешнее соединение слева, но я не хочу пробовать.

Предполагая, что я ничего не упускаю, не могли бы вы это сделать? Где X — идентификатор, который вы хотите найти?

 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)
  

Я думаю, что ammianus находится на правильном пути.

В принципе, вы хотите использовать DISTINCT, чтобы получить только уникальные, и UNION, чтобы объединить два запроса, которые вы получите.

Попробуйте этот:

 declare @UserID int
select @UserID = 1

-- Union all messages in one table and filter them on our userid
declare @Union table(ID int, UserID int, SendDate datetime, Message nvarchar(50))
insert into @Union(ID, UserID, SendDate, Message)
select ID, RecipientID, SendDate, Message
from Messages
where SenderID = @UserID
union
select ID, SenderID, SendDate, Message
from Messages
where RecipientID = @UserID

    -- Gets max dates for every correspond user
declare @MaxDates table(UserID int, SendDate datetime)
insert into @MaxDates(UserID, SendDate)
select UserID, max(SendDate)
from @Union
group by UserID

    -- Gets result
select u.*
from @Union u
    inner join @MaxDates md on (u.UserID = md.UserID) and (u.SendDate = md.SendDate)