尝试向 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

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

Answer 2

它

应该在WHERE部分中，但没有第二个WHERE关键字：

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 
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 t.EventId = 123456
GROUP BY t.MicrosoftId