Teradata SQL：选择文字

发布于 2024-09-07 03:40:44 字数 416 浏览 4 评论 0原文

我想使用任意数字列表作为选择的输入。当然，选项 A 是创建一个仅包含值（例如 1、2、3）的临时表。

我希望你们知道选项>A是什么。

假设语句如下：

select Fx,
XXXXXX as Foo
from MyTable
where MyTest depends on each XXXXXX

因此，如果我可以神奇地使 XXXXXX 成为值列表 (1,2,3)，我将得到如下结果集：

    My val | Foo
    -------+---
       cat | 1
     mouse | 2
cheesecake | 3

同样，我可以从表中获取输入，但我不喜欢这样做没有必要。各位大师，请插话。TIA

。

原文

I want to use a list of arbitrary numbers as a sort of input to a select. Option A, of course, is to create a temporary table that contains just the values (e.g., 1,2,3).

I hope that you folks know what Option >A is.

Suppose the statement is like:

select Fx,
XXXXXX as Foo
from MyTable
where MyTest depends on each XXXXXX

So if I could magically make XXXXXX a list of values (1,2,3), I'd have a resultset like:

    My val | Foo
    -------+---
       cat | 1
     mouse | 2
cheesecake | 3

Again, I could source the inputs from a table, but I prefer not to if it's not necessary. Gurus, please chime in.

TIA.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

峩卟喜欢 2024-09-14 03:40:44

您可能会发现使用 ROW_NUMBER() 窗口聚合函数会取得成功。

随机排序

SELECT CALENDAR_DATE
     , ROW_NUMBER()
       OVER (ORDER BY 1)
FROM SYS_CALENDAR.CALENDAR
WHERE CALENDAR_DATE BETWEEN DATE '2010-06-01' AND DATE 
;

或按列排序

SELECT CALENDAR_DATE
     , ROW_NUMBER()
       OVER (ORDER BY CALENDAR_DATE)
FROM SYS_CALENDAR.CALENDAR
WHERE CALENDAR_DATE BETWEEN DATE '2010-06-01' AND DATE 
;

或按另一列分区以重新启动序列

SELECT CALENDAR_DATE
     , YEAR_OF_CALENDAR
     , ROW_NUMBER()
       OVER (PARTITION BY YEAR_OF_CALENDAR
             ORDER BY CALENDAR_DATE)
FROM SYS_CALENDAR.CALENDAR
WHERE CALENDAR_DATE BETWEEN DATE '2009-11-01' AND DATE 
;

；

You will probably find success using the ROW_NUMBER() Window Aggregate function.

Random Order

SELECT CALENDAR_DATE
     , ROW_NUMBER()
       OVER (ORDER BY 1)
FROM SYS_CALENDAR.CALENDAR
WHERE CALENDAR_DATE BETWEEN DATE '2010-06-01' AND DATE 
;

OR Order by the column

SELECT CALENDAR_DATE
     , ROW_NUMBER()
       OVER (ORDER BY CALENDAR_DATE)
FROM SYS_CALENDAR.CALENDAR
WHERE CALENDAR_DATE BETWEEN DATE '2010-06-01' AND DATE 
;

OR Partition by another column to restart the sequence

SELECT CALENDAR_DATE
     , YEAR_OF_CALENDAR
     , ROW_NUMBER()
       OVER (PARTITION BY YEAR_OF_CALENDAR
             ORDER BY CALENDAR_DATE)
FROM SYS_CALENDAR.CALENDAR
WHERE CALENDAR_DATE BETWEEN DATE '2009-11-01' AND DATE 
;

;

回复收藏 0 原文

~没有更多了~