选择具有混合值的N行 [英] Select N Rows With Mixed Values
问题描述
我有一个表,其中包含列
I have a table with columns like
insertTimeStamp, port, data
1 , 20 , 'aaa'
2 , 20 , 'aba'
3 , 20 , '3aa'
4 , 20 , 'aab'
2 , 21 , 'aza'
5 , 21 , 'aha'
8 , 21 , 'aaa'
15 , 22 , '2aa'
现在我需要该表中的 N 行(说4),由 insertTimeStamp
排序。
但是,如果可能的话,我想从其他端口
s获得它们。
Now I need N Rows (Say 4) from that table, ordered asc by insertTimeStamp
.
But if possible, I want to get them from different port
s.
所以结果应该是:
1 , 20 , 'aaa'
2 , 20 , 'aba'
2 , 21 , 'aza'
15 , 22 , '2aa'
如果数量不足 port
中的其他值,我想选择 insertTimeStamp
最低的其余值。
If there are not enough different values in port
I would like select the remaining ones with the lowest insertTimeStamp
.
推荐答案
SQL Fiddle Demo
如您所见,我创建了 group_id
,因此 group_id = 1
将是每个端口中较小的 TimeStamp
As you can see I create a group_id
so group_id = 1
will be the smaller TimeStamp
for each port
第二个字段是 time_id
,因此在选择所有 1 $ c $之后,在
ORDER BY
中c>将所有 2,3,4
带到任何端口。
The second field is time_id
so in the ORDER BY
after I select all the 1
bring all the 2,3,4
for any port.
SELECT *
FROM (
SELECT *,
row_number() over (partition by "port" order by "insertTimeStamp") group_id,
row_number() over (order by "insertTimeStamp") time_id
FROM Table1 T
) as T
ORDER BY CASE
WHEN group_id = 1 THEN group_id
ELSE time_id
END
LIMIT 4
输出
| insertTimeStamp | port | data | group_id | time_id |
|-----------------|------|------|----------|---------|
| 1 | 20 | aaa | 1 | 1 |
| 2 | 21 | aza | 1 | 3 |
| 15 | 22 | 2aa | 1 | 8 |
| 2 | 20 | aba | 2 | 2 |
这篇关于选择具有混合值的N行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!