帮助使用大小写和组的sql查询

问题描述：

我有一个数据库表，日期和字段叫做posneg，值是'pos'，'neu'或'neg'。帮助使用大小写和组的sql查询

我需要一个结果集，其中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并不重要，如你所见。

答

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

舍甫琴科M的回答运作良好。因为它是第一个，我首先尝试了...我将它重写为： SELECT dateadd（d，0，datediff（d，0，date））as d，posneg = SUM（CASE posneg when'pos'THEN 1 WHEN'neg'THEN -1 ELSE 0 END）FROM TABLE GROUP BY dateadd（d，0，datediff（d，0，date））order by d 非常感谢:) – Jesper 2011-04-22 08:02:08

答

试试这个：

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

帮助使用大小写和组的sql查询

相关推荐