如何将派生的列列表（表和列别名）应用于SQL Server中的表值函数调用 [英] How to apply a derived column list (table and column aliases) to a table valued function call in SQL Server

问题描述

我正在像这样在SQL Server中模拟PostgreSQL风格的 generate_series（）表值函数：

I'm emulating PostgreSQL style generate_series() table-valued functions in SQL Server as such:

CREATE FUNCTION generate_series(@d1 DATE, @d2 DATE)
RETURNS TABLE AS
RETURN
  -- Ignore implementation (returning a single DATE column) for this question

现在可以按预期使用：

SELECT *
FROM generate_series(
  CAST ('2005-07-01' AS DATE),
  CAST ('2005-07-31' AS DATE)
) t

现在我想使用派生列列表语法重命名带有表的列，如下所示：

Now I'd like to use derived column list syntax to rename columns along with the table as such:

SELECT *
FROM generate_series(
  CAST ('2005-07-01' AS DATE),
  CAST ('2005-07-31' AS DATE)
) t(col)

正在产生此错误：

消息195，级别15，状态15 ，第5行

'generate_series'不是公认的函数名称。

Msg 195, Level 15, State 15, Line 5
'generate_series' is not a recognized function name.

...而这种语法（使函数合格）...

... whereas this syntax (qualifying the function) ...

SELECT *
FROM dbo.generate_series(
  CAST ('2005-07-01' AS DATE),
  CAST ('2005-07-31' AS DATE)
) t(col)

...产生此错误：

消息317，级别16，状态1，第5行

表值函数'generate_series'不能具有列别名

Msg 317, Level 16, State 1, Line 5
Table-valued function 'generate_series' cannot have a column alias.

在我看来，这种语法是完全正确的。它在PostgreSQL，标准SQL和普通 T-SQL中也是如此，例如：

This syntax is perfectly valid in my opinion. It works this way in PostgreSQL, standard SQL, and in "ordinary" T-SQL as well, e.g.:

SELECT *
FROM (
  VALUES (1),(2)
) t(col)

我在做什么错？我怎样才能做到这一点？ （我知道可以模仿这个，但我想真正使用派生的列列表语法）。

What am I doing wrong? How could I achieve this? (I'm aware of the possibility of emulating this, but I'd like to really use derived column list syntax).

我正在使用SQL Server Express 2014

I'm using SQL Server Express 2014

推荐答案

from子句的相关部分在这里。

The relevant part of the from clause is here.

column_alias 仅支持派生表和 @ variable.function_call

[ FROM { <table_source> } [ ,...n ] ]   
<table_source> ::=   
{  
    table_or_view_name [ [ AS ] table_alias ]   
        [ <tablesample_clause> ]   
        [ WITH ( < table_hint > [ [ , ]...n ] ) ]   
    | rowset_function [ [ AS ] table_alias ]   
        [ ( bulk_column_alias [ ,...n ] ) ]   
    | user_defined_function [ [ AS ] table_alias ]  
    | OPENXML <openxml_clause>   
    | derived_table [ [ AS ] table_alias ] [ ( column_alias [ ,...n ] ) ]   
    | <joined_table>   
    | <pivoted_table>   
    | <unpivoted_table>  
    | @variable [ [ AS ] table_alias ]  
    | @variable.function_call ( expression [ ,...n ] )   
        [ [ AS ] table_alias ] [ (column_alias [ ,...n ] ) ]  
    | FOR SYSTEM_TIME <system_time>   
}  

因此，至少暂时而言，您需要包装 user_defined_function 调用。例如在派生表或CTE中为列添加别名。

So, at least for the time being, you would need to wrap the user_defined_function call. e.g. in a derived table or CTE to alias the columns.

这篇关于如何将派生的列列表（表和列别名）应用于SQL Server中的表值函数调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！