gpt4 book ai didi

oracle - 在 GROUP BY 查询中将值聚合到表类型中

转载 作者:行者123 更新时间:2023-12-04 06:49:10 24 4
gpt4 key购买 nike

假设您有一个表(在 Oracle 中):

CREATE TABLE CUSTOMER
(
customer_id NUMBER,
gender CHAR(1)
);

假设你有一个表类型:
CREATE TYPE NUMBER_TABLE_TYPE AS TABLE OF NUMBER;

是否可以写一个 GROUP BY查询,对于每个组,该组的主键字段存储在 NUMBER_TABLE_TYPE 中?例如:
DECLARE
CURSOR gender_cursor IS
SELECT
gender,
/* The CUSTOMER_IDS column will be of type NUMBER_TABLE_TYPE: */
SOME_MAGICAL_AGGREGATE_FUNCTION(customer_id) AS customer_ids
FROM
CUSTOMER
GROUP BY
gender;

customer_ids NUMBER_TABLE_TYPE;
BEGIN
FOR gender IN gender_cursor LOOP
customer_ids := gender.customer_ids;
FOR i IN customer_ids.FIRST .. customer_ids.LAST LOOP
dbms_output.put_line(customer_ids(i));
END LOOP;
END LOOP;
END;

我知道这应该通过有两个光标来完成(一个用于性别组,另一个用于查询每个组的客户)。我只是想知道这是否可以做到。 :)

最佳答案

有两种方法可以实现这一点。
首先,通过分析来获取每一行该性别的所有记录的计数。

DECLARE
CURSOR gender_cursor IS
SELECT gender, customer_id, count(*) over (partition by gender) cnt_in_gender,
FROM CUSTOMER
ORDER BY gender;
v_prev_gender varchar2(1) := '?';
BEGIN
FOR gender IN gender_cursor LOOP
IF gender.gender != v_prev gender then
dbms_output.put_line('You will now see '||gender.cnt_in_gender);
v_prev_gender := gender.gender);
END IF;
dbms_output.put_line(gender.customer_ids);
END LOOP;
END;

其次,更接近您的要求
DECLARE
CURSOR gender_cursor IS
SELECT
gender,
/* The CUSTOMER_IDS column will be of type NUMBER_TABLE_TYPE: */
CAST(COLLECT(customer_id) AS NUMBER_TABLE_TYPE) AS customer_ids
FROM
CUSTOMER
GROUP BY
gender;

customer_ids NUMBER_TABLE_TYPE;
BEGIN
FOR gender IN gender_cursor LOOP
customer_ids := gender.customer_ids;
FOR i IN customer_ids.FIRST .. customer_ids.LAST LOOP
dbms_output.put_line(customer_ids(i));
END LOOP;
END LOOP;
END;

关于oracle - 在 GROUP BY 查询中将值聚合到表类型中,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/3367579/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com