从3个表格中检索值 [英] Retrieving values from 3 tables

问题描述

我有三个表:

1. 具有字段cons_id_no，key_id的消费者
2. bm_bill，其中包含字段key_id，bill_id_no，amt_payable，bill_date (它将包含所有账单金额和消费者的日期)
3. 收据，其中包含以下字段:key_id，receive_no，amt_paid，fine， pay_date(它将包含消费者的所有付款明细)

1. Consumer which has fields cons_id_no, key_id
2. bm_bill which has fields key_id, bill_id_no, amt_payable, bill_date (It will contain all of bill amounts and date of a consumer)
3. mreceipt which has fields key_id, receipt_no, amt_paid, fine, pay_date (It will contain all of the payment details of a consumer)

使用者表与bm_bill和mreceipt有一对多关系.我想基于消费者的cons_id_no创建分类帐信息.它应该包含他的cons_id_no，key_id，bill_id_no(最新)，bill_date(最新)，amt_payable(最新)，receipt_no(最新)，amt_paid(最新)，罚款(最新)，pay_date(最新)，为此我创建了以下内容查询

The consumer table has one to many relationship with bm_bill and mreceipt. I want to create ledger information of a consumer based on his cons_id_no. It should contain his cons_id_no, key_id, bill_id_no (latest), bill_date (latest), amt_payable (latest),receipt_no (latest), amt_paid (latest), fine (latest), pay_date (latest) and for that I have created the below query

我在 此处提问> ，我发现了一个解决方案，但仍然不够，因为它只能从帐单和付款表中检索整个数据.根据我从那里获得的提示，我已经找到了另一个解决方案，如下所示:

I have asked the question here and I found out one solution to that, but still it was not enough as it was retrieving whole data from tableS of bills and payments. As per the tip I got from there, I have reached another solution, which is as follows:

首先，我创建了一个类型:

First of all I created a type:

CREATE OR REPLACE TYPE key_id_row AS OBJECT
    (
        key_id number(10)
    );

然后创建一个表类型:

CREATE OR REPLACE TYPE key_id_tab AS TABLE OF key_id_row;

然后创建一个函数:

CREATE OR REPLACE FUNCTION get_key_id(cons_id VARCHAR2) RETURN key_id_tab IS
    result_tab key_id_tab;    
BEGIN
    SELECT
        key_id_row(key_id)
    BULK COLLECT INTO 
        result_tab
    FROM
        consumer
    WHERE
        cons_id_no = cons_id;
    RETURN result_tab;    
END get_key_id;

之后，我在查询中使用它的方式如下:

After that I used it in the query as follows:

SELECT
    c.key_id,
    c.cons_id_no consumerid,
    b.bill_id_no,
    b.key_id,
    b.bill_date,
    m.key_id,
    m.receipt_no,
    m.pay_date    
FROM
    consumer c
LEFT OUTER JOIN
    (
        SELECT
            bb.bill_id_no,
            gk.key_id,
            bb.bill_date,
            ROW_NUMBER() OVER (PARTITION BY bb.key_id ORDER BY bb.bill_date DESC) AS rowNumber
        FROM
            bm_bill bb
        JOIN
            TABLE(get_key_id('2114109999')) gk
        ON
            (gk.key_id = bb.key_id)    
    ) b
ON
    (b.rowNumber = 1)
LEFT OUTER JOIN 
    (
        SELECT
            gk.key_id,
            mr.receipt_no,
            mr.pay_date,
            ROW_NUMBER() OVER (PARTITION BY mr.key_id ORDER BY mr.pay_date DESC) rowNumber
        FROM
            mreceipt mr
        JOIN
            TABLE(get_key_id('2114109999')) gk
        ON
            (gk.key_id = mr.key_id)
    ) m
ON
    (m.rowNumber = 1)        
WHERE
    c.cons_id_no='2114109999'; 

解决方案是否足够好，还是我必须通过以下方式解决问题:

Is the solution good enough or Do I have to approach the problem in the following way:

从使用者表中选择key_id作为单独的查询，如下所示:

Select the key_id from the consumer table as a separate query as in:

SELECT key_id FROM consumer WHERE cons_id_no = '2114109999';

并将其存储在变量中，并在我上面提到的查询中使用该值.

and store it in a variable and use that value inside the query I mentioned above.

推荐答案

您当前的解决方案是创建与每个使用者ID关联的key_id表.如果您只需要最新版本，那么其他线程上的答案是正确的.由于您要汇总帐单和付款表的最新值，因此最好的方法是在有序表上进行子查询联接.

Your solution currently is creating table of the key_ids associated with each consumer id. If you only need the latest then the answer on your other thread is correct. Since you are aggregating the latest values from your bill and payment tables a sub-query join on an ordered table is the best way to go.

我不确定从帐单和付款表中检索全部数据是什么意思.如果要进行全表扫描，则应通过适当的索引编制和/或分区来缓解这种情况.

I am not sure what you mean by retrieving whole data from the bills and payments tables. If you mean full table scans then this should be relieved by appropriate indexing and/or partitioning.

您是否打算将分类帐作为消费者所有账单和付款的完整清单?您的问题给人的印象是您只在乎最新消息.

Do you intend your ledger to be a full list of all bills and payments by the consumer? Your question gives the impression you only care about the latest.

这篇关于从3个表格中检索值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！