编写复杂查询的更好/正确方法-Better/Rightwaytowriteacomplexquery

4  

This should do the trick.

这应该能达到目的。

WITH Priorities AS (
   SELECT
      Priority = Row_Number() OVER (PARTITION BY X.Party2 ORDER BY Message_id DESC),
      M.*
   FROM
      dbo.Message M
      OUTER APPLY (VALUES
         (M.Sender_id, M.Receiver_Id),
         (M.Receiver_id, M.Sender_Id)
      ) X (Party1, Party2)
   WHERE
      Party1 = '101'
)
SELECT *
FROM Priorities
WHERE Priority = 1     
;

Explanation:

解释:

The real problem is that you don't care whether the selected person is the sender or the receiver. This leads to complication dealing with pulling the value from one column or the other, such as can be solved in typical fashion with a CASE statement.

真正的问题是，您不关心所选的人是发送方还是接收方。这导致从一列或另一列提取值的复杂性，例如可以用CASE语句以典型的方式解决。

However, I'm always a fan of solving things like this relationally instead of procedurally, so I simplified the problem by (basically) doubling up the data. For each source row in the table, we're generating two rows, one where the sender comes first, and one where the receiver comes first. We don't care which one is the sender or receiver, and we can just say that we're looking for Party1 to be id 101, and then want to find, for each Party2 that he exchanged a message with (and whether he was sender or receiver is irrelevant), the most recent one.

然而，我总是喜欢用关系式而不是程序化的方式来解决这些问题，所以我通过(基本上)将数据加倍来简化问题。对于表中的每个源行，我们生成了两行，其中一个是发送者最先到达的，另一个是接收者最先到达的。我们不关心哪一个是发送方或接收方,我们可以说,我们正在寻找Party1 id 101,然后想找,他交换消息的每个Party2(以及他是否发送方或接收方无关),最近的一个。

OUTER APPLY is just a trick for us to avoid doing more CTEs or nested queries (another way to write it). It's like a LEFT JOIN, but lets one use outer references (it refers to columns in table M).

对于我们来说，OUTER APPLY只是一个避免执行更多cte或嵌套查询(另一种编写的方式)的技巧。它类似于左连接，但允许使用外部引用(它引用表M中的列)。

For what it's worth, Message_id doesn't seem like a reliable way to choose the latest message. You should have a date column and order by that instead! (Just add a datetime or datetime2 column to your table, and change the ORDER BY to use it. You never know if messages could be inserted to the table out of order from when they actually occurred--them being out of order should in fact be expected. What if you have to back-insert lost messages? Identity columns are not a good way to guarantee insertion order, in my experience.

无论如何，Message_id似乎不是选择最新消息的可靠方法。您应该有一个日期列，并订购，以代替!(只需向表添加一个datetime或datetime2列，并通过使用它更改顺序。您永远不知道消息是否可以从实际发生时开始按顺序插入到表中——实际上应该预期它们是无序的。如果您必须返回插入丢失的消息怎么办?根据我的经验，身份列不是保证插入顺序的好方法。

P.S. My original take was that you wanted the most recent message sent and the most recent message received, for each sender and receiver. However, that's not what you asked for. I thought I'd leave this in for posterity since it could also be a useful answer to someone:

附注:我最初的想法是，您希望为每个发送者和接收者发送最新的消息和接收最新的消息。然而，那不是你要的。我想我应该把这个留给后人，因为它也可能是对某人有用的回答:

WITH Priorities AS (
   SELECT
      SNum = Row_Number() OVER (PARTITION BY Sender_id ORDER BY Message_id DESC),
      PNum = Row_Number() OVER (PARTITION BY Receiver_id ORDER BY Message_id DESC),
      *
   FROM
      Message
   WHERE
      '101' IN (Sender_id, Receiver_id)
)
SELECT *
FROM Priorities
WHERE 1 IN (SNum, PNum)
;

3  

If you want the most recent message to/from another user, calculate the recency (based on message_id) for the other user as both sender and receiver. The trick is to partition using the other user as the partitioning key.

如果您希望另一个用户的最新消息，则计算另一个用户作为发送方和接收方的近似值(基于message_id)。诀窍是使用其他用户作为分区键进行分区。

Then choose the first one in the outer query:

然后选择外部查询中的第一个:

select m.*
from (select m.*,
             row_number() over (partition by (case when sender_id = 101 then receiver_id else sender_id end)
                                order by message_id desc) as seqnum
      from message m
      where 101 in (sender_id, receiver_id)
     ) m
where seqnum = 1;

0  

Hope this helps

希望这有助于

SELECT * FROM (
SELECT Message_id, Sender_id, Receiver_id, Message, RANK() OVER(PARTITION BY Sender_id, Receiver_id ORDER BY Message_id DESC) OBD FROM Message
WHERE Sender_id = 101 OR Receiver_id = 101) A WHERE A.OBD = 1