select name,age,num=(select sum(num) from tb where name=t.name) from tb t
DECLARE @T TABLE(name VARCHAR(10), age INT, num INT) INSERT @T SELECT 'zhao' , 22 , 3 INSERT @T SELECT 'zhao' , 22, 1 INSERT @T SELECT 'qian' , 23 , 2 INSERT @T SELECT 'qian', 23 , 2 SELECT DISTINCT NAME,AGE,(SELECT SUM(NUM) FROM @T WHERE NAME=T.NAME AND AGE=T.AGE) AS NUM FROM @T T /*NAME AGE NUM ---------- ----------- ----------- qian 23 4 zhao 22 4(影響 2 個資料列)*/
--sql2005 if object_id('[tb]') is not null drop table [tb] go create table [tb]([name] varchar(4),[age] int,[num] int) insert [tb] select 'zhao',22,3 union all select 'zhao',22,1 union all select 'qian',23,2 union all select 'qian',23,2 go --select * from [tb]select distinct name,age,num=sum(num) over(partition by name,age) from tb /* name age num ---- ----------- ----------- qian 23 4 zhao 22 4(2 行受影响) */
select name,age,num=(select sum(num) from tb where name=t.name) from tb t 为什么不用group by??
select name,age,num from table where num=4
既然楼主不用GROUP BY , select name,age, (select sum(num) from tb where name=t.name) as num from tb t
from tb t
INSERT @T SELECT 'zhao' , 22 , 3
INSERT @T SELECT 'zhao' , 22, 1
INSERT @T SELECT 'qian' , 23 , 2
INSERT @T SELECT 'qian', 23 , 2
SELECT DISTINCT NAME,AGE,(SELECT SUM(NUM) FROM @T WHERE NAME=T.NAME AND AGE=T.AGE) AS NUM FROM @T T
/*NAME AGE NUM
---------- ----------- -----------
qian 23 4
zhao 22 4(影響 2 個資料列)*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([name] varchar(4),[age] int,[num] int)
insert [tb]
select 'zhao',22,3 union all
select 'zhao',22,1 union all
select 'qian',23,2 union all
select 'qian',23,2
go
--select * from [tb]select distinct name,age,num=sum(num) over(partition by name,age)
from tb
/*
name age num
---- ----------- -----------
qian 23 4
zhao 22 4(2 行受影响)
*/
为什么不用group by??
select name,age, (select sum(num) from tb where name=t.name) as num
from tb t