我如何加入“固定向量"网站?在SQL中? [英] How do I join to a "fixed vector" in SQL?

问题描述

固定向量"是指值的静态列表，例如1到24.

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

当前查询如下(简化)

SELECT Period, Profit FROM Projections

但是数据是稀疏的"-因此每个期间都没有一行.

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

什么查询每次都会给我一行1至24的小段位，没有数据的地方会带有零(或NULL)?

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

我只想通过查询来做到这一点，以避免混乱的客户端代码.

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

谢谢！

推荐答案

您可以创建一个名为udfRange(start int，count int)之类的udf，然后左键连接到函数的输出.

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

或者对于某些快速而又肮脏的事情，您可以加入一个看起来像这样的子选择

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

这篇关于我如何加入“固定向量"网站?在SQL中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！