php - 是否有通过匹配多个条件来排序的 SQL 技术？

Question

我有几个表，它们连接在一起形成一个包含列的表

designID
garmentID
colorID
sizeID
imageID

我有一个看起来像这样的函数[方括号中的变量是可选的]:

getProductImages($designID, [$garmentID], [$colorID], [$sizeID]);

我希望它按以下顺序返回所有匹配 $designID 的图像 ID:

• 首先匹配 $garmentID、$colorID 和 $sizeID 的行
• 接下来匹配 $garmentID 和 $colorID 的行
• 下一个仅匹配 $garmentID 的行
• 最后没有匹配的行(仅 $designID)

我可以很容易地做到这一点，只需加载所有与 $designID 匹配的行，然后在 PHP 中对它们进行排序，但我的理解是，如果可能，在 MySQL 中进行排序通常会更快。将有大约 20 行匹配给定的 $designID。

所以我的问题是双重的:是否值得在 SQL 语句中进行排序？如果我这样做，最好的方法是什么？

我也很想知道是否有这种排序的名称。

Answer 1

如果我没理解错的话，您似乎可以在 ORDER BY 中使用表达式，其方式类似于对以下 Stack Overflow 帖子给出的公认答案:

• Using MySql, can I sort a column but have 0 come last?

因此，您的查询可能如下所示:

SELECT    imageID
FROM      ...
JOIN      ...
WHERE     designID = 100          
ORDER BY  garmentID = 1 DESC,
          colorID = 5 DESC,
          sizeID = 10 DESC;

请注意，garmentID、colorID 和 sizeID 未用作 WHERE 子句中的过滤器。这些值仅用于 ORDER BY 表达式。

测试用例:

CREATE TABLE designs (designID int, garmentID int, colorID int, sizeID int);

INSERT INTO designs VALUES (100, 1, 1, 1);
INSERT INTO designs VALUES (100, 1, 2, 2);
INSERT INTO designs VALUES (100, 1, 5, 3);
INSERT INTO designs VALUES (100, 1, 5, 10);
INSERT INTO designs VALUES (100, 1, 5, 15);
INSERT INTO designs VALUES (100, 1, 8, 20);
INSERT INTO designs VALUES (100, 2, 5, 10);
INSERT INTO designs VALUES (100, 2, 6, 15);
INSERT INTO designs VALUES (101, 1, 1, 1);
INSERT INTO designs VALUES (101, 2, 1, 1);

结果:

SELECT    * 
FROM      designs 
WHERE     designID = 100 
ORDER BY  garmentID = 1 DESC, 
          colorID = 5 DESC, 
          sizeID = 10 DESC;

+----------+-----------+---------+--------+
| designID | garmentID | colorID | sizeID |
+----------+-----------+---------+--------+
|      100 |         1 |       5 |     10 |
|      100 |         1 |       5 |      3 |
|      100 |         1 |       5 |     15 |
|      100 |         1 |       1 |      1 |
|      100 |         1 |       2 |      2 |
|      100 |         1 |       8 |     20 |
|      100 |         2 |       5 |     10 |
|      100 |         2 |       6 |     15 |
+----------+-----------+---------+--------+
8 rows in set (0.02 sec)

请注意与指定的 garmentID、colorID 和 sizeID 匹配的行是如何排在第一位的。否则，匹配 garmentID 和 colorID 的行是下一个。然后是仅匹配 garmentID 的行。然后剩下的，只匹配 WHERE 子句的 designID 过滤器。

我相信在 SQL 中这样做是值得的。作为@Toby noted in the other answer ，一般来说，在对如此少的行进行排序时，您不必担心性能，假设您将始终按 designID 进行过滤...至于您的其他问题，我不知道如果有这样一个查询的名称 - 我倾向于称它为“按表达式排序”。