table A 數據如下:
no_Tran,op_date,name,qty,id_lot,job_id,id_mo_rt
no_001 20061201 a 100 011 002 001
no_001 20061201 a 200 012 003 002
no_001 20061201 a 300 013 004 003
no_002 20061202 b 200 012 003 002
no_002 20061202 b 100 011 002 001
no_002 20061202 b 200 012 003 002
no_002 20061202 b 300 013 004 003
用游標實現:其它的方法也可以!使得主從明細呈現出來!
主表 B
no_Tran,op_date
no_001 20061201 a
明細表 C
qty,id_lot,job_id,id_mo_rt
100 011 002 001
200 012 003 002
300 013 004 003
no_Tran,op_date,name,qty,id_lot,job_id,id_mo_rt
no_001 20061201 a 100 011 002 001
no_001 20061201 a 200 012 003 002
no_001 20061201 a 300 013 004 003
no_002 20061202 b 200 012 003 002
no_002 20061202 b 100 011 002 001
no_002 20061202 b 200 012 003 002
no_002 20061202 b 300 013 004 003
用游標實現:其它的方法也可以!使得主從明細呈現出來!
主表 B
no_Tran,op_date
no_001 20061201 a
明細表 C
qty,id_lot,job_id,id_mo_rt
100 011 002 001
200 012 003 002
300 013 004 003
table B:
B_id,no_Tran,op_date name
01 no_001 20061201 a
02 no_002 20061201 b
table C:
C_id,B_id,qty,id_lot,job_id,id_mo_rt
001 001 100 011 002 001
002 001 200 012 003 002
003 001 300 013 004 003
004 002 200 012 003 002
005 002 100 011 002 001
006 002 200 012 003 002
007 002 300 013 004 003
insert B(no_Tran, op_date, name)
select no_Tran, op_date, name from A group by no_Tran, op_date, name
--插入子表
insert C(qty, id_lot, job_id, id_mo_rt)
select qty, id_lot, job_id, id_mo_rt from A
SELECT
b.no_Tran,b.op_date,b.name,
c.qty,c.id_lot,c.job_id,c.id_mo_rt
FROM tableB as b INNER JOIN tableC as c ON b.B_id = c.B_id
select distinct no_Tran,op_date,name
from Ainsert into C
select no_Tran,qty,id_lot,job_id,id_mo_rt
from A
id_lot varchar(10),job_id varchar(10),id_mo_rt varchar(10))
insert A select 'no_001', '2006-12-01', 'a', 100, '011', '002' , '001'
insert A select 'no_001', '2006-12-01', 'a', 200, '012', '003', '002'
insert A select 'no_001', '2006-12-01', 'a', 300, '013', '004', '003'
insert A select 'no_002', '2006-12-02', 'b', 200, '012', '003', '002'
insert A select 'no_002', '2006-12-02', 'b', 100, '011', '002', '001'
insert A select 'no_002', '2006-12-02', 'b', 200, '012', '003', '002'
insert A select 'no_002', '2006-12-02', 'b', 300, '013', '004', '003'create table B(B_id int identity, no_Tran varchar(10), op_date datetime, name varchar(10))
create table C(C_id int identity, B_id int,qty int,id_lot varchar(10),job_id varchar(10),id_mo_rt varchar(10))
--插入主表
insert B(no_Tran, op_date, name)
select no_Tran, op_date, name from A group by no_Tran, op_date, name
--插入子表
insert C(B_id, qty, id_lot, job_id, id_mo_rt)
select B_id=(select B_id from B where no_Tran=A.no_Tran and op_date=A.op_date and name=A.name),
qty, id_lot, job_id, id_mo_rt from A
--result
B_id no_Tran op_date name
----------- ---------- ------------------------------------------------------ ----------
1 no_001 2006-12-01 00:00:00.000 a
2 no_002 2006-12-02 00:00:00.000 b(2 row(s) affected)select * from C
--result
C_id B_id qty id_lot job_id id_mo_rt
----------- ----------- ----------- ---------- ---------- ----------
1 1 100 011 002 001
2 1 200 012 003 002
3 1 300 013 004 003
4 2 200 012 003 002
5 2 100 011 002 001
6 2 200 012 003 002
7 2 300 013 004 003(7 row(s) affected)