当前位置： 文江博客 话题详情

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

发布于 2024-08-23 21:53:14 字数 1284 浏览 3 评论 0 原文

我在 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

我不介意查询是否显示当前周的开始和结束日期，或者是否从上周开始。

原文

I have the following query that runs in my Oracle database and I want to have the equivalent for a SQL Server 2008 database:

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 */

What the query does is get the start of the week and the end of the week for the last 4 weeks. The number of weeks is arbitrary and should be easily modified in the SQL Server query I want. It generates data like the following:

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
...

The part I'm currently stuck on translating is CONNECT BY LEVEL since it seems SQL Server 2008 doesn't have an equivalent. I would prefer to simply adjust a line like CONNECT BY LEVEL <= 4 and have the query generate more or fewer weeks (i.e., I don't want to have to adjust multiple UNION ALL statements).

Edit: here's what I have so far that gets the beginning and end of the current week:

   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

I don't mind if the query shows the current week start and end date or if it starts at the previous week.

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（5）

花开柳相依 2024-08-30 21:53:14

我修改了 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

这比我的 上一个答案，我认为，因为它不依赖于另一个具有特定行数的表。只需更改一位数字即可更改生成的周数：SELECT DATEADD(WEEK, -3, GETDATE()) ASstarting_date 中的 3。包括当前周，该数字表示应显示当前周之前的其他周数。

迭代过去几周至今，不包括当前周

更新：这是排除当前周的版本：

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

I modified OMG Ponies' answer because it looked like a good idea, it just had the wrong week_end value on each week and also showed future weeks instead of past weeks. I came up with the following:

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

It generates the past 4 weeks:

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

This is better than my previous answer, I think, because it does not rely on another table having a particular number of rows. The number of weeks generated can be changed by altering only one digit: the 3 in SELECT DATEADD(WEEK, -3, GETDATE()) AS starting_date. The current week is included, and that digit represents how many additional weeks prior to the current week should be shown.

Iterate over Past Weeks up to Now, Excluding Current Week

Update: and here's a version that excludes the current week:

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

Its results:

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

Iterate over Past Months up to Now

I later found a need for a monthly version of this query. Here is that modification:

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
回复 原文
二智少女猫性小仙女 2024-08-30 21:53:14

使用（但不要忘记 投票给 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

以前：

  1. 使用 SET DATEFIRST 命令：

    <前><代码>设置日期优先 7

  2. SQL Server 2005+ 相当于 Oracle 的 CONNECT BY LEVEL 是递归 CTE（ANSI 标准顺便说一句）。使用：

    日期为 AS (
   选择日期添加（DD， 
                  1 - DATEPART(DW, CONVERT(VARCHAR(10), GETDATE(), 111)), 
                  CONVERT(VARCHAR(10), GETDATE(), 111)) AS 日期
   联合所有
   选择 DATEADD(dd, 7, d.date)
     从日期 d
    其中 DATEADD(dd, 7, d.date) <= DATEADD(dd, 4*7, GETDATE()))
选择 t.date 作为 week_start，
       DATEADD(ss, -1, DATEADD(DAY, 7, t.date)) AS week_end
  从日期 t

参见此链接了解如何获取一周的第一天。要更改周数，请更改 DATEADD(dd, 4*7, GETDATE())，其中 4 表示您想要生成的周数。

Use (but don't forget to 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

Previously:

  1. Set the first day of the week to be Sunday with the SET DATEFIRST command:

    SET DATEFIRST 7

  2. The SQL Server 2005+ equivalent to Oracle's CONNECT BY LEVEL is the recursive CTE (ANSI standard btw). Use:

    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

See this link for explaining how to get the first day of the week. To alter the number of weeks, change the DATEADD(dd, 4*7, GETDATE()), where 4 represents the number of weeks you want generated.

回复 原文
久随 2024-08-30 21:53:14

您所需要的只是一个集合：

;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;

但是我会提醒您，如果您的数据是日期时间并且您打算将这些边界用于查询范围，则应该使用开放式范围，例如 >= 03/07 和 <= 03/07 和 < 03/14。这样您就不会错过 23:59:59 到午夜之间发生的任何行；尽管它们可能很罕见，但它们可能很重要。

All you need is a set:

;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;

However I will caution you that if your data is datetime and you are going to use these boundaries for query ranges, you should use an open-ended range, e.g. >= 03/07 and < 03/14. This way you don't miss out any rows that happened between 23:59:59 and midnight; as rare as they may be, they could be important.

回复 原文
拥有 2024-08-30 21:53:14

根据您的代码：

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

based on your code:

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
回复 原文
森林很绿却致人迷途 2024-08-30 21:53:14

这就是我的想法。它有点笨拙，因为它至少依赖于我的一个表，其行数>=我想要选择的周数。我可以稍微调整一下以包括本周。

   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
...

Here's what I came up with. It's slightly janky in that it relies on at least one of my tables having rows >= the number of weeks I want to select. I could adjust it slightly to include the current week.

   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

This gets the past 21 weeks, starting at last week. Here's sample data:

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
...
回复 原文
~没有更多了~

关于作者

你又不是我

暂无简介

0 文章
0 评论
23 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

内心激荡

文章 0 评论 0

JSmiles

文章 0 评论 0

赏烟花じ飞满天

文章 0 评论 0

左秋

文章 0 评论 0

迪街小绵羊

文章 0 评论 0

瞳孔里扚悲伤

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文