SQLite排序结果的差异最小

Question

这个问题在很多方面都是我上一个问题的延续。我有一张几乎完全相同的表

CREATE TABLE IF NOT EXISTS test
(
 id INTEGER PRIMARY KEY,
 a INTEGER NOT NULL,
 b INTEGER NOT NULL,
 c INTEGER NOT NULL,
 d INTEGER NOT NULL,
 weather INTEGER NOT NULL);

其中我通常会有诸如之类的条目

INSERT INTO test (a,b,c,d,weather) VALUES(1,2,3,4,30100306);
INSERT INTO test (a,b,c,d,weather) VALUES(1,2,3,4,30140306);
INSERT INTO test (a,b,c,d) VALUES(1,2,5,5,10100306);    
INSERT INTO test (a,b,c,d) VALUES(1,5,5,5,11100306);
INSERT INTO test (a,b,c,d) VALUES(5,5,5,5,21101306);

通常，该表将有多行，其中b、c和d的一些/全部值相同，但a和weather值不同。根据我另一个问题的答案，我当然可以发布

WITH cte AS (SELECT *, DENSE_RANK() OVER (ORDER BY (b=2) + (c=3) + (d=4) DESC) rn FROM test where a = 1) SELECT * FROM cte WHERE rn < 3;

到目前为止没有任何问题。然而，我还有一个要求，这是由于天气专栏而产生的。虽然这个值是一个整数，但它实际上是一个复合值，其中每个数字代表一个"带状"天气条件。以weather = 20100306为例。这里，2表示指南针上划分为45度带的风向，0表示风速范围，1表示雪等形式的降水。在获得有序结果时，我现在需要做的是考虑天气差异。以前两行为例

INSERT INTO test (a,b,c,d,weather) VALUES(1,2,3,4,30100306);
INSERT INTO test (a,b,c,d,weather) VALUES(1,2,3,4,30140306);

尽管在其他方面相似，但它们代表了截然不同的天气条件——第四个数字是4，而不是0，表明降水强度更高。上面的WITH cte...将对顶部的前两行进行排序，这很好。但是，如果我更喜欢与即将到来的30130306"天气状况"相差最小的行，该怎么办？我显然希望第二排出现在最上面。再一次，我可以接受WITH cte...返回的"原始"结果，然后根据我在Java中的当前"天气状况"向下搜索到右侧一行。然而，我再一次发现自己在想，在SQL中可能有一种相当巧妙的方法来做到这一点，这超出了我的技能范围。我非常感谢任何能够告诉我如何/是否可以只使用SQL来完成这项工作的人。

Answer 1

您可以根据DENSE_RANK()对结果进行第一次排序，根据weather和传入的"天气条件"的绝对差对结果进行第二次排序：

WITH cte AS (
  SELECT *, 
    DENSE_RANK() OVER (ORDER BY (b=2) + (c=3) + (d=4) DESC) rn 
  FROM test 
  WHERE a = 1
) 
SELECT a,b,c,d,weather 
FROM cte 
WHERE rn < 3
ORDER BY rn, ABS(weather - ?);

将?替换为传入的"天气条件">的值。