SQL CASE:WHEN 语句的顺序重要吗? [英] SQL CASE: Does the order of the WHEN statements matter?

问题描述

我正在使用 PHP 生成需要以自定义方式排序的 SQL 查询.我正在生成一个 CASE 块，其中包含许多分配数字排名的 WHEN 语句.包含的 WHEN 语句取决于我有多少信息可用于查询.例如，如果我有一部可用的电话，我将生成三个 WHEN 语句:

I am using PHP to generate a SQL query that needs to be ordered in a custom way. I am generating a CASE block with a number of WHEN statements that assign a number ranking. The WHEN statements that are included are contingent on how much information I have available for querying. For example, if I have a phone available, I will generate three WHEN statements:

WHEN phone = '[phone]' AND last_name = 'Smith' and first_name = 'John' THEN 1

WHEN phone = '[phone]' AND last_name = 'Smith' THEN 2

WHEN phone = '[phone]' THEN 3

所以我根据匹配的接近程度进行排名，并且我对参数的评价相同(电话、名字和姓氏的匹配应该与地址、名字的匹配在同一层级和姓氏).但是，在为多条信息(电话、地址等)生成这些语句后，我的WHEN 语句将全部乱序；THEN 1 终止的子句将彼此分开，THEN 2 也是如此，依此类推.我可以把它们按顺序排列，但这会使我的 PHP 更加冗长和丑陋.

So I am ranking based on how close the match is, and I am valuing the parameters the same (a match on phone, first name, and last name should be ranked on the same tier as a match on address, first name and last name). However, after generating these statements for multiple pieces of information (phone, address, etc), my WHEN statements will be all out of order; the THEN 1 terminated clauses will be separated from each other, same for THEN 2 and so on. I could put them in order, but that would make my PHP more verbose and ugly.

TL;DR?

所以简短的问题是:CASE 语句中 WHEN 语句的顺序重要吗?SQL(我使用的是 Oracle)是第一个匹配项还是它评估所有可能性并分配最高排名?

So the short question is: Does the order of the WHEN statements in a CASE statement matter? Does SQL (I'm using Oracle) go with the first match or does it evaluate all of the possibilities and assign the highest ranking?

推荐答案

是的，case 语句的顺序确实很重要.第一个匹配的行将是返回的行.

Yes, the order of the case statements does matter. The first matching row will be the one returned.

所以，这个声明:

(CASE WHEN phone = '[phone]' AND last_name = 'Smith' and first_name = 'John' THEN 1
      WHEN phone = '[phone]' AND last_name = 'Smith' THEN 2
      WHEN phone = '[phone]' THEN 3
 END)

可以返回 1、2 或 3(或 NULL).以下将始终返回 3(或 NULL)，因为永远不会处理最后两个条件:

Could return 1, 2, or 3 (or NULL). The following will always return 3 (or NULL) because the last two conditions will never be processed:

(CASE WHEN phone = '[phone]' THEN 3
      WHEN phone = '[phone]' AND last_name = 'Smith' THEN 2
      WHEN phone = '[phone]' AND last_name = 'Smith' and first_name = 'John' THEN 1
 END)

这篇关于SQL CASE:WHEN 语句的顺序重要吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！