我可以在查詢變數值中獲取作為雪花中的列名嗎？
SET "CURRENT_YEAR"=YEAR(CURRENT_DATE());
SELECT SUM("AMOUNT") AS "$CURRENT_YEAR" (here I want the value 2021)
FROM "DB"."SCHEMA"."TABLE"
WHERE YEAR("DATE") = $CURRENT_YEAR;
uj5u.com熱心網友回復：
請嘗試以下：
create or replace table test (
date date,
amount int
);
insert into test values
('2021-01-01', 100),
('2022-01-01', 56),
('2022-02-01', 67),
('2021-05-01', 38),
('2023-01-01', 150),
('2021-01-06', 400),
('2021-07-11', 120)
;
SET "CURRENT_YEAR"=YEAR(CURRENT_DATE());
with year_tbl as (
select year(date) as year, amount from test
where year = $CURRENT_YEAR
)
select *
from year_tbl
pivot(sum(amount) for year in ($CURRENT_YEAR)) as yr
;
------
| 2021 |
|------|
| 658 |
------
如果你想要不同的年份：
with year_tbl as (
select year(date) as year, amount from test
)
select *
from year_tbl
pivot(sum(amount) for year in (2020, 2021, 2022, 2023)) as yr
;
------ ------ ------ ------
| 2020 | 2021 | 2022 | 2023 |
|------ ------ ------ ------|
| NULL | 658 | 123 | 150 |
------ ------ ------ ------
uj5u.com熱心網友回復：
你的意思是這樣嗎
create or replace table fld_year as
(SELECT current_date() dt, 2021 as fld_year, 1 as AMT UNION ALL
SELECT current_date(),2021 as fld_year, 2 as r_num UNION ALL
SELECT current_date()- 900,2019 as fld_year, 3 as r_num UNION ALL
SELECT current_date()-400,2020 as fld_year, 4 as r_num );
SET "CURRENT_YEAR"=YEAR(CURRENT_DATE());
SELECT SUM(AMT) FROM fld_year WHERE YEAR(dt) = $CURRENT_YEAR;
SELECT * FROM fld_year WHERE YEAR(dt) = $CURRENT_YEAR;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/364944.html
上一篇：將資料從UIViewRepresentable函式傳遞給SwiftUI視圖
下一篇：在Julia變數中使用素數“'”