表结构如下
id pid N1 N2
1 1 10 20
1 2 10 10
1 3 10 10
1 ... ... ...
2 1 5 5
2 2 5 5
2 3 5 5
2 ... ... ...
现在想得到如下查询结果,查询条件是查询pid从1到3
id N1 N2
1 30 40
2 15 15
这个语句该怎么写
id pid N1 N2
1 1 10 20
1 2 10 10
1 3 10 10
1 ... ... ...
2 1 5 5
2 2 5 5
2 3 5 5
2 ... ... ...
现在想得到如下查询结果,查询条件是查询pid从1到3
id N1 N2
1 30 40
2 15 15
这个语句该怎么写
from 表
where pid between 1 and 3
group by id
SELECT id,sum(N1),SUM(N2) FROM tablename where pid in ('1','2','3') group by id
go
create table 表(id int,pid int,N1 int,N2 int)
insert into 表
select 1,1,10,20
union all select 1,2,10,10
union all select 1,3,10,10
union all select 2,1,5,5
union all select 2,2,5,5
union all select 2,3,5,5select id,sum(N1) as N1,sum(N2) as N2
from 表
where pid between 1 and 3
group by id
/*
id N1 N2
----------- ----------- -----------
1 30 40
2 15 15(所影响的行数为 2 行)
*/
insert into t_exam(id,pid,n1,n2)values(1,1,10,20)
insert into t_exam(id,pid,n1,n2)values(1,2,10,10)
insert into t_exam(id,pid,n1,n2)values(1,3,10,10)
insert into t_exam(id,pid,n1,n2)values(2,1,20,10)
insert into t_exam(id,pid,n1,n2)values(2,2,30,20)
insert into t_exam(id,pid,n1,n2)values(2,3,20,20)
select id,sum(n1) as n1 ,sum(n2) as n2 from t_exam where pid<=3 group by id
drop table t_exam