如何將一個值從一行重復到其他行？這是表格和所需的結果：
普通表：
|SURVEY_ID | SONDA| DATA_CRIA | ANSWER
CHK-SON-CS-07 PR-05 06/04/2022 1
CHK-SON-CS-07 06/04/2022 2
CHK-SON-CS-07 06/04/2022 3
CHK-SON-CS-07 06/04/2022 4
我需要在所有行中重復 PR-05。
期望的結果
SURVEY_ID | SONDA | DATA_CRIA | ANSWER
CHK-SON-CS-07 PR-05 06/04/2022 1
CHK-SON-CS-07 PR-05 06/04/2022 2
CHK-SON-CS-07 PR-05 06/04/2022 3
CHK-SON-CS-07 PR-05 06/04/2022 4
uj5u.com熱心網友回復：
添加到@Huda Khan 答案，鑒于 SONDA 在任何行號（不僅僅是第一行）上都不為空。
select
(select sonda from test
where sonda is not null fetch first 1 rows only) sonda
from test
connect by level=(select count(*) from test);
或 - 使用 CTE
with cte as
(select sonda from test
where sonda is not null
fetch first 1 rows only)
select cte.sonda from test,cte
uj5u.com熱心網友回復：
最簡單的是這樣的：
select survey_id, min(sonda) over () as sonda, data_cria, answer
from <table_name>
;
分析函式min從列中回傳唯一的非空值；作為分析函式（與其聚合版本相反），該值將被復制到輸入表的每一行。
更有可能的是，您會想要這樣的東西，其中有多組行（by survey_id，或者可能 by survey_idand data_cria），每個組中有一個非空值sonda，并且您將希望在每個組內執行相同的“復制”（而不是結束整個表）。如果是這樣，您可以輕松地調整解決方案：代替over ()您使用磁區子句，例如over(partition by survey_id)或over (partition by survey_id, date_cria)。
uj5u.com熱心網友回復：
樣品表：
select * from test_table
SURVEY_ID | SONDA | DATA_CRIA | ANSWEAR
CHK-SON-CS-07 PR-05 06.04.2022 1
CHK-SON-CS-07 06.04.2022 2
CHK-SON-CS-07 06.04.2022 3
CHK-SON-CS-07 06.04.2022 4
CHK-SON-CS-07 06.04.2022 5
使用此查詢：
select t1.survey_id, t2.sonda, t1.data_cria, t1.answer
from test_table t1,
(select survey_id,sonda from test_table where sonda is not null) t2
where t1.survey_id=t2.survey_id
結果：
SURVEY_ID | SONDA | DATA_CRIA | ANSWEAR
CHK-SON-CS-07 PR-05 06.04.2022 1
CHK-SON-CS-07 PR-05 06.04.2022 2
CHK-SON-CS-07 PR-05 06.04.2022 3
CHK-SON-CS-07 PR-05 06.04.2022 4
CHK-SON-CS-07 PR-05 06.04.2022 5
uj5u.com熱心網友回復：
您可以使用lag(ignore nulls)：
SELECT COALESCE(SONDA,lag(SONDA ignore nulls) over (order by ANSWER))
FROM TABLENAME;
或使用最簡單的解決方案：
SELECT
t1.SURVEY_ID ,
CASE WHEN t1.SONDA IS NULL
THEN (SELECT top 1 t2.SONDA
FROM TABLE AS t2
WHERE t2.ANSWER< t1.ANSWER AND
t2.SONDA IS NOT NULL
)
ELSE t1.SONDA
END AS SONDA,
t1.ANSWER
FROM TABLE AS t1;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/457329.html