想从 sql server 显示 12 个月的名字

Question

提问by Thomas

I want to display 12 months name from sql server. i though to accomplish insert month name into temp table and then fire select statement on that table. so i had to write 12 insert table to insert 12 months name. so i search google to find better solution and i got it.

我想从 sql server 显示 12 个月的名称。我虽然完成将月份名称插入临时表，然后在该表上触发选择语句。所以我不得不写12个插入表来插入12个月的名字。所以我搜索谷歌以找到更好的解决方案，我明白了。

here is the sql statement

这是sql语句

WITH R(N) AS
(
    SELECT 0
    UNION ALL
    SELECT N+1 
    FROM R
    WHERE N < 12
)

SELECT LEFT(DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())),3) AS [month]
FROM R

the above script works perfectly but my problem is i just do not understand how it works. i never work with CTE.

上面的脚本运行良好，但我的问题是我只是不明白它是如何工作的。我从不与 CTE 合作。

so tell me what is the meaning of WITH R(N) AS

所以告诉我是什么意思 WITH R(N) AS

and see this sql

并查看此 sql

SELECT LEFT(DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())),3) AS [month] FROM R
when above sql execute how it is getting value for -N ??
because here i have not set anything for -N ??

so please anyone help me to understand how whole thing works. thaks

所以请任何人帮助我了解整个事情是如何运作的。塔克斯

My Second Phase of Question

我的第二阶段问题

just have look a and tell me

看看然后告诉我

;WITH months(MonthNumber) AS
(
    SELECT 0
    UNION ALL
    SELECT MonthNumber+1 
    FROM months
    WHERE MonthNumber < 12
)

onething is not clear to me that why only first time the below part execute

我不清楚为什么只有第一次执行下面的部分

SELECT 0
UNION ALL
SELECT MonthNumber+1 
FROM months
WHERE MonthNumber < 12

and from the 2nd time only this below part execute

从第二次开始，只有下面的部分执行

SELECT MonthNumber+1 
FROM months
WHERE MonthNumber < 12

whenever we write two sql statement using Union and execute then always it return data from two sql state but specially in this case from the 2nd time why only this below part execute

每当我们使用 Union 编写两个 sql 语句并执行时，它总是从两个 sql 状态返回数据，但特别是在这种情况下，从第二次开始为什么只执行以下部分

SELECT MonthNumber+1 
FROM months
WHERE MonthNumber < 12

basically i not familiar with CTE with recursion technique and that is why things is not getting clear to me. if possible please discuss how CTE recursion works.

基本上我不熟悉使用递归技术的 CTE，这就是为什么我不清楚事情的原因。如果可能，请讨论 CTE 递归的工作原理。

DECLARE @TotaDays SMALLINT
DECLARE @Month VARCHAR(15)
DECLARE @Year SMALLINT
DECLARE @date DATETIME 
SET @Month = 'January'
SET @Year = 2015
SET @date = '01 ' + @Month + ' ' + CONVERT(VARCHAR(4),@Year)
SET @TotaDays = 0
SELECT @TotaDays = DATEDIFF(DAY, @date, DATEADD(MONTH, 1, @date))

;WITH months(MonthNumber) AS
(
    SELECT 1
    UNION ALL
    SELECT MonthNumber+1 
    FROM months
    WHERE MonthNumber < @TotaDays
)

select * from months;

Answer 1

回答by Taryn

The With R(N)is a Common Table Expression. From MDSN:

这With R(N)是一个公共表表达式。来自 MDSN：

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

公用表表达式 (CTE) 可以被认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集。CTE 类似于派生表，因为它不作为对象存储并且仅在查询期间持续。与派生表不同，CTE 可以是自引用的，并且可以在同一个查询中多次引用。

The Ris the name of the result set (or table) that you are generating. And the Nis the monthnumber.

该R是结果集（或表），您正在生成的名称。而且N是month数量。

This CTE in particular is a Recursive Common Table Expression. From MSDN:

这个 CTE 特别是一个递归公用表表达式。来自 MSDN：

A common table expression (CTE) provides the significant advantage of being able to reference itself, thereby creating a recursive CTE. A recursive CTE is one in which an initial CTE is repeatedly executed to return subsets of data until the complete result set is obtained.

公用表表达式 (CTE) 提供了能够引用自身的显着优势，从而创建递归 CTE。递归 CTE 是一种重复执行初始 CTE 以返回数据子集直到获得完整结果集的递归 CTE。

When using CTE my suggestion would to be more descriptive with the names. So for your example you could use the following:

使用 CTE 时，我的建议是使用名称更具描述性。因此，对于您的示例，您可以使用以下内容：

;WITH months(MonthNumber) AS
(
    SELECT 0
    UNION ALL
    SELECT MonthNumber+1 
    FROM months
    WHERE MonthNumber < 12
)
select *
from months;

In my version the monthsis the name of the result set that you are producing and the monthnumberis the value. This produces a list of the Month Numbers from 0-12 (See Demo).

在我的版本中，months是您正在生成的结果集的名称，monthnumber是值。这会生成一个从 0 到 12 的月数列表（参见演示）。

Result:

结果：

| MONTHNUMBER |
---------------
|           0 |
|           1 |
|           2 |
|           3 |
|           4 |
|           5 |
|           6 |
|           7 |
|           8 |
|           9 |
|          10 |
|          11 |
|          12 |

Then the SELECTstatement immediately after is using the values of the CTE result set to get you the Month Names.

然后SELECT紧随其后的语句使用 CTE 结果集的值来获取月份名称。

Final query (See Demo):

最终查询（见演示）：

;WITH months(MonthNumber) AS
(
    SELECT 0
    UNION ALL
    SELECT MonthNumber+1 
    FROM months
    WHERE MonthNumber < 12
)
SELECT LEFT(DATENAME(MONTH,DATEADD(MONTH,-MonthNumber,GETDATE())),3) AS [month]
FROM months;

Answer 2

回答by Saradhi

This query is to obtain all month names and month no's

此查询是获取所有月份名称和月份编号

SELECT DATENAME(MONTH, DATEADD(MM, s.number, CONVERT(DATETIME, 0))) AS [MonthName], 
MONTH(DATEADD(MM, s.number, CONVERT(DATETIME, 0))) AS [MonthNumber] 
FROM master.dbo.spt_values s 
WHERE [type] = 'P' AND s.number BETWEEN 0 AND 11
ORDER BY 2

Answer 3

回答by RIJWAN KASSAR

Try This,

尝试这个，

with Months as 
( 
    select month(GETDATE()) as Monthnumber, datename(month, GETDATE()) as name, 1 as number
    union all
    select month(dateadd(month,number,(GETDATE()))) Monthnumber ,datename(month, dateadd(month,number,(GETDATE()))) as name, number+1 
    from Months 
    where number<12
)   
select Monthnumber, name 
from Months 
order by Monthnumber

Answer 4

回答by Ian Preston

Rdefines the name of the CTE, and (N)defines the name of the column(s) in the CTE - in this case there is only one column.

R定义 CTE 的名称，并(N)定义CTE 中列的名称 - 在这种情况下，只有一列。

You can see that you are selecting from R in the second part of the statement.

您可以在语句的第二部分看到您正在从 R 中进行选择。

You are indeed setting the value of (N)when you select 0, i.e. the first and only column in the anchor portion of the CTE definition and later on when specifying select N+1in the recursive part of the CTE definition.

您确实在设置(N)when you的值select 0，即 CTE 定义的锚定部分中的第一列也是唯一一列，稍后在 CTE 定义select N+1的递归部分中指定时。

Answer 5

回答by ederbf

Try changing the second part of the code, that way you will see what is generated in the recursive part:

尝试更改代码的第二部分，这样您将看到递归部分生成的内容：

WITH R(N) AS
(
    SELECT 0
    UNION ALL
    SELECT N+1 
    FROM R
    WHERE N < 12
)

SELECT *
FROM R

You will obtain this result:

你会得到这样的结果：

By the way, i think it would be better to do SELECT 1instead of SELECT 0, so that you generate exactly 12 numbers and therefore you won't end up having an extra month

顺便说一句，我认为最好用SELECT 1代替SELECT 0，这样您就可以生成正好 12 个数字，因此您最终不会多出一个月

Answer 6

回答by Kasim Husaini

I modified to code shared earlier to give 12-month names with their respective month numbers in required sequence. Also, removed the dependency on the current date by adding 1st Jan 1900 as the fixed date.

我修改为之前共享的代码，以按要求的顺序给出 12 个月的名称及其各自的月份编号。此外，通过将 1900 年 1 月 1 日添加为固定日期来消除对当前日期的依赖。

WITH R (N)
AS
(SELECT
        0
    UNION ALL
    SELECT
        N + 1
    FROM R
    WHERE N < 12)

SELECT
    N
   ,DATENAME(MONTH, DATEADD(MONTH, N - 1, '1 Jan 1900')) AS [month]
FROM R
WHERE N > 0

Answer 7

回答by Benigno Geronimo

This query obtain information per period of time and get the list:

此查询获取每个时间段的信息并获取列表：

set language  'SPANISH'
DECLARE @table table(fechaDesde datetime , fechaHasta datetime ) 
INSERT @table VALUES('20151231' , '20161231');
WITH x AS 
    (
        SELECT   DATEADD( m , 1 ,fechaDesde ) as fecha  FROM @table
        UNION ALL
        SELECT  DATEADD( m , 1 ,fecha )
        FROM @table t INNER JOIN x ON  DATEADD( m , 1 ,x.fecha ) <= t.fechaHasta
    )
SELECT LEFT( CONVERT( VARCHAR, fecha , 112 ) , 6 ) as Periodo_Id 
,DATEPART ( dd, DATEADD(dd,-(DAY(fecha)-1),fecha)) Num_Dia_Inicio
,DATEADD(dd,-(DAY(fecha)-1),fecha) Fecha_Inicio
,DATEPART ( mm , fecha ) Mes_Id
,DATEPART ( yy , fecha ) Anio
,DATEPART ( dd, DATEADD(dd,-(DAY(DATEADD(mm,1,fecha))),DATEADD(mm,1,fecha))) Num_Dia_Fin
,DATEADD(dd,-(DAY(DATEADD(mm,1,fecha))),DATEADD(mm,1,fecha)) ultimoDia
,datename(MONTH, fecha) mes
,'Q' + convert(varchar(10),  DATEPART(QUARTER, fecha)) Trimestre_Name
FROM x 
OPTION(MAXRECURSION 0)

Answer 8

回答by user3219293

I think you are expecting this by seeing this you can understand Thank you.

我认为您通过看到这一点就可以理解这一点，谢谢。

with months(mnum) as (select 0 union all select mnum+1 from months where mnum <11) select left(datename(month,dateadd(month,mnum,getdate())),3) as mn from months

withmonths(mnum) as (select 0 union all select mnum+1 from months where mnum <11) select left(datename(month,dateadd(month,mnum,getdate())),3) as mn frommonths

David raja

大卫·拉贾

想从 sql server 显示 12 个月的名字

提问by Thomas

here is the sql statement

这是sql语句

My Second Phase of Question

我的第二阶段问题

回答by Taryn

回答by Saradhi

回答by RIJWAN KASSAR

回答by Ian Preston

回答by ederbf

回答by Kasim Husaini

回答by Benigno Geronimo

回答by user3219293

相关推荐

最近更新

标签

想从 sql server 显示 12 个月的名字

提问by Thomas

here is the sql statement

这是sql语句

My Second Phase of Question

我的第二阶段问题

回答by Taryn

回答by Saradhi

回答by RIJWAN KASSAR

回答by Ian Preston

回答by ederbf

回答by Kasim Husaini

回答by Benigno Geronimo

回答by user3219293

相关推荐

SQL SQL选择一列中的多行

SQL 如何在 postgresql 中合并两个表？

将包含数据的表复制到 SQL Server 2008 中的另一个数据库

SQL 如何按范围选择行？

相关推荐

最近更新

标签