From: http://community.9cbs.net/expert/topic/3381/3381415.xml? Temp = .4722254
- Test: - Solution Lord, from Table - Master Table P-Master (Pcode Varchar (5), PName Varchar (5), PStatus Varchar (1)) Insert Into Pmasterselect 'P001', 'Pa', '1' union allselect 'p002', 'pb', '0' - from Table P-Detailcreate Table Pdetail (Pcode Varchar (5), Pid Varchar (5), Mcode Varchar (5), MTYPE VARCHAR (1), [Money] int) Insert Into Pdetailselect 'p001', '01', 'M1', '2', 560 Union Allselect 'P001', '02', 'M2', '3', 480 Union AllSelect 'P001', '03', 'm3', '2', 360 Union AllSelect 'P001', '04', 'M4', '3', 670
- Borrowing the branch, from the table - the main table LMASTERCREATE TABLE LMASTER (Lcode Varchar (5), Pcode Varchar (4), LTPYE VARCHAR (1)) INSERT INTO LMASTERSELECT 'L001', 'P001', '2' Union All SELECT 'L002', 'P001', '3' Union All SELECT 'L003', 'P001', '2' Union All Select 'L004', 'P001', '3' - From Table Ldetail Create Table Ldetail (Lcode Varchar (5), Lid Varchar (5), LMONEY INT) INSERT INTO LDETAILSELECT 'L001', '01', 51 Union AllSelect 'L001', '02', 41 Union AllSelect 'L002', '03', 31 Union AllSelect ' L002 ',' 04 ', 61 - Reimbursement Lord ',' 2 'Union All SELECT' A002 ',' P001 ',' 3 'Union All Select' A003 ',' P001 ',' 2 'Union AllSelect' A004 ',' P001 ',' 3 '- from Table Adetail Create Table Adetail (Acode Varchar (5), AID VARCHAR (5), AMONEY INT) INSERT INTO ADETAILSELECT 'A001', '01', 52 Union All Select 'A001', '02', 42 Union AllSelect 'A002', '03', 32 Union AllSelect 'A002', '04', 62 - Query Declare @a varchar (5) set @ a = 'p001' Select Distinct a.pcode, b.mtype, [Money] = SUM (B. [Money]) INTO #ptfrom pmaster a inner join pdetail b on a.pcode = b.pcode where a.pstatus = '1'group by b.mtype, a.pcodeselect distinct a.ltpye, lmoney = sum (b.lmoney) INTO #LTFROM LMASTER A inner join ldetail b on a.lcode = b.lcodegroup by a.ltpye
Select a.atpye, amoney = sum (b.amoney) INTO #ATFROM AMASTER A INNER JOIN ADETAIL B on a.acode = B.AcodeGroup by a.atpyselect a.pcode, a.mtype, amount = A.Money-b. LMONEY C.AMONEYINTO #TFROM #pt a inner join #lt b on a.mtype = b.ltpye inner join #at c on a.mtype = c.Atpye select pcode = max (pcode), the total amount of cash The amount = sum (Case MTYPE WHEN 2 THEN ELSE 0 End)
Drop Table Pmaster, Pdetail, Lmaster, Ldetail, Amaster, Adetail, # Pt, # lt, # at, # T
--operation result:
The total amount of the total amount of Pcode can borrow the total amount of the product ------------------------- P001 922 1152
