想从 sql server 显示 12 个月的名字 [英] Want to display 12 months name from sql server
问题描述
我想从 sql server 显示 12 个月的名字.我虽然完成将月份名称插入临时表，然后在该表上触发选择语句.所以我不得不写12个插入表来插入12个月的名字.所以我搜索谷歌以找到更好的解决方案，我明白了.
这里是sql语句
WITH R(N) AS(选择 0联合所有选择 N+1从 R当 N <12)SELECT LEFT(DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())),3) AS [月]从 R上述脚本完美运行，但我的问题是我不明白它是如何工作的.我从不与 CTE 合作.
那么告诉我WITH R(N) AS
看看这个sql
SELECT LEFT(DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())),3) AS [month] FROM R当上面的 sql 执行时，它是如何获取 -N 的值的??因为在这里我没有为 -N 设置任何东西??所以请任何人帮助我了解整个事情是如何运作的.谢谢
我的第二阶段问题
看看就告诉我
;WITH Month(MonthNumber) AS(选择 0联合所有选择月数+1从几个月WHERE MonthNumber <12)我不清楚为什么只第一次执行下面的部分
SELECT 0联合所有选择月数+1从几个月WHERE MonthNumber <12从第二次开始只执行下面这部分
SELECT MonthNumber+1从几个月WHERE MonthNumber <12每当我们使用 Union 编写两个 sql 语句并执行时，它总是从两个 sql 状态返回数据，但特别是在这种情况下，从第二次开始为什么只有下面这部分执行
SELECT MonthNumber+1从几个月WHERE MonthNumber <12基本上我不熟悉使用递归技术的 CTE，这就是为什么我不清楚事情的原因.如果可能，请讨论 CTE 递归的工作原理.
声明@TotaDays SMALLINT声明@Month VARCHAR(15)声明@Year SMALLINT声明 @date 日期时间SET @Month = '一月'SET @Year = 2015SET @date = '01 ' + @Month + ' ' + CONVERT(VARCHAR(4),@Year)设置@TotaDays = 0SELECT @TotaDays = DATEDIFF(DAY, @date, DATEADD(MONTH, 1, @date));WITH 月份(MonthNumber) AS(选择 1联合所有选择月数+1从几个月WHERE MonthNumber <@TotaDays)从月份中选择*；With R(N) 是一个 公用表表达式.来自 MDSN:
一个公用表表达式(CTE)可以被认为是一个临时的在单个执行范围内定义的结果集SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句.CTE 是类似于派生表，因为它不存储为对象，并且仅在查询期间持续.与派生表不同，一个CTE 可以是自引用的，并且可以被多次引用相同的查询.
R 是您生成的结果集(或表)的名称.N 是 month 编号.
这个 CTE 特别是一个 递归通用表表达式.来自 MSDN:
<块引用>公共表表达式 (CTE) 提供了显着的优势能够引用自身，从而创建递归 CTE.一种递归 CTE 是一种重复执行初始 CTE 以返回数据的子集，直到得到完整的结果集.
使用 CTE 时，我的建议是使用名称更具描述性.因此，对于您的示例，您可以使用以下内容:
;WITH Month(MonthNumber) AS(选择 0联合所有选择月数+1从几个月WHERE MonthNumber <12)选择 *从几个月;在我的版本中，months 是您生成的结果集的名称，monthnumber 是值.这会生成一个从 0 到 12 的月数列表(参见 Demo).>
结果:
<代码>|月号 |---------------|0 ||1 ||2 ||3 ||4 ||5 ||6 ||7 ||8 ||9 ||10 ||11 ||12 |
然后紧随其后的 SELECT 语句使用 CTE 结果集的值来获取月份名称.
最终查询(参见演示):
;WITH Month(MonthNumber) AS(选择 0联合所有选择月数+1从几个月WHERE MonthNumber <12)SELECT LEFT(DATENAME(MONTH,DATEADD(MONTH,-MonthNumber,GETDATE())),3) AS [月]从几个月;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.
here is the sql statement
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.
so tell me what is the meaning of WITH R(N) AS
and see this 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
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.
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;
The With R(N) is a Common Table Expression. From 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.
The R is the name of the result set (or table) that you are generating. And the N is the month number.
This CTE in particular is a Recursive Common Table Expression. From 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.
When using CTE my suggestion would to be more descriptive with the names. So for your example you could use the following:
;WITH months(MonthNumber) AS
(
SELECT 0
UNION ALL
SELECT MonthNumber+1
FROM months
WHERE MonthNumber < 12
)
select *
from months;
In my version the months is the name of the result set that you are producing and the monthnumber is the value. This produces a list of the Month Numbers from 0-12 (See Demo).
Result:
| MONTHNUMBER |
---------------
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
Then the SELECT statement immediately after is using the values of the CTE result set to get you the Month Names.
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;
这篇关于想从 sql server 显示 12 个月的名字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
