SQLite删除包含group的多行并连接两个表

Question

我有两张桌子。第一个是聊天室表，包含当前用户与朋友的消息。第二个表是线程，它保存线程聊天(用户与朋友的线程聊天-包含多条消息)

我想要的是:线程聊天1我想删除x消息，线程聊天2删除y消息，线程3删除z消息.

如何在一个sql查询中实现它？

每一表列如下：

表聊天: msgId、threadId、消息、时间戳。

表线程: threadId，displayName。

​

​

​

​

Answer 1

通过临时表存储要保存多少记录。

并通过一个技巧来模拟带有分区的ROW_NUMBER。

安装示例数据：

drop table IF EXISTS TestThreads;
create Table TestThreads (threadId INTEGER NOT NULL primary key, displayName TEXT);
drop table IF EXISTS TestChats;
create Table TestChats (msgId INTEGER NOT NULL primary key AUTOINCREMENT, threadId INTEGER, "timestamp" DATETIME, messages TEXT);

--
-- Sample Data
--
delete from TestThreads;
insert into TestThreads (threadId, displayName) values
 (1,'Superman')
,(2,'Son Goku')
,(3,'Bai Xiaochun')
;

delete from TestChats;
insert into TestChats (threadId, "timestamp", messages) values 
 (1,'2018-01-01 11:15:01','It is not')
,(1,'2018-01-01 11:15:02','an S')
,(1,'2018-01-01 11:15:03','on my world')
,(1,'2018-01-01 11:15:04','it means hope')
,(2,'2018-01-01 12:15:01','Kame')
,(2,'2018-01-01 12:15:02','Hame')
,(2,'2018-01-01 12:15:03','Hhhaaa')
,(2,'2018-01-01 12:15:04','aaaaaaaaaaaaaaaa')
,(3,'2018-01-01 13:15:01','When')
,(3,'2018-01-01 13:15:02','I start deleting')
,(3,'2018-01-01 13:15:03','I frighten')
,(3,'2018-01-01 13:15:04','even myself')
 ;

将带有数字的线程in放在一个临时表中：

--
-- create and fill temporary table
--
DROP TABLE IF EXISTS _tmpThreadsToCleanup;
CREATE TEMP TABLE _tmpThreadsToCleanup(threadId int primary key, NrToDel int);

DELETE FROM _tmpThreadsToCleanup;
INSERT INTO _tmpThreadsToCleanup(threadId, NrToDel)
SELECT threadId, 3 as NrToDel from TestThreads where displayName = 'Superman' UNION ALL
SELECT threadId, 2 as NrToDel from TestThreads where displayName = 'Son Goku' UNION ALL
SELECT threadId, 1 as NrToDel from TestThreads where displayName = 'Bai Xiaochun'
;

删除：

--
-- Delete messages based on the counts in the temporary table
--
DELETE 
FROM TestChats
WHERE msgId IN (
   SELECT c.msgId
    FROM TestChats AS c
    JOIN _tmpThreadsToCleanup AS r on (r.threadId = c.threadId)
    WHERE (select count(*) from TestChats c2 WHERE c2.threadId = c.threadId AND c2.msgID <= c.msgId) <= r.NrToDel
);

查询剩余的内容：

SELECT th.threadId, th.displayName, ch.msgId, ch."timestamp", ch.messages
FROM TestThreads AS th
LEFT JOIN TestChats AS ch on (ch.threadId = th.threadId)
ORDER BY th.threadId, ch.msgId;

结果：

threadId displayName msgId timestamp           messages
-------- ----------- ----- ------------------- ------------
1        Superman      4   2018-01-01 11:15:04 it means hope
2        Son Goku      7   2018-01-01 12:15:03 Hhhaaa
2        Son Goku      8   2018-01-01 12:15:04 aaaaaaaaaaaaaaaa
3        Bai Xiaochun 10   2018-01-01 13:15:02 I start deleting
3        Bai Xiaochun 11   2018-01-01 13:15:03 I frighten
3        Bai Xiaochun 12   2018-01-01 13:15:04 even myself

备注：

因此，为了模拟ROW_NUMBER，在每条记录上进行计数(*)上的连接，该方法应该是相当慢的。

但是，如果要删除的记录数量与所选线程相同，则可以使用限制。

DELETE 
FROM TestChats 
WHERE msgId IN (
   SELECT c.msgId
    FROM TestChats AS c
    JOIN _tmpThreadsToCleanup AS r on (r.threadId = c.threadId)
    WHERE c.threadId = TestChats.threadId
    ORDER BY c.msgID ASC
    LIMIT 2 -- Fixed number
);

如果您想做相反的事情，并保留N条记录，那么可以使用带有偏移量的限制。那么顺序应该是下降的。

这样做的好处是，这样的查询在第二次运行时不会删除额外的记录。

DELETE
FROM TestChats 
WHERE msgId IN (
   SELECT c.msgId
    FROM TestChats AS c
    JOIN _tmpThreadsToCleanup AS r on (r.threadId = c.threadId)
    WHERE c.threadId = TestChats.threadId
    ORDER BY c.msgID DESC
    LIMIT -1 OFFSET 2 -- Fixed number
);