通过时区处理 SQL 连接

发布于 2025-01-09 15:42:43 字数 700 浏览 0 评论 0原文

我需要加入两个表。必须使用参数@timezone 来处理连接。但有一个问题 - @timezone 的值可以为 null、''、' ' 或其他任何值。用户可以输入所有内容。

如果@timezone的值为null，则连接的结果基本上什么也没有。因此，如果发生这种情况，至少必须有基本值才能加入（例如，“太平洋标准时间”）

DECLARE @StartDate DATE = '2022-01-20', @EndDate DATE = '2022-01-30', @t1 INT = 12345, @timezone VARCHAR(max) = NULL, @OnlyActivated INT = 0
SELECT r.ChargeType, r.CustomerName, r.PurchaseDate, r.accountID
FROM table_report as r
LEFT JOIN [sys].[time_zone_info] tzi ON tzi.[name] = @timezone (???)

我尝试了 IIF(@timezone IN (NULL,'',' '),'太平洋标准时间Time',@timezone) 它适用于 '' 和 ' ' 场景，但后来我意识到 null 无法进行比较。

也许有一个非常明显的解决方案，但我没有看到。

希望你能帮忙。

PS 不支持 CURRENT_TIMEZONE_ID()。

原文

I need to join two tables. The join has to be handled using parameter @timezone.
But there is a catch - @timezone's value can be null, '', ' ' or anything else. User can enter everything.

If @timezone's value is null the outcome of the join will be basically nothing.
So that, if it happens, there has to be at least basic value to join (for example, 'Pacific Standard Time')

DECLARE @StartDate DATE = '2022-01-20', @EndDate DATE = '2022-01-30', @t1 INT = 12345, @timezone VARCHAR(max) = NULL, @OnlyActivated INT = 0
SELECT r.ChargeType, r.CustomerName, r.PurchaseDate, r.accountID
FROM table_report as r
LEFT JOIN [sys].[time_zone_info] tzi ON tzi.[name] = @timezone (???)

I tried IIF(@timezone IN (NULL,'',' '),'Pacific Standard Time',@timezone) and it worked with '' and ' ' scenarios but then I realized that null can't be compared.

Maybe there is a pretty obvious solution but I don't see it.

Hope you can help with that.

P.S. CURRENT_TIMEZONE_ID() is not supported.

分享到QQ

分享到微博

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

发布评论

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

贪恋 2025-01-16 15:42:43

您需要左连接两次才能执行此操作。第二个连接需要有一个条件，即仅在第一个连接不成功时才执行。

DECLARE
    @StartDate DATE = '2022-01-20',
    @EndDate DATE = '2022-01-30',
    @t1 INT = 12345,
    @timezone VARCHAR(max) = NULL,
    @OnlyActivated INT = 0;

SELECT
  r.ChargeType, r.CustomerName, r.PurchaseDate, r.accountID,
  ISNULL(tzi1.SomeValue, tzi2.SomeValue)
FROM table_report as r
LEFT JOIN [sys].[time_zone_info] tzi1 ON tzi1.[name] = @timezone
LEFT JOIN [sys].[time_zone_info] tzi2 ON tzi2.[name] = 'Pacific Standard Time'
    AND tzi1.[name] IS NOT NULL;

如果您只担心 NULL ，另一种选择是仅使用 ISNULL 变量

DECLARE
    @StartDate DATE = '2022-01-20',
    @EndDate DATE = '2022-01-30',
    @t1 INT = 12345,
    @timezone VARCHAR(max) = NULL,
    @OnlyActivated INT = 0;

SELECT
  r.ChargeType, r.CustomerName, r.PurchaseDate, r.accountID,
  tzi.SomeValue
FROM table_report as r
LEFT JOIN [sys].[time_zone_info] tz1 ON tzi.[name] = ISNULL(@timezone, 'Pacific Standard Time');

You need to left-join twice to do this. The second join needs to have a condition that it only executes if the first join does not succeed.

DECLARE
    @StartDate DATE = '2022-01-20',
    @EndDate DATE = '2022-01-30',
    @t1 INT = 12345,
    @timezone VARCHAR(max) = NULL,
    @OnlyActivated INT = 0;

SELECT
  r.ChargeType, r.CustomerName, r.PurchaseDate, r.accountID,
  ISNULL(tzi1.SomeValue, tzi2.SomeValue)
FROM table_report as r
LEFT JOIN [sys].[time_zone_info] tzi1 ON tzi1.[name] = @timezone
LEFT JOIN [sys].[time_zone_info] tzi2 ON tzi2.[name] = 'Pacific Standard Time'
    AND tzi1.[name] IS NOT NULL;

An alternative, if you are only worrying about NULL is to just ISNULL the variable

DECLARE
    @StartDate DATE = '2022-01-20',
    @EndDate DATE = '2022-01-30',
    @t1 INT = 12345,
    @timezone VARCHAR(max) = NULL,
    @OnlyActivated INT = 0;

SELECT
  r.ChargeType, r.CustomerName, r.PurchaseDate, r.accountID,
  tzi.SomeValue
FROM table_report as r
LEFT JOIN [sys].[time_zone_info] tz1 ON tzi.[name] = ISNULL(@timezone, 'Pacific Standard Time');

回复收藏 0 原文

~没有更多了~