如何在SQL中加入“固定向量”？

By "fixed vector" I mean a static list of values, like 1 through 24.

“固定向量”是指静态值列表,如1到24。

The current query looks like this (simplified)

当前查询看起来像这样(简化)

SELECT Period, Profit FROM Projections

But the data is "sparse" — so there's not a row for every period.

但数据“稀疏” - 所以每个时期都没有一行。

What query will give me a row for peiods 1-24 every time, with zeros (or NULLs) where there's no data?

什么查询每次都会为peiods 1-24提供一行,其中没有数据的零(或NULL)?

I would like to do this with just the query to avoid a mess of client code.

我想只使用查询来避免一堆客户端代码。

Thanks!

2 个解决方案

#1

You could make a udf called udfRange(start int,count int) or something like that, and left-join to the output of the function.

你可以创建一个名为udfRange的udf(start int,count int)或者类似的东西,然后左连接到函数的输出。

Or for something really quick and dirty, you could join to a subselect that looked like

或者对于非常快速和肮脏的东西,你可以加入一个看起来像的子选择

SELECT DATA.Period, P.Profit
FROM (
SELECT 1 AS Period
UNION SELECT 2
...
UNION SELECT 24) AS DATA 
LEFT JOIN Projections P ON DATA.Period = P.Period

#2

Why not create a 'Periods' lookup table, with values 1 - 24 (and any other columns that might be relevant, like a description of the period, or its name) then do a left outer join between the Periods lookup table and your projections table.

为什么不创建一个'Periods'查找表,其值为1 - 24(以及可能相关的任何其他列,如句点的描述或其名称),然后在Periods查找表和您的投影之间执行左外连接表。

#1