对于一个经典的排名问题(每个玩家3个最佳结果的总和),我使用jooqdsl和scala(作为select值嵌入并加入到更复杂的查询中)提出了以下子查询:
val tr1 = TOUR_RESULT.as("tr1")
val tr2 = TOUR_RESULT.as("tr2")
val inner:Table[Record1[java.lang.Integer]] = DSL.select(count().as("count")).from(tr2).where(tr1.PLAYER_ID.eq(tr2.PLAYER_ID).and(tr1.NSP_SCORE.le(tr2.NSP_SCORE))).asTable("tr3")
val result = sql.select(tr1.PLAYER_ID,tr1.NSP_SCORE.sum().as("score"))
.from(tr1)
.where(inline(3).gt(sql.selectQuery(inner)))
.groupBy(tr1.PLAYER_ID)
.orderBy(2)
.execute()
我目前的问题(使用mariadb10和jooq3.9)是使这个生成的查询能够与mysql一起工作,并按‘score’(第2列)降序排序。我相信我可以使用原始sql语句重写整个语句并转换结果(因此绕过jooqapi)。不过,我希望尽可能长时间地保持编译器和jooqapi的整洁。因此,如果我再次接触这个查询,也许有一个解决方案在将来仍然可读。
说明案例的模式和生成的sql可以在下面找到http://sqlfiddle.com/#!9/2f614f/3,注解中有违规行/语句。
create table TOUR_RESULT (
player_id int,
nsp_score int
);
insert into TOUR_RESULT values (1,4);
insert into TOUR_RESULT values (1,14);
insert into TOUR_RESULT values (1,24);
insert into TOUR_RESULT values (1,34);
insert into TOUR_RESULT values (1,44);
insert into TOUR_RESULT values (2,3);
insert into TOUR_RESULT values (2,13);
insert into TOUR_RESULT values (2,23);
insert into TOUR_RESULT values (2,33);
insert into TOUR_RESULT values (2,43);
insert into TOUR_RESULT values (3,3);
insert into TOUR_RESULT values (3,13);
insert into TOUR_RESULT values (4,130);
insert into TOUR_RESULT values (5,2);
insert into TOUR_RESULT values (5,7);
insert into TOUR_RESULT values (5,7);
insert into TOUR_RESULT values (5,7);
insert into TOUR_RESULT values (5,5);
insert into TOUR_RESULT values (5,7);
insert into TOUR_RESULT values (5,10);
insert into TOUR_RESULT values (5,12);
SELECT `tr1`.`player_id`, sum(`tr1`.`nsp_score`) AS `score`
FROM `tour_result` AS `tr1`
WHERE 3 >=
-- (SELECT `tr3`.`count`
-- FROM
(SELECT count(*) AS `count`
FROM `tour_result` AS `tr2`
WHERE (`tr1`.`player_id` = `tr2`.`player_id`
AND `tr1`.`nsp_score` <= `tr2`.`nsp_score`))
-- AS `tr3`)
GROUP BY `tr1`.`player_id`
ORDER BY 2 desc;
取消注解行时的错误是
Unknown column 'tr1.player_id' in 'where clause'
1条答案
按热度按时间b1uwtaje1#
不幸的是,mariadb和mysql不允许在相关子查询中引用“两级以上”列。但是,如果您有mariadb 10.2或mysql 8.0,则可以使用窗口函数来执行作业:
sql版本
筛选依据
ROW_NUMBER()
将在分数中选择3个中奖行。如果你想有3行或更多行,如果他们是并列的(WITH TIES
语义),您可以使用RANK()
. 我以前也写过这个主题的博客。jooq版本
这将转换为以下jooq查询:
以上假设