sql - 试图为JOIN查询引入一个简单的新条件

Question

我正在使用SQLite，有人帮助我构造了很好的JOIN查询，但是现在我需要添加另一个条件，但是我很难在不破坏它的情况下将其引入查询中。

在JOIN中使用的两个表中都有一个名为EventId的列，我想介绍一个简单的条件...

WHERE EventId = 123456

在下面，您可以看到查询本身的一个有效示例，以及两个尝试引入新条件并失败的注释（因为我对SQL不好）。

SELECT t.MicrosoftId, 
    SUM(CASE WHEN name = 'necktie' THEN 1 ELSE 0 END) as 'necktie',
    SUM(CASE WHEN name = 'shirt' THEN 1 ELSE 0 END) as 'shirt',
    SUM(CASE WHEN name = 'suit' THEN 1 ELSE 0 END) as 'suit',
    SUM(CASE WHEN name = 'man' THEN 1 ELSE 0 END) as 'man',
    SUM(CASE WHEN name = 'male' THEN 1 ELSE 0 END) as 'male' 
FROM TagsMSCV t 
        /* <---- WHERE t.EventId = 123456 (fails here...) */
LEFT JOIN 
(SELECT i.MicrosoftId
FROM Images i 
GROUP BY i.MicrosoftId) i 
ON i.MicrosoftId = t.MicrosoftId 
WHERE t.name IN ('necktie','shirt','suit','man','male') 
        /* <---- AND WHERE t.EventId = 123456 (fails here too...) */
GROUP BY t.MicrosoftId

Answer 1

尝试如下

select t1.* from   ( SELECT t.MicrosoftId, 
        SUM(CASE WHEN name = 'necktie' THEN 1 ELSE 0 END) as 'necktie',
        SUM(CASE WHEN name = 'shirt' THEN 1 ELSE 0 END) as 'shirt',
        SUM(CASE WHEN name = 'suit' THEN 1 ELSE 0 END) as 'suit',
        SUM(CASE WHEN name = 'man' THEN 1 ELSE 0 END) as 'man',
        SUM(CASE WHEN name = 'male' THEN 1 ELSE 0 END) as 'male' 
    FROM TagsMSCV t WHERE t.EventId = 123456 
  and name IN ('necktie','shirt','suit','man','male') group by t.MicrosoftId 
  ) t1

您在创建子查询时犯了错误，并且作为第二个子查询不需要分组，因为没有使用聚合函数