sql - 是否可以将这两个查询与SQLite中的联接结合在一起？

Question

是否可以在来自不同表的这两个查询之间的SQLite中创建JOIN ...

SELECT EventId as event, 
SUM(CASE WHEN name = 'computer' THEN 1 ELSE 0 END) as computer,
SUM(CASE WHEN name = 'desk' THEN 1 ELSE 0 END) as desk,
SUM(CASE WHEN name = 'chair' THEN 1 ELSE 0 END) as chair 
FROM TagsMSCV 
WHERE name IN ('computer','desk','chair') 
GROUP BY EventId

...以及类似...

SELECT COUNT(MicrosoftId) as total FROM Images WHERE Images.EventId = TagsMSCV.EventId 

...这样最重要的查询来自生产...

event   computer   desk   chair   
####    ########   ####   #####
etc...

至

event   computer   desk   chair   total
####    ########   ####   #####   #####
etc...

编辑：我写的第二个查询是试图创建联接的伪代码。作为常规选择的实际查询如下所示：

SELECT Count(MicrosoftId) FROM Images WHERE EventId IN ("10003", "10004", etc.) GROUP BY EventId

Answer 1

我先做汇总，然后再做left join：

SELECT t.EventId as event, 
       SUM(CASE WHEN t.name = 'computer' THEN 1 ELSE 0 END) as computer,
       SUM(CASE WHEN t.name = 'desk' THEN 1 ELSE 0 END) as desk,
       SUM(CASE WHEN t.name = 'chair' THEN 1 ELSE 0 END) as chair,
       i.total
FROM TagsMSCV t LEFT JOIN
     (SELECT i.eventId, COUNT(MicrosoftId) as total
      FROM Images i
      GROUP BY i.eventId
     ) i
     ON i.EventId = t.EventId 
WHERE t.name IN ('computer', 'desk', 'chair') 
GROUP BY t.EventId, i.total;

通过先进行汇总，可以防止重复计算。