SQL 运算符作为 where 子句中的文本

发布于 2024-11-17 05:46:10 字数 683 浏览 2 评论 0原文

我有下表,用于存储条带。该表通过网络前端维护。

CREATE TABLE [dbo].[Banding](
[BandingID] [int] IDENTITY(1,1) NOT NULL,
[ValueLowerLimitOperator] [varchar](10) NULL,
[ValueLowerLimit] [decimal](9, 2) NULL,
[ValueUpperLimitOperator] [varchar](10) NULL,
[ValueUpperLimit] [decimal](9, 2) NULL,
[VolumeLowerLimitOperator] [varchar](10) NULL

运算符字段存储诸如>之类的值。 < >= <=。我想要到达一个可以在 where 子句的 case 语句中使用存储在表中的运算符值的位置。像这样。

SELECT * FROM table WHERE CASE ValueLowerLimitOperator
WHEN '<' THEN VALUE < X
WHEN '>' THEN VALUE > X END

而不必为每个排列编写多个 case 或 if 语句。

有谁对如何解码存储在表中的运算符值作为查询的一部分然后在 case/where 语句中使用它们有任何建议吗?

I have the following table, which is used for storing bandings. The table is maintained via a web frontend.

CREATE TABLE [dbo].[Banding](
[BandingID] [int] IDENTITY(1,1) NOT NULL,
[ValueLowerLimitOperator] [varchar](10) NULL,
[ValueLowerLimit] [decimal](9, 2) NULL,
[ValueUpperLimitOperator] [varchar](10) NULL,
[ValueUpperLimit] [decimal](9, 2) NULL,
[VolumeLowerLimitOperator] [varchar](10) NULL

The operator fields store values such as > < >= <=. I want to get to a position where I can use the operators values stored in the table in a case statement in a where clause. Like this.

SELECT * FROM table WHERE CASE ValueLowerLimitOperator
WHEN '<' THEN VALUE < X
WHEN '>' THEN VALUE > X END

rather than having to write mutiple case or if statements for each permutation.

Does anyone have any suggestions how I can decode the operators values stored in the table as part of my query and then use them in a case/where statement?

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

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

发布评论

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

评论(3

七禾 2024-11-24 05:46:10

有多种技术,但有一些事情需要注意;尝试将两种类型的功能压缩到单个查询中通常会导致执行计划不佳。 (例如,表或索引扫描,而不是索引查找。)

我经常发现最好重新考虑数据的设计,和/或重新考虑如何形成查询。

但是,回答您问题中的具体示例,我会这样做...

WHERE
  CASE WHEN VALUE < X THEN 1
       WHEN VALUE = X THEN 2
       WHEN VALUE > X THEN 4
  END
  |
  CASE ValueLowerLimitOperator
       WHEN '<'       THEN 1
       WHEN '<='      THEN 3
       WHEN '='       THEN 2
       WHEN '>='      THEN 6
       WHEN '>'       THEN 4
  END
  <>
  0

但是,这当然不会利用任何索引。

另一种选择是只对每个场景进行编码...

WHERE
  CASE ValueLowerLimitOperator
       WHEN '<'       THEN CASE WHEN VALUE <  X THEN 1 ELSE 0 END
       WHEN '<='      THEN CASE WHEN VALUE <= X THEN 1 ELSE 0 END
       WHEN '='       THEN CASE WHEN VALUE =  X THEN 1 ELSE 0 END
       WHEN '>='      THEN CASE WHEN VALUE >= X THEN 1 ELSE 0 END
       WHEN '>'       THEN CASE WHEN VALUE >  X THEN 1 ELSE 0 END
  END
  =
  1

或者可能是一堆联合查询

WITH main_query AS (SELECT * FROM blah)
SELECT * FROM main_query WHERE ValueLowerLimitOperator = '<'  AND VALUE <  X
UNION ALL
SELECT * FROM main_query WHERE ValueLowerLimitOperator = '<=' AND VALUE <= X
UNION ALL
SELECT * FROM main_query WHERE ValueLowerLimitOperator = '='  AND VALUE =  X
UNION ALL
SELECT * FROM main_query WHERE ValueLowerLimitOperator = '>=' AND VALUE >= X
UNION ALL
SELECT * FROM main_query WHERE ValueLowerLimitOperator = '>'  AND VALUE >  X

甚至可能使用一个函数来解码所有内容?

SELECT
  *
FROM
  main_query
CROSS APPLY
  dbo.my_function(ValueLowerLimitOperator, VALUE, X) AS check
WHERE
  check.return_value = 1

(Using CROSS APPLY with a Inline Table Valued Function will be MUCH faster than a Scalar Function in the WHERE clause)

当您看起来希望对 LowerLimit、UpperLimit 等重复此操作时,将其封装在函数中对我来说是个好主意。

There are several techniques, but there is something to be aware of; trying to compress two types of functionality into a single query often results in poor execution plans. (Table or Index scans, rather than Index Seeks, for example.)

I've often found it better to rethink the design of the data, and/or rethink how to form the query.

Answering the Specific example in you question, however, I'd do this...

WHERE
  CASE WHEN VALUE < X THEN 1
       WHEN VALUE = X THEN 2
       WHEN VALUE > X THEN 4
  END
  |
  CASE ValueLowerLimitOperator
       WHEN '<'       THEN 1
       WHEN '<='      THEN 3
       WHEN '='       THEN 2
       WHEN '>='      THEN 6
       WHEN '>'       THEN 4
  END
  <>
  0

This certainly won't be taking advantage of any indexes, however.

Another option is just to code every scenario...

WHERE
  CASE ValueLowerLimitOperator
       WHEN '<'       THEN CASE WHEN VALUE <  X THEN 1 ELSE 0 END
       WHEN '<='      THEN CASE WHEN VALUE <= X THEN 1 ELSE 0 END
       WHEN '='       THEN CASE WHEN VALUE =  X THEN 1 ELSE 0 END
       WHEN '>='      THEN CASE WHEN VALUE >= X THEN 1 ELSE 0 END
       WHEN '>'       THEN CASE WHEN VALUE >  X THEN 1 ELSE 0 END
  END
  =
  1

Or possibly a bunch of unioned queries

WITH main_query AS (SELECT * FROM blah)
SELECT * FROM main_query WHERE ValueLowerLimitOperator = '<'  AND VALUE <  X
UNION ALL
SELECT * FROM main_query WHERE ValueLowerLimitOperator = '<=' AND VALUE <= X
UNION ALL
SELECT * FROM main_query WHERE ValueLowerLimitOperator = '='  AND VALUE =  X
UNION ALL
SELECT * FROM main_query WHERE ValueLowerLimitOperator = '>=' AND VALUE >= X
UNION ALL
SELECT * FROM main_query WHERE ValueLowerLimitOperator = '>'  AND VALUE >  X

Maybe even use a function to decode it all?

SELECT
  *
FROM
  main_query
CROSS APPLY
  dbo.my_function(ValueLowerLimitOperator, VALUE, X) AS check
WHERE
  check.return_value = 1

(Using CROSS APPLY with a Inline Table Valued Function will be MUCH faster than a Scalar Function in the WHERE clause)

As you look like you want this repeated for LowerLimit, UpperLimit, etc, encapsulating this in a function feels like a good idea to me.

不甘平庸 2024-11-24 05:46:10

您需要动态sql。虽然没有更多信息,但我无法为您提供比链接中更多的示例

You'll need dynamic sql. Without more information though I can't give you more of an example than in the link

一百个冬季 2024-11-24 05:46:10

为什么不输入值:

1代表>,-1代表<,2代表>=,-2代表<=,0代表=,

的结果

value * ValueLowerLimitOperator  -x*(ValueLowerLimitOperator)

然后尝试检查你可以这样选择

select *
from table
where 1=
case 
when (value * ValueLowerLimitOperator  -x*ValueLowerLimitOperator >=0 and abs(ValueLowerLimitOperator)>1)      
    --that means it is <= or >= and the condition is true.
or (value * ValueLowerLimitOperator -x*ValueLowerLimitOperator >0 and abs(ValueLowerLimitOperator)=1)
   --that means it is < or >  and the condition is true.
or (value =x and ValueLowerLimitOperator=0)
   --that means it is = and the condition is true.
then 1
end

:这会做它。

Why not put the values:

1 for >, -1 for <, 2 for >=, -2 for <=, 0 for =

and then try to check the result of

value * ValueLowerLimitOperator  -x*(ValueLowerLimitOperator)

you can select like this:

select *
from table
where 1=
case 
when (value * ValueLowerLimitOperator  -x*ValueLowerLimitOperator >=0 and abs(ValueLowerLimitOperator)>1)      
    --that means it is <= or >= and the condition is true.
or (value * ValueLowerLimitOperator -x*ValueLowerLimitOperator >0 and abs(ValueLowerLimitOperator)=1)
   --that means it is < or >  and the condition is true.
or (value =x and ValueLowerLimitOperator=0)
   --that means it is = and the condition is true.
then 1
end

This will do it.

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