有没有办法通过in列表中的值来限制查询结果

Question

我有以下数据库表：

myTable:
----------------------------------------
| customID | value |     timestamp     |
----------------------------------------
|   e01    | key01 |  11-11-2019 04:00 |
|   e02    | key02 |  11-11-2019 04:01 |
|   e02    | key03 |  11-11-2019 04:02 |
|   e02    | key04 |  11-11-2019 04:03 |
|   e03    | key05 |  11-11-2019 04:04 |
|   e03    | key06 |  11-11-2019 04:05 |
|   e03    | key07 |  11-11-2019 04:06 |
|   e03    | key08 |  11-11-2019 04:07 |
----------------------------------------

我想查询一个customIDs的列表，其中每个customID的列表都限制为某个数字。

例如，我想做一个类似的查询：

SELECT
customID,
value,
timestamp
FROM
myTable
WHERE
customID
IN
(e02, e03)
ORDER BY timestamp DESC
LIMIT (2, 3);

这将给我以下结果：

customID | value |      timestamp 
-----------+-------+--------------------
|   e02    | key03 |  11-11-2019 04:02 |
|   e02    | key04 |  11-11-2019 04:03 |
|   e03    | key06 |  11-11-2019 04:05 |
|   e03    | key07 |  11-11-2019 04:06 |
|   e03    | key08 |  11-11-2019 04:07 |

如何合理有效地做到这一点？如果可能的话，我宁愿不做UNION或JOIN。

Answer 1

您可以使用row_number():

select *
from (
select
t.*,
row_number() over(partition by customID order by timestamp desc) rn
from mytable
where customID in ('e02', 'e03')
) t
WHERE 
(customID = 'e02' and rn <= 2)
or (customID = 'e03' and rn <= 3)

Answer 2

最有效的方法可能是union all:

(SELECT customID, value, timestamp
FROM myTable
WHERE customID = 'e02'
ORDER BY timestamp DESC
LIMIT 2
) UNION ALL
(SELECT customID, value, timestamp
FROM myTable
WHERE customID = 'e03'
ORDER BY timestamp DESC
LIMIT 3
);

有了(customID, timestamp DESC)的索引，很难想象任何其他公式会更快。