34,576
社区成员
发帖
与我相关
我的任务
分享
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 行)
*/
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
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
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
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
select job_id,name,sum(num1)
from
(select distinct job_id,name,num1 from table)t
group by job_id,name
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
*/