英文:
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;
2023Q12022Q42022Q32022Q22022Q12021Q42021Q32021Q22021Q1......
I tried creating the temp data as
WITH t(n) AS (SELECT 1900 from dualUNION ALLSELECT 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 dualUNION ALLSELECT n+1 from t WHERE n < 2003)SELECT t.n || 'Q' || y.qFROM tcross join (select 1 as q from dualunion all select 2 from dualunion all select 3 from dualunion all select 4 from dual) y
结果：
T.N||'Q'||Y.Q-------------2000Q12000Q22000Q32000Q42001Q12001Q22001Q32001Q42002Q12002Q22002Q32002Q42003Q12003Q22003Q32003Q4
请参见db<>fiddle中的示例。
英文:
You can cross-join the result with the quarters. For example:
WITH t(n) AS (SELECT 2000 from dualUNION ALLSELECT n+1 from t WHERE n < 2003)SELECT t.n || 'Q' || y.qFROM tcross join (select 1 as q from dualunion all select 2 from dualunion all select 3 from dualunion all select 4 from dual) y
Result:
T.N||'Q'||Y.Q-------------2000Q12000Q22000Q32000Q42001Q12001Q22001Q32001Q42002Q12002Q22002Q32002Q42003Q12003Q22003Q32003Q4
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_quartersFROM DUALCONNECT 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_quartersFROM DUALCONNECT BY ADD_MONTHS(DATE '1900-01-01', 3 * LEVEL - 3) <= SYSDATE;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论