我如何在他們的年份中每年在“自 YYYY 以來獲得”下添加員工 我找到了一個星期的方法，但仍然不知道它是如何作業的 這是我的宣告
DECLARE
CURSOR c_year IS SELECT EXTRACT (YEAR FROM hire_date) YEAR, COUNT(*) PERSON
FROM employees
GROUP BY EXTRACT (YEAR FROM hire_date)
ORDER BY 1 ASC;
CURSOR c_emp (thisyear NUMBER) IS SELECT employee_id ID, first_name, last_name , hire_date
FROM employees
WHERE EXTRACT(YEAR FROM hire_date) = thisyear
ORDER BY 1 ASC;
BEGIN
FOR e_year IN c_year LOOP
DBMS_OUTPUT.PUT_LINE('Acquire since '|| e_year.YEAR || ' ('||e_year.PERSON || ' persons) ==========');
FOR e_emp IN c_emp(8) LOOP
DBMS_OUTPUT.PUT_LINE('ID: '||e_emp.ID||' '||e_emp.first_name||' '||e_emp.last_name);
END LOOP;
END LOOP;
END;
這是結果
Acquire since 2001 (1 persons) ==========
Acquire since 2002 (7 persons) ==========
Acquire since 2003 (6 persons) ==========
Acquire since 2004 (10 persons) ==========
Acquire since 2005 (29 persons) ==========
Acquire since 2006 (24 persons) ==========
Acquire since 2007 (19 persons) ==========
Acquire since 2008 (11 persons) ==========
但是我想按他們的年份在每行下的員工詳細資訊我該怎么辦
uj5u.com熱心網友回復：
傳入e_Year.YEAR引數化游標而不是靜態值8：
DECLARE
CURSOR c_year IS
SELECT EXTRACT (YEAR FROM hire_date) YEAR, COUNT(*) PERSON
FROM employees
GROUP BY EXTRACT (YEAR FROM hire_date)
ORDER BY 1 ASC;
CURSOR c_emp (thisyear NUMBER) IS
SELECT employee_id ID, first_name, last_name , hire_date
FROM employees
WHERE EXTRACT(YEAR FROM hire_date) = thisyear
ORDER BY 1 ASC;
BEGIN
FOR e_year IN c_year LOOP
DBMS_OUTPUT.PUT_LINE('Acquire since '|| e_year.YEAR || ' ('||e_year.PERSON || ' persons) ==========');
FOR e_emp IN c_emp(e_year.year) LOOP
DBMS_OUTPUT.PUT_LINE('ID: '||e_emp.ID||' '||e_emp.first_name||' '||e_emp.last_name);
END LOOP;
END LOOP;
END;
/
其中，對于樣本資料：
CREATE TABLE employees ( employee_id, first_name, last_name, hire_date ) AS
SELECT 1, 'Alice', 'Abbot', DATE '2001-01-01' FROM DUAL UNION ALL
SELECT 2, 'Betty', 'Baron', DATE '2001-02-01' FROM DUAL UNION ALL
SELECT 3, 'Carol', 'Count', DATE '2001-03-01' FROM DUAL UNION ALL
SELECT 4, 'Debra', 'Duke', DATE '2002-01-01' FROM DUAL UNION ALL
SELECT 5, 'Emily', 'Earl', DATE '2002-02-01' FROM DUAL UNION ALL
SELECT 6, 'Fiona', 'Friar', DATE '2003-01-01' FROM DUAL UNION ALL
SELECT 7, 'Gerri', 'Grace', DATE '2004-01-01' FROM DUAL;
輸出：
Acquire since 2001 (3 persons) ========== ID: 1 Alice Abbot ID: 2 Betty Baron ID: 3 Carol Count Acquire since 2002 (2 persons) ========== ID: 4 Debra Duke ID: 5 Emily Earl Acquire since 2003 (1 persons) ========== ID: 6 Fiona Friar Acquire since 2004 (1 persons) ========== ID: 7 Gerri Grace
小提琴
uj5u.com熱心網友回復：
您也可以在單個查詢中運行它：
SELECT
EXTRACT(YEAR FROM hire_date) AS "Acquire since",
COUNT(*) OVER (PARTITION BY EXTRACT(YEAR FROM hire_date)) "persons",
'ID: '||employee_id||' '||first_name||' '||last_name AS ID
FROM employees
ORDER BY 1 ASC;
輸出格式只是有點不同。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/522711.html
標籤：甲骨文plsql
上一篇：Oracle-選擇自定義訂單