SQL：仅比较日期与月份和年份列

发布于 2024-11-14 06:52:50 字数 660 浏览 3 评论 0原文

我有一个表 MonthlyShipments，如下所示：

partnumber | quantity | month | year |
part1      |       12 |     6 | 2011 |
part1      |       22 |     5 | 2011 |
part1      |       32 |     4 | 2011 |
part1      |       42 |     3 | 2011 |
part1      |       52 |     2 | 2011 |

我想对过去 3 个月的数量进行求和，不包括当月。我的 where 子句目前看起来像：

where
  MonthlyShipments.Month <> MONTH(GETDATE()) AND 
  CAST(
    (CAST(MonthlyShipments.Month as nvarchar(2)) + 
    '-01-' + 
    CAST(MonthlyShipments.Year as nvarchar(4))) as DateTime)
  > DATEADD(m, -4, GETDATE())

它有效，但它丑陋且具有侮辱性。有什么建议可以让它变得更漂亮吗？非常感谢！

原文

I have a table MonthlyShipments that looks like:

partnumber | quantity | month | year |
part1      |       12 |     6 | 2011 |
part1      |       22 |     5 | 2011 |
part1      |       32 |     4 | 2011 |
part1      |       42 |     3 | 2011 |
part1      |       52 |     2 | 2011 |

I want to sum the quantities of the past 3 months, excluding the current month. My where clause currently looks like:

where
  MonthlyShipments.Month <> MONTH(GETDATE()) AND 
  CAST(
    (CAST(MonthlyShipments.Month as nvarchar(2)) + 
    '-01-' + 
    CAST(MonthlyShipments.Year as nvarchar(4))) as DateTime)
  > DATEADD(m, -4, GETDATE())

It works, but its ugly and insulting. Any advice on making it prettier? Much appreciate!

分享到QQ

分享到微博

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

发布评论

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

极致的悲 2024-11-21 06:52:50

好不了多少...

DATEDIFF(
    month,
    DATEADD(Year, MonthlyShipments.Year-1900,
        DATEADD(Month, MonthlyShipments.Month-1, 0)
           ),
    GETDATE()
    ) BETWEEN 1 AND 3

但是嵌套的 DATEADD 可以成为一个计算和索引列，

ALTER TABLE MonthlyShipments ADD
    ShipDate AS DATEADD(Year, MonthlyShipments.Year-1900,
            DATEADD(Month, MonthlyShipments.Month-1, 0)
               )

它给出

WHERE DATEDIFF(month, ShipDate, GETDATE()) BETWEEN 1 AND 3

Not much better...

DATEDIFF(
    month,
    DATEADD(Year, MonthlyShipments.Year-1900,
        DATEADD(Month, MonthlyShipments.Month-1, 0)
           ),
    GETDATE()
    ) BETWEEN 1 AND 3

however the nested DATEADD can be made a computed and indexed column

ALTER TABLE MonthlyShipments ADD
    ShipDate AS DATEADD(Year, MonthlyShipments.Year-1900,
            DATEADD(Month, MonthlyShipments.Month-1, 0)
               )

which gives

WHERE DATEDIFF(month, ShipDate, GETDATE()) BETWEEN 1 AND 3

回复收藏 0 原文

和我恋爱吧 2024-11-21 06:52:50

可以添加日期栏吗？如果您需要进行日期计算并且您不希望事情变得丑陋，这可能是一个要求。即使计算列也可以工作......

然后你就可以做一些事情像这样：

WHERE datecolumn < DATEADD(month, -3, GETDATE())

Can you add a date column? If you need to do date calculations and you don't want things to get ugly this is probably going to be a requirement. Even a computed column would work...

Then you can just do something like this:

WHERE datecolumn < DATEADD(month, -3, GETDATE())

回复收藏 0 原文

素手挽清风 2024-11-21 06:52:50

您可以将月份+日期转换为日期，然后将其与当前日期进行比较：

WHERE DATEDIFF(m,CONVERT(DATE, [month]+'/01/'+[year],101), GETDATE()) 
BETWEEN 1 AND 3

或者正如其他人所说，您可以创建一个包含日期类型的新计算列

You can convert your month+date to date and then compare it with current date:

WHERE DATEDIFF(m,CONVERT(DATE, [month]+'/01/'+[year],101), GETDATE()) 
BETWEEN 1 AND 3

Or as it was said by others you can create a new computed column that holds date type

回复收藏 0 原文

小梨窩很甜 2024-11-21 06:52:50

如果你有一个 (year,month) 索引，我认为这会利用它：

FROM MonthlyShipments ms
WHERE ( ms.year = YEAR(GetDate()) 
        AND ms.month BETWEEN MONTH(GetDate())-3
                         AND MONTH(GetDate())-1
      )
   OR ( ms.year = YEAR(GetDate())-1
        AND ms.month BETWEEN 12 + MONTH(GetDate())-3
                         AND 12 + MONTH(GetDate())-1
      )

不过看起来不太漂亮。

If you have a (year,month) index, this will make use of it, I think:

FROM MonthlyShipments ms
WHERE ( ms.year = YEAR(GetDate()) 
        AND ms.month BETWEEN MONTH(GetDate())-3
                         AND MONTH(GetDate())-1
      )
   OR ( ms.year = YEAR(GetDate())-1
        AND ms.month BETWEEN 12 + MONTH(GetDate())-3
                         AND 12 + MONTH(GetDate())-1
      )

Not looking much prettier though.

回复收藏 0 原文

~没有更多了~