英文:
Printing a pattern of alphanumeric numbers in Oracle sql
问题
SELECT语句中如何创建一个临时表，用于保存字母数字组合的列表。
从temp中选择yearlyQuarters;
2023Q1
2022Q4
2022Q3
2022Q2
2022Q1
2021Q4
2021Q3
2021Q2
2021Q1
......
我尝试创建临时数据：
WITH t(n) AS (
SELECT 1900 from dual
UNION ALL
SELECT n+1 from t WHERE n < 3000)
SELECT * FROM t;
我不确定如何将季度信息添加到这些数字中。
英文:
How can I create a temp table in a sql select statement which will hold list of alphanumeric numbers.
Select yearlyQuarters from temp;
2023Q1
2022Q4
2022Q3
2022Q2
2022Q1
2021Q4
2021Q3
2021Q2
2021Q1
......
I tried creating the temp data as
WITH t(n) AS (
SELECT 1900 from dual
UNION ALL
SELECT n+1 from t WHERE n < 3000)
SELECT * FROM t;
I am not sure how can I add the quarter details to the numbers.
答案1
得分: 0
以下是翻译好的部分：
您可以将结果与季度进行交叉连接。例如：
WITH t(n) AS (
SELECT 2000 from dual
UNION ALL
SELECT n+1 from t WHERE n < 2003)
SELECT t.n || 'Q' || y.q
FROM t
cross join (
select 1 as q from dual
union all select 2 from dual
union all select 3 from dual
union all select 4 from dual
) y
结果：
T.N||'Q'||Y.Q
-------------
2000Q1
2000Q2
2000Q3
2000Q4
2001Q1
2001Q2
2001Q3
2001Q4
2002Q1
2002Q2
2002Q3
2002Q4
2003Q1
2003Q2
2003Q3
2003Q4
请参见db<>fiddle中的示例。
英文:
You can cross-join the result with the quarters. For example:
WITH t(n) AS (
SELECT 2000 from dual
UNION ALL
SELECT n+1 from t WHERE n < 2003)
SELECT t.n || 'Q' || y.q
FROM t
cross join (
select 1 as q from dual
union all select 2 from dual
union all select 3 from dual
union all select 4 from dual
) y
Result:
T.N||'Q'||Y.Q
-------------
2000Q1
2000Q2
2000Q3
2000Q4
2001Q1
2001Q2
2001Q3
2001Q4
2002Q1
2002Q2
2002Q3
2002Q4
2003Q1
2003Q2
2003Q3
2003Q4
See example at db<>fiddle.
答案2
得分: 0
从日期 1900-01-01 开始，重复添加3个月以获取下一个季度，然后使用 TO_CHAR 格式化它：
SELECT TO_CHAR(
ADD_MONTHS(DATE '1900-01-01', 3 * LEVEL - 3),
'YYYY"Q"Q'
) AS yearly_quarters
FROM DUAL
CONNECT BY ADD_MONTHS(DATE '1900-01-01', 3 * LEVEL - 3) <= SYSDATE;
英文:
Start with the date 1900-01-01 and repeatedly add 3 months to get the next quarter and then use TO_CHAR to format it:
SELECT TO_CHAR(
ADD_MONTHS(DATE '1900-01-01', 3 * LEVEL - 3),
'YYYY"Q"Q'
) AS yearly_quarters
FROM DUAL
CONNECT BY ADD_MONTHS(DATE '1900-01-01', 3 * LEVEL - 3) <= SYSDATE;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论