在 SELECT 语句中设置变量

发布于 2024-10-16 18:21:33 字数 509 浏览 1 评论 0原文

不幸的是，我遇到了与此处所述相同的问题该解决方案不适用于 MS SQL Server。 MS SQL Server 是否存在类似的语法？

注意：我的查询并不像示例中那么简单。我想在 TooLateTime 的情况下重用 DifferenceMinutes 。

DATEDIFF(MINUTE, DayOfWeekStopTime, GETDATE()) AS DifferenceMinutes,
CASE 
    WHEN DATEDIFF(MINUTE, DayOfWeekStopTime, GETDATE()) < 0 THEN NULL 
    ELSE CONVERT(varchar, GETDATE() - DayOfWeekStopTime, 108) 
END AS TooLateTime

原文

I'm having the same problem as described here, unfortunately the solution does not work for MS SQL Server.
Does a similar syntax exist for MS SQL Server?

Note: my query is not as simple as in the example. I'd like to reuse DifferenceMinutes in the TooLateTime case.

DATEDIFF(MINUTE, DayOfWeekStopTime, GETDATE()) AS DifferenceMinutes,
CASE 
    WHEN DATEDIFF(MINUTE, DayOfWeekStopTime, GETDATE()) < 0 THEN NULL 
    ELSE CONVERT(varchar, GETDATE() - DayOfWeekStopTime, 108) 
END AS TooLateTime

分享到QQ

分享到微博

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

发布评论

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

左耳近心 2024-10-23 18:21:33

很难准确说出您想要做什么，但我认为这可能就是您正在寻找的：

SELECT
    DifferenceMinutes,
    CASE 
        WHEN DifferenceMinutes < 0 THEN NULL
        ELSE CONVERT(varchar, GETDATE() - DayOfWeekStopTime, 108) 
    END AS TooLateTime
FROM (
    SELECT
        DayOfWeekStopTime,
        DATEDIFF(MINUTE, DayOfWeekStopTime, GETDATE()) AS DifferenceMinutes
    FROM TableName
) X

您必须将源表替换为 FROM 部分中的“TableName”内部查询。

通过将计算值滚动到像这样的嵌套选择中，您可以通过在外部查询中给它们指定的任何名称来引用它们。

如果要为每个值设置变量，可以按如下方式执行此操作，但需要确保只从查询中返回一行：

DECLARE @DifferenceMinutes int, @TooLateTime varchar(30)
SELECT
    @DifferenceMinutes = DifferenceMinutes,
    @TooLateTime = CASE 
        WHEN DifferenceMinutes < 0 THEN NULL
        ELSE CONVERT(varchar, GETDATE() - DayOfWeekStopTime, 108) 
    END
FROM (
    SELECT
        DayOfWeekStopTime,
        DATEDIFF(MINUTE, DayOfWeekStopTime, GETDATE()) AS DifferenceMinutes
    FROM TableName
) X

It's a little hard to tell exactly what you're trying to do, but I think this might be what you're looking for:

SELECT
    DifferenceMinutes,
    CASE 
        WHEN DifferenceMinutes < 0 THEN NULL
        ELSE CONVERT(varchar, GETDATE() - DayOfWeekStopTime, 108) 
    END AS TooLateTime
FROM (
    SELECT
        DayOfWeekStopTime,
        DATEDIFF(MINUTE, DayOfWeekStopTime, GETDATE()) AS DifferenceMinutes
    FROM TableName
) X

You'll have to substitute your source table(s) for "TableName" in the FROM section of the inner query.

By rolling your calculated values into a nested select like this, you can refer to them by whatever name you give them in the outer query.

If you want to set variables for each of the values, you can do that as follows, but you'll need to make sure you're only returning one row from the query:

DECLARE @DifferenceMinutes int, @TooLateTime varchar(30)
SELECT
    @DifferenceMinutes = DifferenceMinutes,
    @TooLateTime = CASE 
        WHEN DifferenceMinutes < 0 THEN NULL
        ELSE CONVERT(varchar, GETDATE() - DayOfWeekStopTime, 108) 
    END
FROM (
    SELECT
        DayOfWeekStopTime,
        DATEDIFF(MINUTE, DayOfWeekStopTime, GETDATE()) AS DifferenceMinutes
    FROM TableName
) X

回复收藏 0 原文

~没有更多了~