作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
假设您有一个表(在 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/
我是一名优秀的程序员,十分优秀!