帮助使用 case 和 group 进行 sql 查询

Question

我有一个带有日期和一个名为 posneg 的字段的数据库表，该值是“pos”、“neu”或“neg”。

我需要一个结果集，其中添加 posneg 值，然后按日期分组。就像这样

2010-10-03    5   (on that date it could be 12 pos, 5 neu and 7 neg)
2010-10-04    -3  (on that date maybe 10 pos, 2 neu and 13 neg)
...and so on

基本上，neu 并不重要，正如你所看到的。

Answer 1

SELECT
  date,
  posneg = SUM(
    CASE posneg
      WHEN 'pos' THEN 1
      WHEN 'neg' THEN -1
      ELSE 0
    END
  )
FROM atable
GROUP BY date

Answer 2

试试这个：

SELECT date, SUM(IF(posneg='pos',1,IF(posneg='neg',-1,0))) as total
FROM table GROUP BY date

Answer 3

with tab as(

select cast('12/12/2010' as smalldatetime) as dt, 'neg' as posneg 
union all
select cast('12/12/2010' as smalldatetime) as dt, 'pos' as posneg 
union all
select cast('12/12/2010' as smalldatetime) as dt, 'pos' as posneg 
union all
select cast('12/12/2010' as smalldatetime) as dt, 'pos' as posneg 
union all
select cast('12/15/2010' as smalldatetime) as dt, 'neg' as posneg 
union all
select cast('12/15/2010' as smalldatetime) as dt, 'neg' as posneg 
union all
select cast('12/15/2010' as smalldatetime) as dt, 'pos' as posneg
)

select dt,
sum(case when posneg='pos' then 1
         when posneg='neg' then -1
         else 0 end) as total
         from tab
         group by dt