--05用法 select *, rmb-sum(usd)over() as ye from table1 a where usid='205'
--> -->
if not object_id('Tempdb..#') is null drop table # Go Create table #([id] int,[usid] int,[usd] int,[rmb] int) Insert # select 1,204,200,500 union all select 2,204,100,null union all select 3,204,100,null union all select 4,205,200,500 union all select 5,205,100,null union all select 6,205,50,null Go Select *,rmb-sum(usd)over() as ye from # where [usid]='205' (6 個資料列受到影響) id usid usd rmb ye ----------- ----------- ----------- ----------- ----------- 4 205 200 500 150 5 205 100 NULL NULL 6 205 50 NULL NULL(3 個資料列受到影響)
create table tb(id int,usid int,usd int,rmb int) insert into tb select 1,204,200,500 insert into tb select 2,204,100,null insert into tb select 3,204,100,null insert into tb select 4,205,200,500 insert into tb select 5,205,100,null insert into tb select 6,205,50,nullselect a.usid,a.usd,a.rmb,ye=a.rmb -isnull(b.usd,0) from tb a join ( select usid,sum(usd) as usd from tb group by usid )b on a.usid=b.usid where a.usid=205 usid usd rmb ye 205 200 500 150 205 100 NULL NULL 205 50 NULL NULL
反正是求一条/// select id,usid,usd,rmb,ye=rmb-(select sum(usd) from tb where usid=a.usid) from tb a where usid=205
select
*,
rmb-sum(usd)over() as ye
from
table1 a
where
usid='205'
if not object_id('Tempdb..#') is null
drop table #
Go
Create table #([id] int,[usid] int,[usd] int,[rmb] int)
Insert #
select 1,204,200,500 union all
select 2,204,100,null union all
select 3,204,100,null union all
select 4,205,200,500 union all
select 5,205,100,null union all
select 6,205,50,null
Go
Select *,rmb-sum(usd)over() as ye from # where [usid]='205'
(6 個資料列受到影響)
id usid usd rmb ye
----------- ----------- ----------- ----------- -----------
4 205 200 500 150
5 205 100 NULL NULL
6 205 50 NULL NULL(3 個資料列受到影響)
insert into tb select 1,204,200,500
insert into tb select 2,204,100,null
insert into tb select 3,204,100,null
insert into tb select 4,205,200,500
insert into tb select 5,205,100,null
insert into tb select 6,205,50,nullselect a.usid,a.usd,a.rmb,ye=a.rmb -isnull(b.usd,0)
from tb a join (
select usid,sum(usd) as usd from tb group by usid
)b on a.usid=b.usid
where a.usid=205 usid usd rmb ye
205 200 500 150
205 100 NULL NULL
205 50 NULL NULL
select id,usid,usd,rmb,ye=rmb-(select sum(usd) from tb where usid=a.usid) from tb a where usid=205