求教sql 语句

[howardyao]

表A 如下
id value dt
1 100 2010/3/20
2 10 2010/3/20
3 20 2010/3/20
4 0 2010/3/20
5 -1 2010/3/20
6 -2 2010/3/20
7 -3 2010/3/20
8 5 2010/3/20
9 6 2010/3/20
10 -6 2010/3/20


求出某个日期 Value>=0列数 - Value< 0列数 的值
本列中Value>=0列数=6
本列中Value< 0列数=4
列结果是：2010/3/20 2

在sqlserver中，通过一个select语句能得到如上的结果吗，应该怎么写？

[sgtzzc]

select dt,
sum(case when value>=0 then 1 else -1 end)
from a
group by dt

[--小F--]

select
 dt,
 sum(case when value>=0 then 1 else -1 end)
from
 a
group by
 dt

[dawugui]

select sum(case when value >= 0 then 1 else -1 end) from a where dt = '2010-03-20'

[东那个升]

顶2层楼

[东那个升]

加一层

[dawugui]

create table a(id int,value int,dt datetime)
insert into a values(1 ,100 ,'2010/3/20')
insert into a values(2 ,10  ,'2010/3/20')
insert into a values(3 ,20  ,'2010/3/20')
insert into a values(4 ,0   ,'2010/3/20')
insert into a values(5 ,-1  ,'2010/3/20')
insert into a values(6 ,-2  ,'2010/3/20')
insert into a values(7 ,-3  ,'2010/3/20')
insert into a values(8 ,5   ,'2010/3/20')
insert into a values(9 ,6   ,'2010/3/20')
insert into a values(10,-6  ,'2010/3/20')
go

select sum(case when value >= 0 then 1 else -1 end) from a where dt = '2010-03-20'

drop table a

/*
            
----------- 
2

（所影响的行数为 1 行）
*/

[sgtzzc]

东升哥

[dawugui]

create table a(id int,value int,dt datetime)
insert into a values(1 ,100 ,'2010/3/20')
insert into a values(2 ,10  ,'2010/3/20')
insert into a values(3 ,20  ,'2010/3/20')
insert into a values(4 ,0   ,'2010/3/20')
insert into a values(5 ,-1  ,'2010/3/20')
insert into a values(6 ,-2  ,'2010/3/20')
insert into a values(7 ,-3  ,'2010/3/20')
insert into a values(8 ,5   ,'2010/3/20')
insert into a values(9 ,6   ,'2010/3/20')
insert into a values(10,-6  ,'2010/3/20')
go

select convert(varchar(10),dt,120) , sum(case when value >= 0 then 1 else -1 end) from a where dt = '2010-03-20' group by convert(varchar(10),dt,120)

drop table a

/*
                       
---------- ----------- 
2010-03-20 2

（所影响的行数为 1 行）

*/

[howardyao]

谢谢了!结贴了