sum(case when meno='1' then 1 when meno='-1' then -1 end )
declare @t table (id int ,no int,meno int) insert @t select 1,345,-1 insert @t select 2,678,1 insert @t select 3,789,1 insert @t select 3,789,-1 select sum(meno) as [Count],no from @t group by no
declare @AA table(id int,no varchar(50),meno int)insert into @AA(id ,no ,meno) select 1,'345',-1 union all select 2,'678',1 union all select 3,'789',1 select id,no, Count=sum(case when meno='1' then no+1 else no-1 end ) from @aa group by id,no
我用count(case when meno='1' then 1 when meno='-1' then -1 end ) 这样为什么统计不出正确的结果? case when meno='1' then count(id) else 0 end case when men0 = '-1' then count(id) else 0 end)
if object_Id ('表t') is not null drop table 表t go create table 表t ( id int, no int, meno int ) insert into 表t select 1,345,-1 union all select 2,678,1 union all select 3,789,1 go select id ,no=case when meno=-1 then no-1 else no+1 end ,meno from 表t
count(no)+sum(meno)才对
不太明白,是不是sum(no*meno)
sum是累加
count只是统计
sum(case when meno='1' then 1 when meno='-1' then -1 end )
insert @t select 1,345,-1
insert @t select 2,678,1
insert @t select 3,789,1
insert @t select 3,789,-1
select sum(meno) as [Count],no from @t group by no
declare @AA table(id int,no varchar(50),meno int)insert into @AA(id ,no ,meno)
select 1,'345',-1 union all
select 2,'678',1 union all
select 3,'789',1 select id,no,
Count=sum(case when meno='1' then no+1 else no-1 end )
from @aa
group by id,no
我用count(case when meno='1' then 1 when meno='-1' then -1 end )
这样为什么统计不出正确的结果?
case when meno='1' then count(id) else 0 end
case when men0 = '-1' then count(id) else 0 end)
if object_Id ('表t') is not null
drop table 表t
go
create table 表t
( id int,
no int,
meno int
)
insert into 表t select 1,345,-1
union all select 2,678,1
union all select 3,789,1
go
select id ,no=case when meno=-1 then no-1 else no+1 end ,meno from 表t