为什么 MS SQL 中的“Where 子句中没有数学函数"? [英] Why 'No math function In Where Clause' in MS SQL?

问题描述

大多数 SQL 查询对 where 子句没有数学运算.

Most of SQL queries have no math operation on where clause.

将它们放在where 子句"上有什么问题?是否有任何性能问题?

What is wrong having them on 'where clause'? Is there any performance issue?

例如:

SELECT * FROM Employee WHERE Salary*3 = 5000

SELECT * FROM Employee WHERE Salary*3 = 5000

推荐答案

如果 where 子句可以使用索引，它通常(但不总是)更快.对字段使用数学运算将停止使用索引.

If a where clause can utilise an index, it is often (but not always) faster. Using a math operation on a field will stop the index from being utilised.

例如，如果您有一个包含一百万行的表和一个已编入索引的日期列，那么这里的查询 1 将远远优于查询 2(它们都检索日期在过去 7 天内的所有行):

For example, if you had a table with a million rows, and a date column that was indexed, query 1 here would by far outperform query 2 (they both retrieve all rows where the date is in the last 7 days):

查询 1:

select date from table where date > dateadd(d, -7, getdate())

查询 2:

select date from table where dateadd(d, 7, date) > getdate()

<小时>

在您的示例中，查询会更好:

---

In your example, the query would be far better as:

select * from employee where salary = (5000 / 3)

这篇关于为什么 MS SQL 中的“Where 子句中没有数学函数"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！