從 Blackberry 上的 SQLite 數據庫中獲取記錄的查詢

我想從 SQLite 數據庫中獲取 Blackberry 的數據。

一張表有四列：ID, FROM, TO, TIME

我想根據FROM、 和TO值來獲取記錄。

條件1：

FROM = [from value] AND TO = [to value]

第二個條件是：

FROM = TO AND TO = FROM

它應該返回最後 20 條記錄。

目前還不是很清楚你在追求什麼。所以，這個猜測可能是錯誤的：

SELECT *
FROM Messages
WHERE ( fromUserID = @From AND toUserID = @To )
  OR ( fromUserID = @To AND toUserID = @From )
ORDER BY TimeSent DESC
 LIMIT 20 ;

這可以作為一個例子：

CREATE TABLE Messages
(
   MessageID INT
   , fromUserID INT
   , toUserID INT
   , TimeSent DATETIME
);

INSERT INTO Messages VALUES (1, 10, 11, GetDate());
INSERT INTO Messages VALUES (2, 11, 10, GetDate());
INSERT INTO Messages VALUES (3, 11, 12, GetDate());
INSERT INTO Messages VALUES (4, 11, 10, GetDate());
INSERT INTO Messages VALUES (5, 11, 12, GetDate());
INSERT INTO Messages VALUES (6, 11, 10, GetDate());
INSERT INTO Messages VALUES (7, 11, 12, GetDate());
INSERT INTO Messages VALUES (8, 11, 12, GetDate());
INSERT INTO Messages VALUES (9, 11, 10, GetDate());
INSERT INTO Messages VALUES (10, 11, 12, GetDate());
INSERT INTO Messages VALUES (11, 11, 10, GetDate());
INSERT INTO Messages VALUES (12, 11, 12, GetDate());

DECLARE @t TABLE (MessageID INT);
DECLARE @From INT;
DECLARE @To INT;
DECLARE cur CURSOR FOR
SELECT fromUserID, toUserID
FROM Messages
GROUP BY fromUserID, toUserID;
OPEN cur;
FETCH NEXT FROM cur INTO @From, @To;
WHILE @@FETCH_STATUS=0
BEGIN
   INSERT INTO @t
   SELECT TOP(2) MessageID
   FROM Messages
   WHERE fromUserID = @From AND toUserID = @To
   ORDER BY TimeSent DESC;
   FETCH NEXT FROM cur INTO @From, @To;
END
CLOSE cur;
DEALLOCATE cur;
SELECT * 
FROM Messages 
   INNER JOIN @t tt ON Messages.MessageID = tt.MessageID

這將為 fromUserID 和 toUserID 之間的每個對話返回 2 條最新消息。將 更改TOP(2)為TOP(20)以獲取 20 個最近的對話。

我確信這不是最好的方法。我相信它可以以基於集合的方式完成，而不是此處顯示的基於游標的過程方式。

引用自：https://dba.stackexchange.com/questions/23759