Tulis semula tajuk sebagai: Cara menukar DENGAN `cte` AS (VALUES ROW(1), ROW(2), ROW(n)) dalam sintaks MySQL kepada jOOQ DSL

Saya mempunyai pertanyaan SQL untuk MySQL:

WITH `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`;

Selepas penukaran kepada 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);

Hasilnya ialah:

+------------+----------------+
| 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)

Tetapi penjelasan menunjukkan KESATUAN yang berlebihan:

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

Saya menulis semula SQL menggunakan sintaks MySQL yang lain - 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`;

Hasil yang sama:

+------------+----------------+
| 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)

Tetapi perancang pertanyaan tidak menggunakan 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

Soalan: Bagaimana untuk menukar skrip akhir kepada jOOQ DSL? Saya tidak dapat mencari apa-apa cara untuk melakukannya

WITH `cte` AS (VALUES ROW(1), ROW(2), ROW(n)) ... ?

P粉794177659319 hari yang lalu368

Tulis semula tajuk sebagai: Cara menukar DENGAN `cte` AS (VALUES ROW(1), ROW(2), ROW(n)) dalam sintaks MySQL kepada jOOQ DSL

membalas semua(1)saya akan balas