有如下一个表A(主键为ID+SUB_ID):
ID SUB_ID SUBJECT DEPART MONEY_A MONEY_B
101 1 1001 1 500.00 0
101 2 5002 0 500.00
102 1 1002 2 300.00 0
102 2 2002 0 300.00
103 1 1001 2 600.00 0
103 2 1001 1 300.00 0
103 3 5002 900.00
现要求对表A中以ID相同的为一组,原则是如果不同ID中的SUBJECT相同,则进行合并(比如ID为101和103可以进行合并,类似会计分录的合并,即如果二笔会计分录的借方和贷方科目都相同,而且金额方向也一致,就将这两笔分录合并成一笔分录),合并后生成一个新表:ID SUB_ID SUBJECT DEPART MONEY_A MONEY_B
1101 1 1001 1 800.00 0
1101 2 1001 2 600.00 0
1101 3 5002 0 1400.00
1102 1 1002 2 300.00 0
1102 2 2002 0 300.00
ID SUB_ID SUBJECT DEPART MONEY_A MONEY_B
101 1 1001 1 500.00 0
101 2 5002 0 500.00
102 1 1002 2 300.00 0
102 2 2002 0 300.00
103 1 1001 2 600.00 0
103 2 1001 1 300.00 0
103 3 5002 900.00
现要求对表A中以ID相同的为一组,原则是如果不同ID中的SUBJECT相同,则进行合并(比如ID为101和103可以进行合并,类似会计分录的合并,即如果二笔会计分录的借方和贷方科目都相同,而且金额方向也一致,就将这两笔分录合并成一笔分录),合并后生成一个新表:ID SUB_ID SUBJECT DEPART MONEY_A MONEY_B
1101 1 1001 1 800.00 0
1101 2 1001 2 600.00 0
1101 3 5002 0 1400.00
1102 1 1002 2 300.00 0
1102 2 2002 0 300.00
use tempdb;
/*
create table t1
(
ID int not null,
SUB_ID int not null,
[SUBJECT] int not null,
DEPART int not null,
MONEY_A money not null,
MONEY_B money not null
);
insert into t1(ID,SUB_ID,[SUBJECT],DEPART,MONEY_A,MONEY_B)
values
(101,1,1001,1,500.00,0),
(101,2,5002,0,500.00,0),
(102,1,1002,2,300.00,0),
(102,2,2002,0,300.00,0),
(103,1,1001,2,600.00,0),
(103,2,1001,1,300.00,0),
(103,3,5002,0,900.00,0);
*/
select identity(int,1,1) as ID,
MAX(t1.SUB_ID) as [SUB_ID],
t1.[SUBJECT],
t1.[DEPART],
SUM(t1.MONEY_A) as [MONEY_A],
SUM(t1.MONEY_B) as [MONEY_B]
into t2
from t1
group by t1.[SUBJECT],t1.[DEPART]
order by ID;select *
from t2;
from table
group by 借贷,账目