我简单的做一个数据吧 fyb
fyid je
1 3.2
1 3.2
1 3.2
2 2.5
2 2.5
3 3.3
4 4.5
5 3.2我想要的是 可不可以 通过case when 判断fyid不重复就相加金额我想要的效果是
select sum(case when fyid不重复 then je end) from fyb
fyid je
1 3.2
1 3.2
1 3.2
2 2.5
2 2.5
3 3.3
4 4.5
5 3.2我想要的是 可不可以 通过case when 判断fyid不重复就相加金额我想要的效果是
select sum(case when fyid不重复 then je end) from fyb
啥叫不重复就相加?
select fyid,row_number() over (PARTITION BY fyid order by getdate()) as id,je
from fyd
)
select sum(je) from cte
where id = 1
1 3.2
1 3.2
1 3.2
2 3.5
2 1.5
3 3.3
4 4.5
5 3.2
insert into fyb select 1,3.2
insert into fyb select 1,3.2
insert into fyb select 1,3.2
insert into fyb select 2,2.5
insert into fyb select 2,2.5
insert into fyb select 3,3.3
insert into fyb select 4,4.5
insert into fyb select 5,3.2
go
select sum(je)je from fyb a where exists(select 1 from fyb where fyid=a.fyid group by fyid having count(*)=1)
/*
je
---------------------------------------
11.0(1 行受影响)*/
go
drop table fyb
insert into fyb select 1,3.2
insert into fyb select 1,3.2
insert into fyb select 1,3.2
insert into fyb select 2,2.5
insert into fyb select 2,2.5
insert into fyb select 3,3.3
insert into fyb select 4,4.5
insert into fyb select 5,3.2
go
select sum(je)je from(
select distinct fyid,je from fyb
)t
/*
je
---------------------------------------
16.7(1 行受影响)
*/
go
drop table fyb
不要限定我一定要case when ,我不会,呵呵
这个行不行?