是否存在通过匹配多个条件进行排序的SQL技术? [英] Is there a SQL technique for ordering by matching multiple criteria?
问题描述
我有几个表，它们被联接在一起以形成带有列的表
I have several tables that get JOINed together to form a table with columns
designID
garmentID
colorID
sizeID
imageID
我有一个看起来像这样的函数[方括号中的变量是可选的]:
I have a function that looks like this [variables in square brackets are optional]:
getProductImages($designID, [$garmentID], [$colorID], [$sizeID]);
我希望它按以下顺序返回与$ designID匹配的所有imageID:
I want it to return all imageIDs that match $designID in the following order:
- 首先匹配$ garmentID，$ colorID和$ sizeID的行
- 接下来匹配$ garmentID和$ colorID的行
- 接下来仅匹配$ garmentID的行
- 最后没有匹配的行(仅$ designID)
我可以很容易地做到这一点，只需加载与$ designID匹配的所有行，然后在PHP中对它们进行排序，但是我的理解是，在可能的情况下，在MySQL中进行排序通常更快.大约有20行与给定的$ designID匹配.
I could do this pretty easily by just loading all the rows that match $designID and then sorting them in PHP, but my understanding is that it's generally faster to do sorting in MySQL when possible. There will be about 20 rows matching a given $designID.
所以我的问题是双重的:是否值得在SQL语句中进行排序?如果这样做，最好的方法是什么?
So my question is twofold: Is it worth doing the sorting in a SQL statement? If I do, what is the best approach to take?
我也很想知道是否有这种排序的名称.
I would also be very interested to know if there is a name for this kind of sorting.
推荐答案
如果我正确理解，看来您可以在ORDER BY中使用表达式，其方式类似于以下Stack Overflow帖子所接受的答案:
If I understood correctly, it looks like you can use expressions in your ORDER BY, in a way similar to the accepted answer given to the following Stack Overflow post:
因此，您的查询可能如下所示:
Therefore, your query might look like this:
SELECT imageID
FROM ...
JOIN ...
WHERE designID = 100
ORDER BY garmentID = 1 DESC,
colorID = 5 DESC,
sizeID = 10 DESC;
请注意，garmentID，colorID和sizeID在WHERE子句中不用作过滤器.这些值仅在ORDER BY表达式中使用.
Note that garmentID, colorID, and sizeID are not used as filters in the WHERE clause. The values are only used in the ORDER BY expressions.
测试用例:
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过滤器匹配.
Note how the row that matches the specified garmentID, colorID and sizeID is first. Failing that, the rows that match garmentID and colorID are next. Then the rows that only match garmentID follow. Then the rest, which only match the designID filter of the WHERE clause.
我相信在SQL中值得这样做.如 @Toby所述其他答案，一般来说，在对这么少的行进行排序时，您无需担心性能，只要您始终使用designID进行过滤即可...至于其他问题，我不需要知道这种查询是否有名称-我倾向于称其为按表达式排序".
I believe it is worth doing this in SQL. As @Toby noted in the other answer, in general you don't need to worry about performance when sorting such a small number of rows, assuming you will always be filtering by designID... As for your other question, I don't know if there is a name for such a query - I tend to call it "ordering by an expression".
这篇关于是否存在通过匹配多个条件进行排序的SQL技术?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
