在 T-SQL 的 where 子句中使用 CASE？

发布于 2024-08-17 04:56:09 字数 357 浏览 6 评论 0原文

我尝试使用 case 来改变在 where 子句中检查的值，但出现错误：

关键字 'CASE'

SQL Server 2005附近的语法不正确

select * 
from   table
where  ((CASE when adsl_order_id like '95037%'
         then select '000000'+substring(adsl_order_id,6,6)
         ELSE select adsl_order_id
       END)
       not in (select mwebID from tmp_csv_dawis_bruger0105)

原文

Im trying to use case to vary the value im checking in a where clause but I'm getting the error:

incorrect syntax near the keyword 'CASE'

SQL Server 2005

select * 
from   table
where  ((CASE when adsl_order_id like '95037%'
         then select '000000'+substring(adsl_order_id,6,6)
         ELSE select adsl_order_id
       END)
       not in (select mwebID from tmp_csv_dawis_bruger0105)

分享到QQ

分享到微博

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

发布评论

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

晚风撩人 2024-08-24 04:56:09

以下是将 case 语句包含在 Where 子句中的一种方法：

SELECT * FROM sometable
WHERE 1 = CASE WHEN somecondition THEN 1 
    WHEN someothercondition THEN 2
    ELSE ... END

Here is one way to include a case statement in a Where clause:

SELECT * FROM sometable
WHERE 1 = CASE WHEN somecondition THEN 1 
    WHEN someothercondition THEN 2
    ELSE ... END

回复收藏 0 原文

计㈡愣 2024-08-24 04:56:09

你可以尝试

SELECT *
FROM table
WHERE (SELECT CASE WHEN adsl_order_id LIKE '95037%'
              THEN '000000' + SUBSTRING(adsl_order_id, 6, 6)
              ELSE adsl_order_id
              END)
      NOT IN (select mwebID from tmp_csv_dawis_bruger0105)

You could try

SELECT *
FROM table
WHERE (SELECT CASE WHEN adsl_order_id LIKE '95037%'
              THEN '000000' + SUBSTRING(adsl_order_id, 6, 6)
              ELSE adsl_order_id
              END)
      NOT IN (select mwebID from tmp_csv_dawis_bruger0105)

回复收藏 0 原文

虫児飞 2024-08-24 04:56:09

相关子查询是一种可能性：

select * 
from mytable
where not exists (
    select * 
    from 
        tmp_csv_dawis_bruger0105
    where 
        mwebID = 
        CASE when mytable.adsl_order_id like '95037%' then '000000' + substring(mytable.adsl_order_id,6,6)
        ELSE mytable.adsl_order_id END
 )

A correlated subquery is one possibility:

select * 
from mytable
where not exists (
    select * 
    from 
        tmp_csv_dawis_bruger0105
    where 
        mwebID = 
        CASE when mytable.adsl_order_id like '95037%' then '000000' + substring(mytable.adsl_order_id,6,6)
        ELSE mytable.adsl_order_id END
 )

回复收藏 0 原文

找回味觉 2024-08-24 04:56:09

我来到这个问题寻找答案，思考 WHERE CASE ... 将是解决方案。我无法调整我的问题的答案，但这种使用可能为空、部分或特定的参数的技术是有效的：

CREATE PROC myproc  @vJobID VARCHAR (11) 
AS
SELECT * FROM Jobs 

WHERE Jobs.JobID like coalesce(@vJobID+'%','%')

HTH

I came to this question looking for an answer thinking WHERE CASE ... would be the solution. I could not adapt the answers to my problem, but this technique to use a parameter that could be null, partial, or specific, works:

CREATE PROC myproc  @vJobID VARCHAR (11) 
AS
SELECT * FROM Jobs 

WHERE Jobs.JobID like coalesce(@vJobID+'%','%')

HTH

回复收藏 0 原文

鯉魚旗 2024-08-24 04:56:09

CASE 表达式前的左括号过多。

回复收藏 0 原文

意中人 2024-08-24 04:56:09

将其放在 SELECT 子句中...

select *, (CASE when adsl_order_id like '95037%'
         then '000000'+substring(adsl_order_id,6,6)
         ELSE adsl_order_id
       END) AS Id
from   table
where  not in (select mwebID from tmp_csv_dawis_bruger0105)

此外，您不需要“SELECT”CASE 的结果。

Put it in the SELECT clause...

select *, (CASE when adsl_order_id like '95037%'
         then '000000'+substring(adsl_order_id,6,6)
         ELSE adsl_order_id
       END) AS Id
from   table
where  not in (select mwebID from tmp_csv_dawis_bruger0105)

Also, you don't need to "SELECT" the result of the CASE.

回复收藏 0 原文

~没有更多了~