查询从 3 个表中检索数据 [英] Query for retrieving data from 3 tables

问题描述

我是 SQL 新手，我对查询有疑问.

I am a newbie in SQL and I have a doubt about a query.

我有三张桌子:

1. 具有 cons_id_no、key_id 字段的消费者
2. bm_bill 具有字段 key_id、bill_id_no、amt_payable、bill_date(它将包含消费者的所有账单金额和日期)
3. mreceipt 具有字段 key_id、receipt_no、amt_paid、fine、pay_date(它将包含消费者的所有付款详情)

consumer 表与 bm_bill 和 mreceipt 有一对多的关系.我想根据他的 cons_id_no 创建消费者的分类帐信息.这应该包含他的 cons_id_no、key_id、bill_id_no(最新)、bill_date(最新)、amt_payable(最新)、receipt_no(最新)、amt_paid(最新)、fine(最新)、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

SELECT 
   c.key_id,
   c.cons_id_no consumerid, 
   b.bill_id_no billno,
   TO_CHAR(b.bill_date,'dd-Mon-YYYY') billdate,
   b.amt_payable,
   m.receipt_no receiptno, 
   TO_CHAR(m.pay_date,'dd-Mon-YYYY') paydate,
   m.amt_paid+m.fine amountpaid 
FROM 
   consumer c 

   LEFT OUTER JOIN (SELECT key_id, MAX(bill_date) AS maxDate FROM bm_bill GROUP BY key_id) maxBillDate 
   ON (maxBillDate.key_id = c.key_id)

   LEFT OUTER JOIN bm_bill b 
   ON (b.key_id = c.key_id AND b.bill_date = maxBillDate.maxDate) 

   LEFT OUTER JOIN (SELECT key_id, MAX(pay_date) AS maxPayDate FROM mreceipt GROUP BY key_id) maxMReceipt 
   ON (maxMReceipt.key_id = c.key_id)

   LEFT OUTER JOIN mreceipt m 
   ON (m.key_id = c.key_id AND m.pay_date = maxMReceipt.maxPayDate)

WHERE 
   c.cons_id_no='?';

我执行了查询，它给了我想要的结果.然后我注意到查询太慢并发现在我的解决方案中我有:

I executed the query and it gave me the desired result. Then I noted that the query is too slow and found out that in my solution I have:

SELECT key_id, max(bill_date) AS maxDate FROM bm_bill GROUP BY key_id

从 bm_bill 中检索所有 key_ids 和 bill_dates，我只需要特定 key_id 的信息.最重要的是，我的解决方案中还有一个这样的查询.

which is retrieving all of the key_ids and bill_dates from the bm_bill where I needed only information of a specific key_id. Above all I have one more query like this in my solution.

因此我的问题是:有没有更好的方法来做到这一点?

Hence my question is: Is there any better way to do this ?

推荐答案

您要加入 2 个表(bm_bill 和 mreceipt)中的每一个.我要尝试的第一件事是更改您的查询以避免双重连接，看看它是否有所不同，例如:

You are joining each of 2 tables (bm_bill and mreceipt) twice. First thing I'd try is changing your query to avoid double joins and see if it makes difference, something like :

SELECT 
   c.key_id,
   c.cons_id_no consumerid, 
   b.bill_id_no billno,
   TO_CHAR(b.bill_date,'dd-Mon-YYYY') billdate,
   b.amt_payable,
   m.receipt_no receiptno, 
   TO_CHAR(m.pay_date,'dd-Mon-YYYY') paydate,
   m.amt_paid+m.fine amountpaid 
FROM 
   consumer c 

   LEFT JOIN (SELECT key_id,
   bill_id_no, bill_date,amt_payable,receipt_no receiptno , 
   ROW_NUMBER() OVER (PARTITION BY key_id ORDER BY bill_date DESC) as rn
   FROM bm_bill)b ON (b.key_id = c.key_id and b.rn =1)


   LEFT JOIN (SELECT key_id,
   pay_date , amt_paid, amt_paid, fine, 
   ROW_NUMBER() OVER (PARTITION BY key_id ORDER BY pay_date DESC) as rn
   FROM mreceipt) m ON (m.key_id = c.key_id and m.rn =1)


WHERE 
   c.cons_id_no='?';

如果这不起作用，您可以使用 Oracle 的替代方案"来解决 SQLServer OUTER APPLY - 您创建了 2 个返回 MAX(bill_date) 的函数和 MAX(pay_date) ，并加入它们.

If that doesn't work well, you may work it around with Oracle "alternative" to SQLServer OUTER APPLY - you create 2 functions that return MAX(bill_date) and MAX(pay_date) respectively , and join them.

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