sqlite - 如何在一个查询中执行多个查询？

Question

我正在构建一个聊天应用程序，使用Firebase发送和接收消息。一旦发送或接收消息，就将其存储到SQLite，如下所示。现在是最近的聊天屏幕，在观察SQLite数据库时，我需要在单个查询中的所有唯一聊天中的最后一条消息，这些唯一聊天中未读消息的数量。

Mid(STRING)     | SentBy | SentTo | message | readTime | sentTime| Type
----------------+--------+--------+---------+----------+---------+------
A               | AA     | JD     | M1      |   1      |    0    |  S
B               | JD     | AA     | M2      |   2      |    1    |  s
C               | AA     | JD     | M3      |   3      |    2    |  s
D               | AB     | JD     | m5      |   null   |    3    |  s
E               | AA     | JC     | M1      |   5      |    4    |  s
F               | JD     | AB     | M2      |   6      |    5    |  s
G               | AA     | JD     | M3      |   7      |    6    |  s
H               | AA     | JC     | m5      |   8      |    7    |  s
I               | AA     | JD     | M1      |   null   |    8    |  s
J               | JD     | AA     | M2      |  10      |    9    |  s
K               | AA     | JD     | M3      |  11      |    10   |  s
L               | AB     | JC     | m5      |  12      |    11   |  s
M               | AA     | JD     | M1      |  13      |    12   |  s
N               | JC     | AA     | M2      |  14      |    13   |  s
O               | AB     | JD     | M3      |  15      |    14   |  s
P               | JC     | JD     | m5      |  16      |    15   |  s

我试过了

SELECT *,COUNT() FROM messagesTable GROUP BY min ( sentBy, sentTo ), max( sentBy , sentTo ) ORDER BY sentTime desc

该查询为我提供了sendTo和sentBy每种组合的最后一条消息。但是我还需要知道该组合有多少未读的消息。我想对每一行都运行查询

SELECT COUNT() FROM messagesTable WHERE sentBy = message.sentBy, sentTo = message.sentTo, readTime = null

如何在一个查询中运行两个查询？

Answer 1

您必须按(sentby, sentto)的组合进行分组，并使用直的count(*)来获取消息的总数，使用条件聚合可以获取未读消息的数量。
然后将结果加入表中，以获取最后一条消息：

select 
  g.user1, g.user2, g.lasttime, m.message lastmessage, 
  g.totalcounter, g.unreadcounter
from messagestable m inner join (
  select 
    min(sentby, sentto) user1, max(sentby, sentto) user2,
    max(senttime) lasttime, count(*) totalcounter,
    sum(case when readtime is null then 1 else 0 end) unreadcounter
  from messagestable
  group by user1, user2
) g 
on g.user1 = min(m.sentby, m.sentto) and g.user2 = max(m.sentby, m.sentto) 
and g.lasttime = m.senttime
order by g.lasttime desc

请参见 demo。
结果：

| user1 | user2 | lasttime | lastmessage | totalcounter | unreadcounter |
| ----- | ----- | -------- | ----------- | ------------ | ------------- |
| JC    | JD    | 15       | m5          | 1            | 0             |
| AB    | JD    | 14       | M3          | 3            | 1             |
| AA    | JC    | 13       | M2          | 3            | 0             |
| AA    | JD    | 12       | M1          | 8            | 1             |
| AB    | JC    | 11       | m5          | 1            | 0             |