代码之家 › 专栏 › 技术社区 › Sarah Vessels

帮助将本周的查询从Oracle PL/SQL转换为SQL Server 2008

plsql sql-server-2008 tsql oracle sql

Sarah Vessels · 技术社区 · 15 年前

我有以下查询在我的Oracle数据库中运行,我希望具有与SQL Server 2008数据库相同的查询:

SELECT TRUNC( /* Midnight Sunday */
         NEXT_DAY(SYSDATE, 'SUN') - (7*LEVEL)
       ) AS week_start,
       TRUNC( /* 23:59:59 Saturday */
         NEXT_DAY(NEXT_DAY(SYSDATE, 'SUN') - (7*LEVEL), 'SAT') + 1
       ) - (1/(60*24)) + (59/(60*60*24)) AS week_end
FROM DUAL
CONNECT BY LEVEL <= 4 /* Get the past 4 weeks */

查询的作用是获取最近4周的每周开始和周末。周数是任意的,应该可以在我想要的SQL Server查询中轻松修改。它生成如下数据:

WEEK_START          WEEK_END
2010-03-07 00:00:00 2010-03-13 23:59:59
2010-02-28 00:00:00 2010-03-06 23:59:59
...

我现在一直在翻译的部分是 CONNECT BY LEVEL 因为看起来SQL Server 2008没有等价的。我想简单地调整一下 CONNECT BY LEVEL <= 4 并让查询生成更多或更少的周(即,我不想调整多个周 UNION ALL 陈述)。

编辑: 以下是到目前为止我所掌握的本周开始和结束的内容:

   SELECT week_start,
          DATEADD(SECOND, -1, DATEADD(DAY, 7, week_start)) AS week_end
   FROM (
          SELECT CAST(
                   CONVERT(
                     VARCHAR(10),
                     DATEADD(DAY, 1-DATEPART(DW, GETDATE()), GETDATE()),
                     111
                   ) AS DATETIME
                 ) AS week_start
        ) AS week_start_view

我不介意查询是否显示当前周的开始和结束日期,或者它是否在前一周开始。

5 回复 | 直到 14 年前

Community CDub 8 年前

我修改 OMG Ponies “回答是因为它看起来是个好主意,它错了 week_end 每周的值,也显示了未来几周而不是过去几周的值。我想到了以下几点:

WITH dates AS (
  SELECT DATEADD(
           DD, 
           1 - DATEPART(DW, CONVERT(VARCHAR(10), starting_date, 111)), 
           CONVERT(VARCHAR(10), starting_date, 111)
         ) AS midnight
  FROM (
         SELECT DATEADD(WEEK, -3, GETDATE()) AS starting_date
       ) AS starting_date_view

  UNION ALL

  SELECT DATEADD(DD, 7, midnight)
  FROM dates
  WHERE DATEADD(DD, 7, midnight) < GETDATE()
) SELECT midnight AS week_start,
         DATEADD(SS, -1, DATEADD(DAY, 7, midnight)) AS week_end
  FROM dates

它产生了过去4周:

week_start                 week_end
2010-02-14 00:00:00.000    2010-02-20 23:59:59.000
2010-02-21 00:00:00.000    2010-02-27 23:59:59.000
2010-02-28 00:00:00.000    2010-03-06 23:59:59.000
2010-03-07 00:00:00.000    2010-03-13 23:59:59.000

这比我的好 previous answer ,我认为,因为它不依赖具有特定行数的另一个表。生成的周数只能通过更改一个数字来更改:3英寸 SELECT DATEADD(WEEK, -3, GETDATE()) AS starting_date . 包括当前周,该数字表示应显示当前周之前的其他周数。

从过去几周到现在迭代,不包括本周

更新: 这里有一个不包括本周内容的版本:

WITH dates AS (
  SELECT DATEADD(
           DD,
           1 - DATEPART(DW, CONVERT(VARCHAR(10), starting_date, 111)), 
           CONVERT(VARCHAR(10), starting_date, 111)
         ) AS midnight_sunday
  FROM (
         SELECT DATEADD(WEEK, -4, GETDATE()) AS starting_date
       ) AS starting_date_view

  UNION ALL

  SELECT DATEADD(DD, 7, midnight_sunday)
  FROM dates
  WHERE DATEADD(DD, 7, midnight_sunday) <
        DATEADD(
          DD,
          1 - DATEPART(DW, CONVERT(VARCHAR(10), GETDATE(), 111)), 
          CONVERT(VARCHAR(10), GETDATE(), 111)
        )
) SELECT midnight_sunday AS week_start,
         DATEADD(SS, -1, DATEADD(DAY, 7, midnight_sunday)) AS week_end
  FROM dates

其结果:

week_start                 week_end
2010-02-07 00:00:00.000    2010-02-13 23:59:59.000
2010-02-14 00:00:00.000    2010-02-20 23:59:59.000
2010-02-21 00:00:00.000    2010-02-27 23:59:59.000
2010-02-28 00:00:00.000    2010-03-06 23:59:59.000

过去几个月到现在的迭代

后来我发现需要这个查询的每月版本。修改如下:

WITH dates AS (
  SELECT CAST(
        FLOOR(CAST(starting_date AS DECIMAL(12, 5))) -
        (DAY(starting_date) - 1) AS DATETIME
      ) AS month_start
  FROM (
         SELECT DATEADD(MONTH, -3, GETDATE()) AS starting_date
       ) AS starting_date_view

  UNION ALL

  SELECT DATEADD(MONTH, 1, month_start)
  FROM dates
  WHERE DATEADD(MONTH, 1, month_start) < GETDATE()
) SELECT month_start,
         DATEADD(SS, -1, DATEADD(MONTH, 1, month_start)) AS month_end
  FROM dates
  ORDER BY month_start DESC

Community CDub 8 年前

使用(但不要忘记 vote for Sarah )以下内容:

WITH dates AS (
    SELECT DATEADD(DD, 
                   1 - DATEPART(DW, CONVERT(VARCHAR(10), starting_date, 111)), 
                   CONVERT(VARCHAR(10), starting_date, 111)
                   ) AS midnight
      FROM (SELECT DATEADD(WEEK, -3, GETDATE()) AS starting_date) AS starting_date_view
    UNION ALL
    SELECT DATEADD(DD, 7, midnight)
      FROM dates
     WHERE DATEADD(DD, 7, midnight) < GETDATE()) 
SELECT midnight AS week_start,
       DATEADD(SS, -1, DATEADD(DAY, 7, midnight)) AS week_end
  FROM dates

先前:

将一周的第一天设置为星期日 SET DATEFIRST 命令:
```
SET DATEFIRST 7
```

SQL Server 2005+相当于Oracle的 CONNECT BY LEVEL 是递归CTE(ANSI标准BTW)。用途:

WITH dates AS (
   SELECT DATEADD(DD, 
                  1 - DATEPART(DW, CONVERT(VARCHAR(10), GETDATE(), 111)), 
                  CONVERT(VARCHAR(10), GETDATE(), 111)) AS date
   UNION ALL
   SELECT DATEADD(dd, 7, d.date)
     FROM dates d
    WHERE DATEADD(dd, 7, d.date) <= DATEADD(dd, 4*7, GETDATE()))
SELECT t.date AS week_start,
       DATEADD(ss, -1, DATEADD(DAY, 7, t.date)) AS week_end
  FROM dates t

见 this link for explaining how to get the first day of the week . 要更改周数,请更改 DATEADD(dd, 4*7, GETDATE()) ,在哪里 4 表示要生成的周数。

Aaron Bertrand 15 年前

你只需要一套:

;WITH cte(n) AS
(
    SELECT 0
    UNION ALL SELECT 1
    UNION ALL SELECT 2
    UNION ALL SELECT 3
)
  SELECT week_start,
          DATEADD(SECOND, -1, DATEADD(DAY, 7, week_start)) AS week_end
   FROM (
          SELECT CAST(
                   CONVERT(
                     VARCHAR(10),
                     DATEADD(WEEK, -n, DATEADD(DAY, 1-DATEPART(DW, GETDATE()), GETDATE())),
                     111
                   ) AS DATETIME
                 ) AS week_start
                           FROM cte
        ) AS week_start_view;

但是,我要提醒您,如果您的数据是datetime,并且要将这些边界用于查询范围,则应使用开放范围,例如>=03/07和<03/14。这样,你就不会错过23:59:59到午夜之间发生的任何争吵;尽管很少见,但它们可能很重要。

devio 15 年前

根据您的代码:

SELECT DATEADD(day, weeks.week * -7, week_start) AS week_start,
       DATEADD(SECOND, -1, DATEADD(DAY, (weeks.week-1) * -7, week_start)) AS week_end
FROM (
      SELECT CAST(
               CONVERT(
                 VARCHAR(10),
                 DATEADD(DAY, 1-DATEPART(DW, GETDATE()), GETDATE()),
                 111
               ) AS DATETIME
             ) AS week_start
     ) AS week_start_view,
     ( SELECT 0 AS week UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) AS weeks

Sarah Vessels 15 年前

这就是我想到的。它有点简陋,因为它至少依赖于我的一个表,其中有行>=我要选择的周数。我可以稍微调整一下,把这一周包括在内。

   SELECT week_start,
          DATEADD(SECOND, -1, DATEADD(DAY, 7, week_start)) AS week_end
   FROM (
          SELECT CAST(
                   CONVERT(
                     VARCHAR(10),
                     DATEADD(
                       DAY,
                       1-DATEPART(DW, GETDATE()),
                       DATEADD(DAY, -7*level, GETDATE())
                     ),
                     111
                   ) AS DATETIME
                 ) AS week_start
          FROM (
                 SELECT level
                 FROM (
                        SELECT ROW_NUMBER() OVER(ORDER BY RAND()) AS level
                        FROM my_table_with_at_least_21_rows
                      ) AS all_rows_view
                 WHERE level <= 21
               ) AS level_view
        ) AS week_start_view

这是过去21周,从上周开始。以下是示例数据:

week_start                  week_end
2010-02-28 00:00:00.000     2010-03-06 23:59:59.000
2010-02-21 00:00:00.000     2010-02-27 23:59:59.000
2010-02-14 00:00:00.000     2010-02-20 23:59:59.000
2010-02-07 00:00:00.000     2010-02-13 23:59:59.000
...