代码之家 › 专栏 › 技术社区 › Balthasar

联合查询中按类型统计记录

union sqlite sql

Balthasar · 技术社区 · 7 年前

我有一个sqlite3数据库,其中有两个表“messages”和“svd”,它们包含类似的信息,并共享某些字段,例如message\u type,但除此之外,它们的差异足以保证使用单独的表。

我想创建一个查询来统计两个表上的消息类型数。

这对于单独的表很好,但在联合中使用时会产生错误的结果。我不明白出了什么问题,有人能帮我弄清楚吗?

每个表的单个消息\u类型计数:

SELECT message_type AS ID, message_types.type AS Type, count(*) as MsgCount FROM messages
       INNER JOIN message_types ON messages.message_type = message_types.ID
       GROUP BY message_type ORDER BY MsgCount DESC'

这将产生预期的结果:

ID          Type                                                   MsgCount  
----------  -----------------------                                ----------
1           Position Report Class A                                96513     
3           Position Report Class A (Response to interrogation)    46265     
18          Standard Class B CS Position Report                    11098     
11          UTC and Date Response                                  961       
10          UTC and Date Inquiry                                   452

对于SVD表,以下是查询:

SELECT message_type AS ID, message_types.type AS Type, count(*) as MsgCount FROM svd
       INNER JOIN message_types ON svd.message_type = message_types.ID
       GROUP BY message_type ORDER BY MsgCount DESC'

结果是:

ID          Type                            MsgCount  
----------  ------------------------------  ----------
5           Static and Voyage Related Data  4746      
24          Static Data Report              3250

现在,联合代码应该生成一个组合结果,但它不会。查询:

SELECT message_type AS ID, message_types.type AS Type, count(*) as MsgCount FROM messages
       INNER JOIN message_types ON messages.message_type = message_types.ID
UNION
SELECT message_type AS ID, message_types.type AS Type, count(*) as MsgCount FROM svd
       INNER JOIN message_types ON svd.message_type = message_types.ID
GROUP BY message_type ORDER BY MsgCount DESC

这将导致此不完整的结果集:

ID          Typee                                                  MsgCount  
----------  -----------------------------------------------------  ----------
1           Position Report Class A                                156063    
5           Static and Voyage Related Data                         4761      
24          Static Data Report                                     3260

您可以看到消息类型1来自消息表,而5和24来自SVD表。类型1的MsgCount似乎是“messages”表中所有消息类型的总和,因此它确实混合了一些数据,但它似乎将所有“messages”表结果合并为一个计数?

1 回复 | 直到 7 年前

Gordon Linoff 7 年前

你错过了 group by 在第一个子查询中。我还建议使用表别名:

SELECT m.message_type AS ID, mt.type AS Type, count(*) as MsgCount
FROM messages m mt INNER JOIN
     message_types mt
     ON m.message_type = mt.ID
GROUP BY m.message_type AS ID, mt.type AS Type
UNION
SELECT svd.message_type AS ID, mt.type AS Type, count(*) as MsgCount
FROM svd INNER JOIN
     message_types mt
     ON svd.message_type = mt.ID
GROUP BY svd.message_type, mt.type
ORDER BY MsgCount DESC