postgresql - 从 4 个表中获取记录

Question

我有 4 个表:

1. acc_budget_transaction
2. acc_budget_transfer
3. acc_library_budget
4. patron

SQL FIDDLE HERE FOR TABLES

这是所有 4 个的插入脚本:

1.

CREATE TABLE acc_budget_transfer (
    transfer_id integer NOT NULL,
    library_id integer NOT NULL,
    from_budget_id character varying(100),
    to_budget_id character varying(100),
    budget_ta_id integer,
    entry_id character varying(20),
    entry_date timestamp without time zone
);

2.

CREATE TABLE acc_budget_transaction (
    library_id integer NOT NULL,
    budget_ta_id integer NOT NULL,
    ta_date timestamp without time zone,
    ta_amt numeric(19,2),
    ta_type character varying(20),
    budget_id character varying(100),
    committed_or_expenditure character varying(20),
    commit_date timestamp without time zone,
    committed_date timestamp without time zone,
    invoice_no character varying(20),
    payslip_no character varying(20),
    swap_budget_id character varying(10),
    entry_id character varying(20),
    entry_date timestamp without time zone
);

3.

CREATE TABLE acc_library_budget (
    library_id integer NOT NULL,
    budget_id character varying(100) NOT NULL,
    fiscal_year bigint,
    budget_head character varying(100),
    budget_source_id integer,
    budget_allocated_amt numeric(19,2),
    balance_amt numeric(19,2),
    expenditure_amt numeric(19,2),
    committed_amt numeric(19,2),
    carry_forward_status character varying(1),
    status character varying(1),
    entry_id character varying(20),
    entry_date timestamp without time zone
);

4.

CREATE TABLE patron (
    library_id integer NOT NULL,
    patron_id character varying(20) NOT NULL,
    patron_category_id integer,
    isonline character varying(20),
    owns character varying(20),
    created_on timestamp without time zone,
    other_library_patron_id integer,
    library_patron_id integer,
    patron_type character(1),
    dept_id integer,
    fname character varying(40),
    mname character varying(20),
    lname character varying(20),
    address1 character varying(200),
    address2 character varying(100),
    city character varying(50),
    state character varying(50),
    country character varying(200),
    pin character varying(15),
    phone1 character varying(20),
    phone2 character varying(20),
    fax character varying(100),
    email character varying(100),
    paddress1 character varying(200),
    paddress2 character varying(100),
    pcity character varying(50),
    pstate character varying(50),
    pcountry character varying(200),
    ppin character varying(15),
    pphone1 character varying(20),
    pphone2 character varying(20),
    pfax character varying(100),
    pemail character varying(100),
    membership_start_date timestamp without time zone,
    membership_expiry_date timestamp without time zone,
    delinquency_reason character varying(200),
    comm_email character(1),
    comm_instant_msg character(1),
    comm_print character(1),
    entry_date timestamp without time zone,
    user_password character varying(500) DEFAULT md5('abc'::text),
    course_id integer,
    status character varying(1),
    send_to_address character varying(1),
    custom character varying(2000),
    privilege text,
    twitter_id character varying(500),
    facebook_id character varying(500),
    sub_location_id integer,
    login_id character varying(300),
    authenticate_localdatabase character varying(1)
);

这是表格的数据:

acc_budget_transaction

library_id, budget_ta_id, ta_date, ta_amt, ta_type, budget_id, committed_or_expenditure, commit_date, committed_date, invoice_no, payslip_no, swap_budget_id, entry_id, entry_date
1   1   2014-04-11 15:57:10.369 1200.00 C   1_KSource_20132014  \N  \N  \N  \N  \N  \N  1   2014-04-11 15:57:10.369
1   2   2014-04-11 16:02:43.421 4000.00 C   2_KSource_20132014  \N  \N  \N  \N  \N  \N  1   2014-04-11 16:02:43.421
1   3   2014-04-11 16:02:53.28  1111.00 D   1_KSource_20132014  E   \N  \N  \N  \N  \N  1   2014-04-11 16:02:53.28
1   4   2014-04-11 16:02:53.28  1111.00 C   2_KSource_20132014  \N  \N  \N  \N  \N  \N  1   2014-04-11 16:02:53.28
1   8   2014-04-15 15:08:05.659 1000.00 C   1_KSource_20132014  \N  \N  \N  \N  \N  \N  1   2014-04-15 15:08:05.659
1   9   2014-04-16 09:14:07.503 1000.00 C   1_KSource_20132014  \N  \N  \N  \N  \N  \N  1   2014-04-16 09:14:07.503
1   7   2014-04-15 15:08:05.659 1000.00 D   2_KSource_20132014  E   \N  \N  \N  \N  \N  1   2014-04-15 15:08:05.659
1   10  2014-04-16 15:06:24.312 1000.00 C   2_KSource_20132014  \N  \N  \N  \N  \N  \N  44  2014-04-16 15:06:24.312

acc_budget_transfer

transfer_id, library_id, from_budget_id, to_budget_id, budget_ta_id, entry_id, entry_date
1   1   1_KSource_20132014  2_KSource_20132014  3   1   2014-04-11 16:02:53.28
4   1   2_KSource_20132014  1_KSource_20132014  7   1   2014-04-15 15:08:05.659

acc_library_budget

library_id, budget_id, fiscal_year, budget_head, budget_source_id, budget_allocated_amt, balance_amt, expenditure_amt, committed_amt, carry_forward_status, status, entry_id, entry_date
1   1_KSource_20132014  20132014    1   1   2200.00 2089.00 1111.00 0.00    Y   O   1   2014-04-16 09:14:07.503
1   2_KSource_20132014  20132014    2   1   5000.00 2311.00 1000.00 0.00    N   O   44  2014-04-16 15:06:24.312

赞助人

library_id, patron_id, patron_category_id, isonline, owns, created_on, other_library_patron_id, library_patron_id, patron_type, dept_id, fname, mname, lname, address1, address2, city, state, country, pin, phone1, phone2, fax, email, paddress1, paddress2, pcity, pstate, pcountry, ppin, pphone1, pphone2, pfax, pemail, membership_start_date, membership_expiry_date, delinquency_reason, comm_email, comm_instant_msg, comm_print, entry_date, user_password, course_id, status, send_to_address, custom, privilege, twitter_id, facebook_id, sub_location_id, login_id, authenticate_localdatabase
1   1   1   \N  \N  \N  \N  \N  A   1   System Admin            A   A   A   A   A   A   A   A   A   service@new.com                                         2004-04-21 00:00:00 2100-09-14 00:00:00 \N  Y   N   N   2006-09-15 00:00:00 900150983cd24fb0d6963f7d28e17f72    \N  A   A   <Root />    \N  \N  \N  1   \N  A

我想要结果，例如:

我尝试了以下查询但没有成功:

select
    t.ta_amt,
    t.ta_type,
    case
        when l.budget_id = tr.from_budget_id then tr.to_budget_id
        when l.budget_id = tr.to_budget_id then tr.from_budget_id
        when l.budget_id = t.budget_id then t.entry_id
    end as Budget,
    t.ta_date,
    t.committed_or_expenditure,
    p.fname
from
    acc_budget_transaction t 
    left join
    acc_budget_transfer tr on tr.from_budget_id = t.budget_id
    left join
    acc_library_budget l on t.budget_id = l.budget_id
    left join
    patron p on t.entry_id = p.patron_id
where l.budget_head = '2';

当 acc_budget_transfer 中有 budget_id 条目时，则在 budget_id 结果字段中
应该显示相反的 budget_id，并且当 没有转账条目时 budget_id 应该为空，因为该金额是分配金额而不是转账金额。

关于这个麻烦问题的任何帮助。

Answer 1

我尝试使用 1 query 获取结果，但我做不到，所以我进行了 3 query 并对所有查询进行了 union并得出一个结果并完成我的问题。

查询:

select a.TA_AMT,a.TA_TYPE,a.ENTRY_DATE,null as budget,a.COMMITTED_OR_EXPENDITURE,c.FNAME 
 from ACC_BUDGET_TRANSACTION a,Patron c 
 where a.BUDGET_ID='2_KSource_20132014' and a.Library_Id= 1
 and c.Patron_Id=a.ENTRY_ID and c.Library_Id=a.Library_Id 
 and a.BUDGET_TA_ID not in(select BUDGET_TA_ID from ACC_BUDGET_TRANSFER) 
 and a.BUDGET_TA_ID-1 not in(select BUDGET_TA_ID from ACC_BUDGET_TRANSFER)UNION

 select distinct a.TA_AMT,a.TA_TYPE,a.ENTRY_DATE,b.TO_BUDGET_ID as budget,a.COMMITTED_OR_EXPENDITURE,c.FNAME 
 from ACC_BUDGET_TRANSACTION a,ACC_BUDGET_TRANSFER b,Patron c 
 where b.FROM_BUDGET_ID like '2_KSource_20132014' and b.Library_Id=1 and a.TA_TYPE='D' 
 and a.BUDGET_ID=b.FROM_BUDGET_ID and a.Library_Id=b.LIBRARY_ID and b.BUDGET_TA_ID=a.BUDGET_TA_ID 
 and c.Patron_Id=a.ENTRY_ID and c.Library_Id=a.Library_Id UNION

 select distinct a.TA_AMT,a.TA_TYPE,a.ENTRY_DATE,b.FROM_BUDGET_ID as budget,a.COMMITTED_OR_EXPENDITURE,c.FNAME 
 from ACC_BUDGET_TRANSACTION a,ACC_BUDGET_TRANSFER b,Patron c 
 where b.TO_BUDGET_ID like '2_KSource_20132014' and b.Library_Id=1 and a.TA_TYPE='C' 
 and a.BUDGET_ID=b.TO_BUDGET_ID and a.Library_Id=b.LIBRARY_ID and b.BUDGET_TA_ID=a.BUDGET_TA_ID-1 
 and c.Patron_Id=a.ENTRY_ID and c.Library_Id=a.Library_Id;

谢谢大家的帮助。