我有一个用于MySQL的SQL查询：
1
954WITH `cte` AS (
( SELECT 1431655747 AS `n` FROM DUAL ) UNION ALL
( SELECT 1431655733 AS `n` FROM DUAL ) UNION ALL
( SELECT 715827794 AS `n` FROM DUAL ) UNION ALL
( SELECT 715827865 AS `n` FROM DUAL ) UNION ALL
( SELECT 1073741809 AS `n` FROM DUAL ) UNION ALL
( SELECT 1073741759 AS `n` FROM DUAL ) UNION ALL
( SELECT 715827800 AS `n` FROM DUAL ) UNION ALL
( SELECT 1431655693 AS `n` FROM DUAL ) UNION ALL
( SELECT 715827789 AS `n` FROM DUAL ) UNION ALL
( SELECT 715827838 AS `n` FROM DUAL ) UNION ALL
( SELECT 715827823 AS `n` FROM DUAL ) UNION ALL
( SELECT 858993391 AS `n` FROM DUAL )) SELECT
`cte`.`n`,
`maxmind_country`.`country`.`name_en`
FROM
`cte`
JOIN `maxmind_country`.`ipv4` ON `cte`.`n` BETWEEN `maxmind_country`.`ipv4`.`start_int`
AND `maxmind_country`.`ipv4`.`last_int`
JOIN `maxmind_country`.`country` ON `maxmind_country`.`country`.`geoname_id` = `maxmind_country`.`ipv4`.`v_geoname_id`;
转换为jOOQ DSL后：
String ipAlias = "n";
SelectSelectStep<Record1<UInteger>> unionIps = ips
.stream()
.distinct()
.map(value -> DSL.select(DSL.val(value).as(ipAlias)))
.reduce((r1, r2) -> (SelectSelectStep<Record1<UInteger>>) r1.unionAll(r2))
.orElse(null);
if(unionIps == null)
return null;
CommonTableExpression<Record1<UInteger>> cte = DSL.name("cte").as(unionIps);
Field<UInteger> ipField = cte.field(ipAlias, UInteger.class);
return dslContext
.with(cte)
.select(ipField, COUNTRY.NAME_EN)
.from(cte)
.join(IPV4).on(ipField.between(IPV4.START_INT, IPV4.LAST_INT))
.join(COUNTRY).on(COUNTRY.GEONAME_ID.eq(IPV4.V_GEONAME_ID))
.fetchMap(ipField, Country.class);
结果为：
+------------+----------------+ | n | name_en | +------------+----------------+ | 1431655747 | Spain | | 1431655733 | Spain | | 715827794 | China | | 715827865 | China | | 1073741809 | United States | | 1073741759 | United States | | 715827800 | China | | 1431655693 | Spain | | 715827789 | China | | 715827838 | China | | 715827823 | China | | 858993391 | United Kingdom | +------------+----------------+ 12 rows in set (0.16 sec)
但是解释显示有多余的UNION：
id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL 12 100,00 1 PRIMARY ipv4 ALL ipv4_start_int_UNIQUE,ipv4_last_int_UNIQUE,ipv4_v_geoname_id_fk_idx 325934 11,11 Range checked for each record (index map: 0x7) 1 PRIMARY country eq_ref PRIMARY PRIMARY 4 maxmind_country.ipv4.v_geoname_id 1 100,00 2 DERIVED No tables used 3 UNION No tables used 4 UNION No tables used 5 UNION No tables used 6 UNION No tables used 7 UNION No tables used 8 UNION No tables used 9 UNION No tables used 10 UNION No tables used 11 UNION No tables used 12 UNION No tables used 13 UNION No tables used
我用另一种MySQL语法重写了一个SQL - VALUES ROW(1), ROW(2), ROW(n)：
WITH `cte` AS (
VALUES ROW(1431655747),
ROW(1431655733),
ROW(715827794),
ROW(715827865),
ROW(1073741809),
ROW(1073741759),
ROW(715827800),
ROW(1431655693),
ROW(715827789),
ROW(715827838),
ROW(715827823),
ROW(858993391)) SELECT
`cte`.`column_0`,
`maxmind_country`.`country`.`name_en`
FROM
`cte`
JOIN `maxmind_country`.`ipv4` ON `cte`.`column_0` BETWEEN `maxmind_country`.`ipv4`.`start_int`
AND `maxmind_country`.`ipv4`.`last_int`
JOIN `maxmind_country`.`country` ON `maxmind_country`.`country`.`geoname_id` = `maxmind_country`.`ipv4`.`v_geoname_id`;
结果相同：
+------------+----------------+ | column_0 | name_en | +------------+----------------+ | 1431655747 | Spain | | 1431655733 | Spain | | 715827794 | China | | 715827865 | China | | 1073741809 | United States | | 1073741759 | United States | | 715827800 | China | | 1431655693 | Spain | | 715827789 | China | | 715827838 | China | | 715827823 | China | | 858993391 | United Kingdom | +------------+----------------+ 12 rows in set (0.16 sec)
但是查询计划器不使用UNION：
id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL 12 100,00 1 PRIMARY ipv4 ALL ipv4_start_int_UNIQUE,ipv4_last_int_UNIQUE,ipv4_v_geoname_id_fk_idx 325934 11,11 Range checked for each record (index map: 0x7) 1 PRIMARY country eq_ref PRIMARY PRIMARY 4 maxmind_country.ipv4.v_geoname_id 1 100,00 2 DERIVED No tables used
问题：如何将最后的脚本转换为jOOQ DSL？我找不到任何方法来实现
WITH `cte` AS (VALUES ROW(1), ROW(2), ROW(n)) ... ?
Copyright 2014-2025 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号
收藏
截至jOOQ 3.15版本，还没有办法将标准SQL
VALUES构造器表示为org.jooq.Select。它只能表示为org.jooq.Table，请参见：https://github.com/jOOQ/jOOQ/issues/5871。因此，您还不能使用jOOQ DSL表示完全符合要求的语法，但是您可以在
FROM子句中使用VALUES：这应该会产生与您所期望的几乎相同的计划。另一种解决方法是使用纯SQL模板。