在线等 sql语句

[雾灵各吉]

数据库 表 table
id job_id name num1 num2
1 123 a 4 5
2 123 a 4 10
3 456 b 3 23
4 456 b 5 12

要求得到：
job_id一样的 num1 不相加,其他相加
其他相加。
想要得到如：
job_id name num1
123 a 4
456 b 8

8=5+3

[水族杰纶]

 
if not object_id('tb') is null
	drop table tb
Go
Create table tb([id] int,[job_id] int,[name] nvarchar(1),[num1] int,[num2] int)
Insert tb
select 1,123,N'a',4,5 union all
select 2,123,N'a',4,10 union all
select 3,456,N'b',3,23 union all
select 4,456,N'b',5,12
Go
select [job_id],
       [Name],
       sum(num1)num1
from(
select [job_id],
       [Name],
       [Num1]
from tb
group by  [job_id],
       [Name],
       [Num1]
)t
group by [job_id],
       [Name]
/*
job_id      Name num1
----------- ---- -----------
123         a    4
456         b    8

*/

[fpzgm]

select job_id,name,sum(num1)
from
(select distinct job_id,name,num1 from table)t
group by job_id,name

[DataBox-MDX]

if not object_id(N'A',N'U') is null  	drop table A  
Go 
Create table A  
(
id int,
job_id int,
name nvarchar(1),
num1 int,
num2 int
) 
Insert into A
select 1,123,N'a',4,5 union all
select 2,123,N'a',4,10 union all  
select 3,456,N'b',3,23 union all  
select 4,456,N'b',5,12  
Go
select distinct Two.job_id,A.name,Two.num1 from
(
select job_id,SUM(num1) as num1 from 
(
  select job_id,num1 from A group by job_id,num1
) as One 
group by job_id
)
as Two inner join A on A.job_id=Two.job_id
drop table A

(4 行受影响)
job_id name num1
----------- ---- -----------
123 a 4
456 b 8

(2 行受影响)

[jaydom]

if object_id('tb') is not null
drop table tb
go
create table tb(id int,job_id int,name varchar(2),num1 int, num2 int)
insert into tb
select 1, 123, 'a', 4, 5 union all
select 2, 123, 'a', 4, 10 union all
select 3, 456, 'b', 3, 23 union all
select 4, 456, 'b', 5, 12 

select job_id,name,sum(num1) as num1
from
 (
	select distinct job_id,name,num1
	from tb
) t
group by job_id,name

123	a	4
456	b	8

[chen8410]

declare @tb table(id int,job_id int,name varchar(10),num1 int,num2 int)
insert into @tb
select 1 ,123 ,'a', 4, 5
union all select 2 ,123 ,'a', 4, 10
union all select 3 ,456 ,'b', 3, 23
union all select 4 ,456 ,'b', 5, 12

select job_id,name,SUM(num1) num1
from(
select job_id,name,num1
from @tb
group by job_id,name,num1) t
group by job_id,name

--结果：
job_id	name	num1
123	a	4
456	b	8

[雾灵各吉]

[Quote=引用 2 楼 fpzgm 的回复:]
SQL code
select job_id,name,sum(num1)
from
(select distinct job_id,name,num1 from table)t
group by job_id,name
[/Quote]

如果还有其他列 就不能用了。

[雾灵各吉]

我表达的意思有问题。
应该运行结果为：
job_id name num1 num2
123 a 4 15
456 b 8 35

[abuying]

select  job_id,name,
       num1=case when min(num1)=max(num1) then min(num1) else min(num1)+max(num1) end
    from tb  group by job,name

[dawugui]

[Quote=引用 7 楼 fangkuai3 的回复:]
我表达的意思有问题。
应该运行结果为：
job_id name num1 num2
123 a 4 15
456 b 8 35
[/Quote]
select job_id ,name ,sum(num1) num1 , sum(num2) num2 from tb group by job_id ,name

[dawugui]

[Quote=引用 7 楼 fangkuai3 的回复:]
我表达的意思有问题。
应该运行结果为：
job_id name num1 num2
123 a 4 15
456 b 8 35
[/Quote]
原来楼主是这个意思？

create table tb(id int,job_id int,name varchar(10),num1 int,num2 int)
insert into tb
select 1 ,123 ,'a', 4, 5
union all select 2 ,123 ,'a', 4, 10
union all select 3 ,456 ,'b', 3, 23
union all select 4 ,456 ,'b', 5, 12
go

select m.* , n.num2 from
(select job_id ,name ,sum(num1) num1 from (select distinct job_id ,name , num1 from tb) t group by job_id ,name) m,
(select job_id ,name ,sum(num2) num2 from tb t group by job_id ,name) n
where m.job_id = n.job_id and m.name = n.name

drop table tb

/*
job_id      name       num1        num2        
----------- ---------- ----------- ----------- 
123         a          4           15
456         b          8           35

（所影响的行数为 2 行）
*/

[diyppfei]

select job_id,name,sum(distinct num1) as num1,sum(num2)num2
from tb group by job_id,name