如果我理解正确,看起来您可以在ORDER BY中使用表达式,其方式类似于给出以下Stack Overflow帖子的接受答案:
因此,您的查询可能如下所示:
SELECT imageID
FROM ...
JOIN ...
WHERE designID = 100
ORDER BY garmentID = 1 DESC,colorID = 5 DESC,sizeID = 10 DESC;
请注意,在WHERE子句中,衣服ID,colorID和sizeID不用作过滤器.这些值仅用于ORDER BY表达式.
测试用例:
CREATE TABLE designs (designID int,garmentID int,colorID int,sizeID int);
INSERT INTO designs VALUES (100,1,1);
INSERT INTO designs VALUES (100,2,2);
INSERT INTO designs VALUES (100,5,3);
INSERT INTO designs VALUES (100,10);
INSERT INTO designs VALUES (100,15);
INSERT INTO designs VALUES (100,8,20);
INSERT INTO designs VALUES (100,6,15);
INSERT INTO designs VALUES (101,1);
INSERT INTO designs VALUES (101,1);
结果:
SELECT *
FROM designs
WHERE designID = 100
ORDER BY garmentID = 1 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)
请注意与指定的衣服ID,colorID和sizeID匹配的行是如何排在第一位的.如果做不到这一点,接下来是匹配garmentID和colorID的行.然后是仅匹配garmentID的行.然后是其余的,它只匹配WHERE子句的designID过滤器.
我相信在sql中这样做是值得的.作为@Toby noted in the other answer,一般来说,排序这么少的行时你不需要担心性能,假设你总是按照designID进行过滤…至于你的其他问题,我不知道是否有名字对于这样的查询 – 我倾向于称之为“通过表达式排序”.