mysql - 如何编写此查询有 4 个季度且值为负

Question

表_a

符号季度值
S1 Q1 -1
S1 Q2 -1
S1 Q3 -1
S1 Q4 -1
S2 Q1 -1
S2 Q3 -1
S3 Q1 -1
S3 Q3 -1
S3 Q4 -1
S3 Q2 -1

我想从 Table_a 中获取 Quarter 的 Q1 和 Q2 以及 Q3 和 Q4 值为负的符号。

所以结果应该是S1和S3。

select symbol 
from Table_a 
where Quarter='A' and 
      Quarter='B' and 
      Quarter='C' and 
      Quarter='D' and Value<0 
group by symbol

Answer 1

您可以为此使用条件聚合:

select symbol
from Table_a
where value < 0
group by symbol
having max(case when quarter = 'Q1' then 1 else 0 end) = 1
   and max(case when quarter = 'Q2' then 1 else 0 end) = 1
   and max(case when quarter = 'Q3' then 1 else 0 end) = 1
   and max(case when quarter = 'Q4' then 1 else 0 end) = 1  

• SQL Fiddle Demo