SQL Case 语句与编码的 if 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2429226/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Case Statements versus coded if statements
提问by Eric
What is more efficient - handling with case statements in sql or handling the same data using if statements in code. I'm asking because my colleague has a huge query that has many case statements. I advised her to take stress off of the DB by coding the case statements. I've found that it is more efficient...but why?
什么更有效 - 在 sql 中处理 case 语句或在代码中使用 if 语句处理相同的数据。我问是因为我的同事有一个包含许多 case 语句的巨大查询。我建议她通过编写 case 语句来减轻 DB 的压力。我发现它更有效......但为什么呢？
回答by Aaronaught
There's a more fundamental question that's not being asked here: What are these CASEstatements actually doing?
这里没有提出一个更基本的问题：这些CASE陈述实际上在做什么？
Forget performance for a minute. If CASEis only being used to transform the final output of a query, and it's actually possible to replace the same functionality with an ifor select casein ASP, then it probably means that the database query/procedure is trying to do things that the UI should be responsible for, such as formatting. The separation-of-concerns issue is more serious than any possible performance issue.
暂时忘记性能。如果CASE仅用于转换查询的最终输出，并且实际上可以用if或select case在 ASP 中替换相同的功能，那么这可能意味着数据库查询/过程正在尝试执行 UI 应该负责的事情用于，例如格式化。关注点分离问题比任何可能的性能问题都更严重。
If you have a query like this:
如果您有这样的查询：
SELECT InvoiceID, InvoiceDate,
CASE WHEN PaidStatus = 0 THEN 'Unpaid' ELSE 'Paid' END
FROM ...
This is just silly, because the UI, or whatever layer does the data-to-domain mapping, should know how to convert a status in the database to its corresponding description. It makes no sense to be including this logic in the query itself.
这很愚蠢，因为 UI 或任何进行数据到域映射的层都应该知道如何将数据库中的状态转换为其相应的描述。在查询本身中包含这个逻辑是没有意义的。
On the other hand, if the CASEconstruct is an essential part of the query, like:
另一方面，如果CASE构造是查询的重要组成部分，例如：
SELECT
SUM(CASE WHEN PaidStatus = 0 THEN Amount ELSE 0 END) AS TotalUnpaid,
SUM(CASE WHEN PaidStatus = 1 THEN Amount ELSE 0 END) AS TotalPaid
FROM ...
Don't even try to move this kind logic to the UI, because the database is muchbetter at it. And the CASEis semantically a part of the query ("compute total paid and unpaid amounts for x"), it's not taking over any UI function.
甚至不要尝试将这种逻辑移动到 UI，因为数据库在这方面要好得多。并且在CASE语义上是查询的一部分（“计算x 的已付和未付总金额”），它不会接管任何 UI 功能。
Worry first about where the logic actually belongs based on what it's intending to accomplish. Performance concerns should only enter into the discussion if you are actually noticing significant performance problems.
首先根据它打算完成的事情担心逻辑实际上属于哪里。只有当您确实注意到重大的性能问题时，才应讨论性能问题。
回答by OMG Ponies
CASEstatements are preferred because:
CASE陈述是首选，因为：
- SQL: They are ANSI standard, making it portable to other databases without need for alteration
- they support "short circuiting"
- SQL：它们是ANSI标准，无需改动即可移植到其他数据库
- 他们支持“短路”
回答by northpole
In my experience our database servers are much MUCH larger than our application servers and are usually sitting below 30% idle. Have the database manage the data, then have the client iterate through the resultSet. It is better practice to have the database only return the data you need (if you can determine this up front).
根据我的经验，我们的数据库服务器比我们的应用程序服务器大得多，并且通常闲置率低于 30%。让数据库管理数据，然后让客户端遍历结果集。更好的做法是让数据库只返回您需要的数据（如果您可以预先确定）。
回答by Keith Adler
You should query (filter and sort) the data in the database, and leave presentation to the presentation tier. This is for two key reasons:
您应该查询（过滤和排序）数据库中的数据，并将展示留给展示层。这是出于两个关键原因：
- Databases are made to filter and sort data
- You want to pull the least amount of data over the wire from the DB as necessary
- 数据库用于过滤和排序数据
- 您希望根据需要从数据库中通过线路提取最少的数据
回答by Ram Dwivedi
As I read, here the basic ask is if CASE is better than IF in SQL. Answers also depends on the depth of your conditions. Found a good article here. May be useful to someone. http://www.4guysfromrolla.com/webtech/102704-1.shtml
正如我所读到的，这里的基本问题是 CASE 是否比 SQL 中的 IF 更好。答案还取决于您的条件的深度。在这里找到了一篇好文章。可能对某人有用。 http://www.4guysfromrolla.com/webtech/102704-1.shtml
