CREATE TABLE test1 (
e_id NUMBER(1),
e_ques_id NUMBER(10)
);
INSERT INTO test1 VALUES(1,3);
INSERT INTO test1 VALUES(1,4);
CREATE TABLE test_ref (
code NUMBER(1),
c_value VARCHAR2(20)
);
INSERT INTO test_ref VALUES(3,'May');
INSERT INTO test_ref VALUES(4,2022);
COMMIT;
Tool Used: SQL Developer(18c)
有兩個表test1，test_ref我想從test_ref表中獲取值以獲取月份和年份列值。
預期輸出：
------ ------------
| e_id | month_year |
------ ------------
| 1 | May 2022 |
------ ------------
我的嘗試：
SELECT t.e_id,
LISTAGG(c_value,' ')month_year
FROM test1 t
JOIN test_ref tr ON(t.e_ques_id = tr.code)
WHERE t.e_ques_id IN(3,4)
GROUP BY t.e_id;
這給了我正在尋找的確切結果，但我需要在現有視圖中添加此邏輯，為此我需要使用聚合或案例運算式來帶來結果。因此我嘗試使用CASE，MAX但它給了我一個不是單個組函式的錯誤
我的嘗試（導致錯誤）：
SELECT t.e_id,
MAX(CASE
WHEN t.e_ques_id IN(3,4) THEN LISTAGG(c_value,' ')
END )month_year
FROM test1 t
JOIN test_ref tr ON(t.e_ques_id = tr.code)
GROUP BY t.e_id;
uj5u.com熱心網友回復：
To me, it is kind of difficult to guess why existing view requires aggregate function or case expression.
Anyway: if you use max in its analytic form, skip case expression and - instead - move e_ques_id condition into the where clause (as you already did in query that works), you'd get this:
SQL> SELECT t.e_id,
2 MAX (LISTAGG (c_value, ' ') WITHIN GROUP (ORDER BY code))
3 OVER (ORDER BY NULL) month_year
4 FROM test1 t JOIN test_ref tr ON t.e_ques_id = tr.code
5 WHERE t.e_ques_id IN (3, 4)
6 GROUP BY t.e_id;
E_ID MONTH_YEAR
---------- ---------------
1 May 2022
SQL>
I have no idea whether this would (or would not) work in that view, but - you may try it.
uj5u.com熱心網友回復：
You can use conditional aggregation inside LISTAGG:
SELECT t.e_id,
LISTAGG(CASE WHEN t.e_ques_id IN (3,4) THEN c_value END, ' ')
WITHIN GROUP (ORDER BY t.e_ques_id) AS month_year
FROM test1 t
JOIN test_ref tr ON(t.e_ques_id = tr.code)
GROUP BY t.e_id;
Which, for the sample data:
CREATE TABLE test1 (e_id, e_ques_id) AS
SELECT 1, LEVEL FROM DUAL CONNECT BY LEVEL <= 5;
CREATE TABLE test_ref (code, c_value) AS
SELECT 1, 'Not this' FROM DUAL UNION ALL
SELECT 3, 'May' FROM DUAL UNION ALL
SELECT 4, '2022' FROM DUAL UNION ALL
SELECT 5, 'Not this either' FROM DUAL;
Outputs:
E_ID MONTH_YEAR 1 May 2022
db<>fiddle here
uj5u.com熱心網友回復：
This is how you can use Max() OVER() though I don't understand what you want to do, but maybe this can help...
WITH
test1 AS
(
SELECT 1 "E_ID", 3 "E_QUES_ID" FROM DUAL UNION ALL
SELECT 1 "E_ID", 4 "E_QUES_ID" FROM DUAL UNION ALL
SELECT 1 "E_ID", 5 "E_QUES_ID" FROM DUAL UNION ALL
SELECT 1 "E_ID", 6 "E_QUES_ID" FROM DUAL
),
test_ref AS
(
SELECT 3 "CODE", 'May' "C_VALUE" FROM DUAL UNION ALL
SELECT 4 "CODE", '2022' "C_VALUE" FROM DUAL UNION ALL
SELECT 5 "CODE", 'Jun' "C_VALUE" FROM DUAL UNION ALL
SELECT 6 "CODE", '2022' "C_VALUE" FROM DUAL
)
SELECT
E_ID,
Max(LISTAGG(MY, ' ') WITHIN GROUP (ORDER BY E_ID, E_QUES_ID)) OVER() "MONTH_YEAR"
FROM
(
SELECT
t.E_ID,
t.E_QUES_ID,
CASE WHEN t.E_QUES_ID IN(3, 4) THEN tr.C_VALUE END "MY"
FROM
test1 t
INNER JOIN
test_ref tr ON(t.E_QUES_ID = tr.CODE)
ORDER BY
t.E_ID,
t.E_QUES_ID
)
GROUP BY
E_ID
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/481652.html