Oracle |从具有一段关系到许多关系的表中检索记录

发布于 2025-01-22 18:29:37 字数 2348 浏览 0 评论 0原文

我有两个与一对多关系的桌子。 my_fact是父表，而my_rmdetails是子表有多个单亲记录的记录。

表my_fact：

fact_id	fact_id fact_date	total_demand
1000	21/04/2022	500
2000 2000 2000	21/04/2022	500

表MY_RMDETAILS：

rm_id	200	RM1	repless_qty	rm_item_item_item_name
500 500	1000	500	500 500 500	promess_qty
promist_id promist_qty promist_qty	500 500 500 500 500 500	500	500	RM2
202	1000	500	500	RM3
203	1000	400	500	RM4
300	2000	2000	500	RM1
301	2000	2000 500	500 500	RM2
302	2000	500	500	RM3
303	2000	500 500	500	RM4

我需要写一个查询才能以下输出。

逻辑：

如果my_rmdetails.promise_qty小于my_rmdetails.request_qty，则供应不足。

因此，对于任何给定的my_fact记录，如果其儿童中的任何一个来自my_rmdetails具有Promise_qty小于request> request_qty，输出中的flag supply_suffe_suffe_suffe_suffe_suffe_suffe_suffe_suffe_suffe_suffe_suff否则应为y。

insuffifice_rms输出中的列应显示my_rmdetails.rm_item_name“不足”记录的记录为逗号分隔格式。

预期输出：

fact_id fact_date	total_demand	supply_suffime_suffime_suffime_suffime_suffe_suffe_suffe_date	？	Insuffife_RMS
1000	21/04/2022	500	N	RM2，RM4
2000	21/04/2022	500	Y

请帮助。提前致谢。

原文

I have two tables which share one-to-many relationship. MY_FACT is the parent table whereas MY_RMDETAILS is the child table having multiple records for a single parent record.

Table MY_FACT:

FACT_ID	FACT_DATE	TOTAL_DEMAND
1000	21/04/2022	500
2000	21/04/2022	500

Table MY_RMDETAILS:

RM_ID	FACT_ID	PROMISE_QTY	REQUEST_QTY	RM_ITEM_NAME
200	1000	500	500	RM1
201	1000	400	500	RM2
202	1000	500	500	RM3
203	1000	400	500	RM4
300	2000	500	500	RM1
301	2000	500	500	RM2
302	2000	500	500	RM3
303	2000	500	500	RM4

I need to write a query to have below output.

Logic:

If MY_RMDETAILS.PROMISE_QTY is less than MY_RMDETAILS.REQUEST_QTY, the supply is insufficient.

So for any given MY_FACT record, if any one of its children records from MY_RMDETAILS has PROMISE_QTY less than REQUEST_QTY, the flag SUPPLY_SUFFICIENT in output should be N else it should be Y.

And INSUFFICIENT_RMs column in output should show the MY_RMDETAILS.RM_ITEM_NAME of "insufficient" records as comma separated format.

EXPECTED OUTPUT:

FACT_ID	FACT_DATE	TOTAL_DEMAND	SUPPLY_SUFFICIENT?	INSUFFICIENT_RMs
1000	21/04/2022	500	N	RM2,RM4
2000	21/04/2022	500	Y

Please help. Thanks in advance.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

青衫负雪 2025-01-29 18:29:37

您可以尝试使用带有条件聚合功能的子查询。

SELECT t2.*,
       CASE WHEN t1.INSUFFICIENT_cnt > 0 THEN 'N' ELSE 'Y' END,
       t1.INSUFFICIENT_RMs
FROM (
    SELECT FACT_ID,
        LISTAGG(CASE WHEN PROMISE_QTY < REQUEST_QTY THEN RM_ITEM_NAME END, ', ')  WITHIN GROUP (ORDER BY RM_ID)  INSUFFICIENT_RMs,
        COUNT(CASE WHEN PROMISE_QTY < REQUEST_QTY   THEN RM_ITEM_NAME END) INSUFFICIENT_cnt
    FROM MY_RMDETAILS
    GROUP BY FACT_ID
) t1 INNER JOIN MY_FACT t2
ON t1.FACT_ID = t2.FACT_ID

You can try to use subquery with condition aggregate function.

SELECT t2.*,
       CASE WHEN t1.INSUFFICIENT_cnt > 0 THEN 'N' ELSE 'Y' END,
       t1.INSUFFICIENT_RMs
FROM (
    SELECT FACT_ID,
        LISTAGG(CASE WHEN PROMISE_QTY < REQUEST_QTY THEN RM_ITEM_NAME END, ', ')  WITHIN GROUP (ORDER BY RM_ID)  INSUFFICIENT_RMs,
        COUNT(CASE WHEN PROMISE_QTY < REQUEST_QTY   THEN RM_ITEM_NAME END) INSUFFICIENT_cnt
    FROM MY_RMDETAILS
    GROUP BY FACT_ID
) t1 INNER JOIN MY_FACT t2
ON t1.FACT_ID = t2.FACT_ID

回复收藏 0 原文

~没有更多了~