处理表中行的最有效方法是什么？

I am teaching myself basic and intermediate SQL concepts for a project I am working on.

我正在为自己正在研究的项目自学基础和中级SQL概念。

I have a lot of data that needs to undergo processing so it can be presented in different ways. Right now I am using scalar functions calls in my select statement to process the data.

我有很多需要进行处理的数据,所以可以用不同的方式呈现。现在我在select语句中使用标量函数调用来处理数据。

A simple example, lets say I have an attribute in my table called fun as data type int. I want to process my table so that all rows with fun < 10 are 'foo' and all rows with fun > 10 are 'faa'.

一个简单的例子,假设我的表中有一个名为fun的属性作为数据类型int。我想要处理我的表,以便所有有趣的行<10都是'foo'而所有有趣的行> 10都是'faa'。

So I write an SQL function something like

所以我写了一个类似的SQL函数

CREATE FUNCTION dbo.fooORfaa
(
    @fun AS int
)
RETURNS VARCHAR(3)
AS
BEGIN
     IF (@fun < 10)
         RETURN 'foo'
     RETURN 'faa' 
END

Then I use my function in something like this select statement

然后我在这个select语句中使用我的函数

select dbo.fooORfaa([mytable].[fun]) AS 'blah'
from mytable

This example is trivial, but in my real code I need perform some fairly involved logic against one or more columns, and I am selecting sub results from procedures and joining tables together and other things you need to do in a data base.

这个例子很简单,但在我的实际代码中,我需要针对一个或多个列执行一些相当复杂的逻辑,并且我从过程中选择子结果并将表连接在一起以及在数据库中需要做的其他事情。

I have to process lots of records in a reasonable time span. Is this method an efficient way to tackle this problem? Is there another technique I should be using instead?

我必须在合理的时间跨度内处理大量记录。这种方法是解决这个问题的有效方法吗?我应该使用另一种技术吗?

1 个解决方案

#1

For this use case, you need a CASE construct.

对于此用例,您需要CASE构造。

SELECT
  CASE
    WHEN T.fun < 10 THEN 'foo'
    ELSE 'faa'
  END foo_faa
FROM
  myTable T

Always try to use set-based operations. User-defined functions will (mostly) kill your performance, and should be a last resort.

始终尝试使用基于集合的操作。用户定义的函数将(大部分)杀死你的性能,应该是最后的手段。

See: CASE (Transact-SQL)

请参阅:CASE(Transact-SQL)

#1