我有一个DB表,有一个日期和一个名为posneg的字段,值是'pos', ' new '或'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
基本不重要,如你所见
SELECT
date,
posneg = SUM(
CASE posneg
WHEN 'pos' THEN 1
WHEN 'neg' THEN -1
ELSE 0
END
)
FROM atable
GROUP BY date
试试这个:
SELECT date, SUM(IF(posneg='pos',1,IF(posneg='neg',-1,0))) as total
FROM table GROUP BY date
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