时间戳

首页 > 解决方案 > 在 SQL 中使用 CTE 创建表

sql-server-2008 - 在 SQL 中使用 CTE 创建表

问题描述

我有一个查询在哪里可以使用 CTE 创建表？因为我无法在我的数据库中创建任何新表。我正在尝试计算工作日和经过的工作日数，不包括节假日和周末。但是这个创建表不起作用。如果有什么办法吗？由于这个创建表，我没有得到任何结果。

create table holidays(holiday date)
insert holidays values ('11-22-2018'),('11-23-2018')

;with dates as(
    select dateadd(d,-day(getdate())+1,convert(date,getdate())) as startofmonth,
    dateadd(d,-1,dateadd(m,1,dateadd(d,-day(getdate())+1,convert(date,getdate())))) as endofmonth,
    convert(date,getdate()) as today
)
,holidaycount as (
    select count(*) as holidaysinmonth,
        sum(case when holiday<=today then 1 else 0 end) as holidaystodate
    from dates
    join holidays on holiday between startofmonth and endofmonth
)
,daycounts as(
    select dates.*,

       (DATEDIFF(dd, startofmonth, endofmonth) + 1)
      -(DATEDIFF(wk, startofmonth, endofmonth) * 2)
      -(CASE WHEN DATENAME(dw, startofmonth) = 'Sunday' THEN 1 ELSE 0 END) 
      -(CASE WHEN DATENAME(dw, endofmonth) = 'Saturday' THEN 1 ELSE 0 END)
      -isnull(holidaysinmonth,0) as wkdaysinmonth,

       (DATEDIFF(dd, startofmonth, today) + 1)
      -(DATEDIFF(wk, startofmonth, today) * 2)
      -(CASE WHEN DATENAME(dw, startofmonth) = 'Sunday' THEN 1 ELSE 0 END) 
      -(CASE WHEN DATENAME(dw, today) = 'Saturday' THEN 1 ELSE 0 END)
      -isnull(holidaystodate,0) as wkdaystodate

    from dates
    cross join holidaycount
) 

select * from daycounts

谢谢。

标签： sql-server-2008

解决方案

使用表变量：

declare @holidays table (holiday date)
insert @holidays values ('11-22-2018'),('11-23-2018')

;with dates as(
    select dateadd(d,-day(getdate())+1,convert(date,getdate())) as startofmonth,
    dateadd(d,-1,dateadd(m,1,dateadd(d,-day(getdate())+1,convert(date,getdate())))) as endofmonth,
    convert(date,getdate()) as today
)
,holidaycount as (
    select count(*) as holidaysinmonth,
        sum(case when holiday<=today then 1 else 0 end) as holidaystodate
    from dates
    join @holidays on holiday between startofmonth and endofmonth
)

使用 cte：

;with dates as(
    select dateadd(d,-day(getdate())+1,convert(date,getdate())) as startofmonth,
    dateadd(d,-1,dateadd(m,1,dateadd(d,-day(getdate())+1,convert(date,getdate())))) as endofmonth,
    convert(date,getdate()) as today
), holidays as (
    select holiday
    from (values ('11-22-2018'),('11-23-2018') ) x (holiday)
 )
,holidaycount as (
    select count(*) as holidaysinmonth,
        sum(case when holiday<=today then 1 else 0 end) as holidaystodate
    from dates
    join holidays on holiday between startofmonth and endofmonth
)

请注意日期文字，您假设数据库将接受 dd-mm-yyyy 字符串作为日期。这可能并不总是正确的，因此请改用 YYYYMMDD，SQL Server 会将其理解为日期，或者下一个最好的是 YYYY-MM-DD。

推荐阅读