可以稍微压缩一下，但我已将其拆分为简单的步骤，希望能让它更容易理解.

It's possible to compress this a bit, but I've split it into simple steps to hopefully make it a little easier to follow.

-- Sample data from the question.
declare @msg table (Sender varchar(32), Receiver varchar(32), [Message] varchar(max), [Date] date);
insert @msg
    (Sender, Receiver, [Message], [Date])
values
    ('John','Dennis', 'How are you', '2015-06-06'),
    ('Dennis', 'John', 'Hi', '2015-06-05'),
    ('Tom', 'John', 'How much is it?', '2015-06-04'),
    ('Tom', 'John', 'Did you buy it?', '2015-06-03'),
    ('Robin', 'Tom', 'Hey man', '2015-06-03'),
    ('Dennis', 'John', 'What up', '2015-06-02'),
    ('John', 'Tom', 'Call me', '2015-06-01');

-- The name of the user whose conversations you want to find.
declare @UserName varchar(32) = 'John';

-- Step 1: Create columns [Participant1] and [Participant2] that will be the same for
--         each pair of users regardless of who's the sender and who the receiver.
with NameOrderCTE as
(
    select 
        Participant1 = case when Sender < Receiver then Sender else Receiver end,
        Participant2 = case when Sender < Receiver then Receiver else Sender end,
        *
    from
        @msg
),

-- Step 2: For each distinct pair of participants, create a [Sequence] number that 
--         puts the messages in reverse chronological order.
MessageSequenceCTE as
(
    select
        *,
        [Sequence] = row_number() over (partition by Participant1, Participant2 order by [Date] desc)
    from
        NameOrderCTE
)

-- Step 3: Get the most recent ([Sequence] = 1) messages for each conversation
--         involving the target user.
select
    Participant = case @UserName when Sender then Receiver else Sender end,
    [Message],
    [Date]
from
    MessageSequenceCTE
where
    @UserName in (Sender, Receiver) and
    [Sequence] = 1;