我有以下架構（SQLite）用于捕獲來自信使的訊息：
create table if not exists spot_message
(
id unsigned big int primary key not null,
messenger_id text not null,
messenger_name text not null,
message_type text not null,
timestamp text not null,
latitude real not null,
longitude real not null
);
我使用以下自加入來查找每個發件人的最新訊息：
select t1.*
from spot_message t1
join (select messenger_id, max(timestamp) timestamp
from spot_message
group by messenger_id) t2 on t1.messenger_id = t2.messenger_id and t1.timestamp = t2.timestamp;
我不清楚如何在 jOOQ 中表示這一點。
我目前有：
DSL.using(c.get())
.select(asterisk())
.from(table("spot_message").as("t1"))
.join(select(field("messenger_id"), max(field("timestamp"))).from(table("spot_message"))
.groupBy(field("messenger_id")))
.on(field("messenger_id")
.eq(field("messenger_id"))
.and(field("timestamp")
.eq(field("timestamp"))));
但不清楚我如何表達連接表（“t2”）的表名的“as”。
uj5u.com熱心網友回復：
回答你的問題
要將 a 別名Select為派生表，您可以使用：
Select.asTable(alias)DSL.table(select).as(alias)
您正在使用普通 SQL API進行查詢構建，因此您可以在字串中使用任何 SQL 運算式，例如"t2.messenger_id". 這應該有效：
DSL.using(c.get())
.select(asterisk())
.from(table("spot_message").as("t1"))
.join(select(field("messenger_id"), max(field("timestamp")))
.from(table("spot_message"))
.groupBy(field("messenger_id")).asTable("t2")) // t2 alias here
.on(field("t1.messenger_id") // Qualifications here, and below
.eq(field("t2.messenger_id"))
.and(field("t1.timestamp")
.eq(field("t2.timestamp"))));
但是，如果您使用代碼生成，這將更具可讀性，出于各種原因，我建議您這樣做
SpotMessage t1 = SPOT_MESSAGE.as("t1");
SpotMessage t2 = SPOT_MESSAGE.as("t2");
DSL.using(c.get())
.select(t1.asterisk())
.from(t1)
.join(
select(t2.MESSENGER_ID, max(t2.TIMESTAMP).as(t2.TIMESTAMP))
.from(t2)
.groupBy(t2.MESSENGER_ID).asTable(t2))
.on(t1.MESSENGER_ID.eq(t2.MESSENGER_ID))
.and(t1.TIMESTAMP.eq(t2.TIMESTAMP));
使用視窗函式的替代方法
既然您使用的是支持視窗函式的 SQLite ，為什么不直接使用它們呢？您甚至可以使用QUALIFY語法，如果您使用的是商業發行版，jOOQ 可以為您模擬：
在 SQL 中：
select *
from spot_message
qualify timestamp = max(timestamp) over (partition by messenger_id)
在 jOOQ 中：
ctx.selectFrom(SPOT_MESSAGE)
.qualify(SPOT_MESSAGE.TIMESTAMP.eq(
max(SPOT_MESSAGE.TIMESTAMP).over(partitionBy(SPOT_MESSAGE.MESSENGER_ID))
));
如果QUALIFY您無法使用，您仍然可以通過將查詢包裝在派生表中來手動模擬它：
SELECT *
FROM (
SELECT
spot_message.*,
timestamp = max(timestamp) over (partition by messenger_id) AS is_max
FROM spot_message
) AS t
WHERE is_max
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/430934.html
上一篇：SQLite洗掉“最差”的重復項