3.3.5. DQL

3.3.5.1. Oracle_19C

3.3.5.1.1. 查询表

语法

with_clause:
WITH [RECURSIVE]
    cte_name [(col_name [, col_name] ...)] AS (subquery)
    [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
    SELECT xxx FROM cte_name xxx;

警告

-HAVING语法必须与GROUP BY一起使用否则不支持

示例

-- 转换前与转换后sql相同 SQL:
WITH sales_employees AS (
            SELECT employee_id, first_name, last_name, department, salary
            FROM employees
            WHERE department = 'Sales'
    )
    SELECT * FROM sales_employees
    WHERE salary > 50000;

3.3.5.1.2. dql语句

语法

SELECT
    [DISTINCT]
    select_expr[, select_expr]...
FROM
    table_references
[WHERE where_condition]
[GROUP BY {col_name}, ...]
[HAVING where_condition]
[ORDER BY {col_name | expr} [ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[FOR {UPDATE} [OF tbl_name [, tbl_name]...]]

警告

在 MySQL 中，执行 SELECT DISTINCT 时，NULL 和空字符串被视为不同的值，因此不会被去重。而在Oracle 中，NULL 和空字符串被视为相同的值，会被去重。
在 MySQL 中，使用 WHERE column IS NULL 只会查询出 column 列值为 NULL 的数据；而在Oracle 中，该条件会同时匹配 column 列值为 NULL 和空字符串的数据。
不支持
1. 在MySQL中，布尔表达式会被自动转换为整数（0或1），其中TRUE被转换为1，FALSE被转换为0。而在Oracle 中，SELECT语句中select列使用布尔表达式会导致语法错误。比如SELECT 1>1 FROM dual
2. Oracle 中，不允许对包含 DISTINCT、GROUP BY、聚合函数或某些其他操作的视图或查询结果使用 FOR UPDATE 子句。这些操作会导致结果集的行无法唯一映射回基表的物理行，无法保证行级锁的准确性。比如SELECT * FROM Orders_111 ORDER BY Amount DESC FETCH NEXT 3 ROWS ONLY for update

示例

-- mysql语法数据准备:
CREATE TABLE students_30629_20250324 (
  student_id int PRIMARY KEY,
  name varchar(150) NOT NULL,
  age int,
  gender varchar(150)
);

CREATE TABLE scores_30629_20250324 (
  score_id int PRIMARY KEY,
  student_id int,
  subject varchar(90),
  score decimal(5, 2)
);

INSERT INTO students_30629_20250324 (student_id, name, age, gender)
VALUES (1, 'Alice', 20, 'Female');

INSERT INTO students_30629_20250324 (student_id, name, age, gender)
VALUES (2, 'Bob', 22, 'Male');

INSERT INTO students_30629_20250324 (student_id, name, age, gender)
VALUES (3, 'Charlie', 21, 'Male');

INSERT INTO students_30629_20250324 (student_id, name, age, gender)
VALUES (4, 'Diana', 19, 'Female');

INSERT INTO scores_30629_20250324 (score_id, student_id, subject, score)
VALUES (1, 1, 'Math', 90.5);

INSERT INTO scores_30629_20250324 (score_id, student_id, subject, score)
VALUES (2, 1, 'Physics', 85.0);

INSERT INTO scores_30629_20250324 (score_id, student_id, subject, score)
VALUES (3, 2, 'Math', 78.0);

INSERT INTO scores_30629_20250324 (score_id, student_id, subject, score)
VALUES (4, 2, 'Physics', 92.5);

INSERT INTO scores_30629_20250324 (score_id, student_id, subject, score)
VALUES (5, 3, 'Math', 88.0);

INSERT INTO scores_30629_20250324 (score_id, student_id, subject, score)
VALUES (6, 4, 'Physics', 76.5);


-- 转换前MySQL SQL，for update:
SELECT * FROM scores_30629_20250324 WHERE student_id = 1 FOR UPDATE;
-- 转换后oracle19c SQL，for update:
SELECT * FROM scores_30629_20250324 WHERE student_id = 1 FOR UPDATE;

-- 转换前MySQL SQL，distinct:
SELECT DISTINCT student_id,name,age FROM students_30629_20250324 ORDER BY student_id DESC;
-- 转换后oracle19c SQL，distinct:
SELECT DISTINCT student_id,name,age FROM students_30629_20250324 ORDER BY student_id DESC;

-- 转换前MySQL SQL，分页：
SELECT * FROM scores_30629_20250324 ORDER BY student_id DESC LIMIT 1, 2
-- 转换后oracle19c SQL，分页:
SELECT * FROM scores_30629_20250324 ORDER BY student_id DESC OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY;

-- 转换前MySQL SQL，group by + having + order by，其中select列包含字段不在group by中：
SELECT
    s.student_id,
    stu.name,
    MIN(s.score) AS min_score
FROM
    scores_30629_20250324 s
INNER JOIN
    students_30629_20250324 stu ON s.student_id = stu.student_id
GROUP BY
    s.student_id
HAVING
    MIN(s.score) > 80
ORDER BY
    stu.name DESC;

-- 转换后oracle19c SQL，group by + having + order by，其中select列包含字段不在group by中：
SELECT
    s.student_id AS student_id,
    ANY_VALUE(stu.name) AS name,
    MIN(s.score) AS min_score
FROM
    scores_30629_20250324 s
INNER JOIN
    students_30629_20250324 stu ON s.student_id = stu.student_id
GROUP BY
    s.student_id
HAVING
    MIN(s.score) > 80
ORDER BY
    ANY_VALUE(stu.name) DESC;

3.3.5.2. OceanBase-Oracle

3.3.5.2.1. 查询表

语法

with_clause:
WITH [RECURSIVE]
    cte_name [(col_name [, col_name] ...)] AS (subquery)
    [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
    SELECT xxx FROM cte_name xxx;

警告

-HAVING语法必须与GROUP BY一起使用否则不支持

示例

-- 转换前与转换后sql相同 SQL:
WITH sales_employees AS (
            SELECT employee_id, first_name, last_name, department, salary
            FROM employees
            WHERE department = 'Sales'
    )
    SELECT * FROM sales_employees
    WHERE salary > 50000;

3.3.5.2.2. dql语句

语法

SELECT
    [DISTINCT]
    select_expr[, select_expr]...
FROM
    table_references
[WHERE where_condition]
[GROUP BY {col_name}, ...]
[HAVING where_condition]
[ORDER BY {col_name | expr} [ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[FOR {UPDATE} [OF tbl_name [, tbl_name]...]]

警告

在 MySQL 中，执行 SELECT DISTINCT 时，NULL 和空字符串被视为不同的值，因此不会被去重。而在 OceanBase Oracle 中，NULL 和空字符串被视为相同的值，会被去重。
在 MySQL 中，使用 WHERE column IS NULL 只会查询出 column 列值为 NULL 的数据；而在 OceanBase Oracle 中，该条件会同时匹配 column 列值为 NULL 和空字符串的数据。
不支持
1. 在MySQL中，布尔表达式会被自动转换为整数（0或1），其中TRUE被转换为1，FALSE被转换为0。而在OceanBase Oracle 中，SELECT语句中select列使用布尔表达式会导致语法错误。比如SELECT 1>1 FROM dual
2. 在OceanBase Oracle 中，不允许对包含 DISTINCT、GROUP BY、聚合函数或某些其他操作的视图或查询结果使用 FOR UPDATE 子句。这些操作会导致结果集的行无法唯一映射回基表的物理行，无法保证行级锁的准确性。比如SELECT * FROM Orders_111 ORDER BY Amount DESC FETCH NEXT 3 ROWS ONLY for update

示例

-- mysql语法数据准备:
CREATE TABLE students_30629_20250324 (
  student_id int PRIMARY KEY,
  name varchar(150) NOT NULL,
  age int,
  gender varchar(150)
);

CREATE TABLE scores_30629_20250324 (
  score_id int PRIMARY KEY,
  student_id int,
  subject varchar(90),
  score decimal(5, 2)
);

INSERT INTO students_30629_20250324 (student_id, name, age, gender)
VALUES (1, 'Alice', 20, 'Female');

INSERT INTO students_30629_20250324 (student_id, name, age, gender)
VALUES (2, 'Bob', 22, 'Male');

INSERT INTO students_30629_20250324 (student_id, name, age, gender)
VALUES (3, 'Charlie', 21, 'Male');

INSERT INTO students_30629_20250324 (student_id, name, age, gender)
VALUES (4, 'Diana', 19, 'Female');

INSERT INTO scores_30629_20250324 (score_id, student_id, subject, score)
VALUES (1, 1, 'Math', 90.5);

INSERT INTO scores_30629_20250324 (score_id, student_id, subject, score)
VALUES (2, 1, 'Physics', 85.0);

INSERT INTO scores_30629_20250324 (score_id, student_id, subject, score)
VALUES (3, 2, 'Math', 78.0);

INSERT INTO scores_30629_20250324 (score_id, student_id, subject, score)
VALUES (4, 2, 'Physics', 92.5);

INSERT INTO scores_30629_20250324 (score_id, student_id, subject, score)
VALUES (5, 3, 'Math', 88.0);

INSERT INTO scores_30629_20250324 (score_id, student_id, subject, score)
VALUES (6, 4, 'Physics', 76.5);


-- 转换前MySQL SQL，for update:
SELECT * FROM scores_30629_20250324 WHERE student_id = 1 FOR UPDATE;
-- 转换后ocean_base_oracle(4.2.1) SQL，for update:
SELECT * FROM scores_30629_20250324 WHERE student_id = 1 FOR UPDATE;

-- 转换前MySQL SQL，distinct:
SELECT DISTINCT student_id,name,age FROM students_30629_20250324 ORDER BY student_id DESC;
-- 转换后ocean_base_oracle(4.2.1) SQL，distinct:
SELECT DISTINCT student_id,name,age FROM students_30629_20250324 ORDER BY student_id DESC;

-- 转换前MySQL SQL，分页：
SELECT * FROM scores_30629_20250324 ORDER BY student_id DESC LIMIT 1, 2
-- 转换后ocean_base_oracle(4.2.1)  SQL，分页:
SELECT * FROM scores_30629_20250324 ORDER BY student_id DESC OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY;

-- 转换前MySQL SQL，group by + having + order by，其中select列包含字段(stu.name)不在group by中：
SELECT
    s.student_id,
    stu.name,
    MIN(s.score) AS min_score
FROM
    scores_30629_20250324 s
INNER JOIN
    students_30629_20250324 stu ON s.student_id = stu.student_id
GROUP BY
    s.student_id
HAVING
    MIN(s.score) > 80
ORDER BY
    stu.name DESC;

-- 转换后ocean_base_oracle(4.2.1)  SQL，group by + having + order by，其中select列包含字段(stu.name)不在group by中：
SELECT
  s.student_id AS student_id,
  MAX(stu.name) AS name,
  min(s.score) AS min_score
FROM
  scores_30629_20250324 s
  INNER JOIN students_30629_20250324 stu ON s.student_id = stu.student_id
GROUP BY
  s.student_id
HAVING
  min(s.score) > 80
ORDER BY
  MAX(stu.name) DESC;

3.3.5.3. GaussDB-MySQL

3.3.5.3.1. select … from … force index(index_name) where …

语法

select xxx from table_name force index(index_name) where xxx;

警告

目前仅限于在单表查询中使用
force index后只能指定一个索引名
类似于CTE等复杂嵌套查询暂不支持

示例

--原始SQL
select * from unisql_force_index1 force index(t_idx_1) WHERE id=4;

--经过统一SQL转换后
SELECT /*+ IndexScan(`unisql_force_index1` `t_idx_1`) */ * FROM `unisql_force_index1` WHERE `id`=4;

3.3.5.3.2. 查询的列可以不在group by中

语法

select column[,column...][alias] from table_reference_where_condition group by column[,column...][alias]

警告

表名列名，别名创建的时候和使用的时候必须一致
查询的列不支持表达式运算，只支持实际的列
不支持多层子查询
如果有函数则函数中的列必须在group by中,且不支持嵌套聚合函数使用，只能一层
group by 不支持函数和表达式运算
两边数据库默认排序机制不一致，会存在查询结果不一致
不支持select table.*、select *
如果是多表关联，只支持主键或唯一键作为关联键。

示例

--原始SQL
SELECT o.price, o.product_name
FROM group_by_2025_6_19 o
WHERE o.price > 2.00
GROUP BY o.customer_name, o.category

--经过统一SQL转换后
SELECT unisql.any_value(o.price) AS price,
unisql.any_value(o.product_name) AS product_name
FROM group_by_2025_6_19 AS o WHERE o.price>2.00
GROUP BY o.customer_name,o.category

3.3.5.3.3. 查询时order by中列可以不在group by中

语法

select column[,column...][alias]
from table_reference_where_condition
group by column[,column...][alias]
order by column[,column...][alias]

警告

表名列名，别名创建的时候和使用的时候必须一致
查询的列不支持表达式运算，只支持实际的列
不支持多层子查询
如果有函数则函数中的列必须在group by中,且不支持嵌套聚合函数使用，只能一层
group by 、order by 不支持函数和表达式运算
两边数据库默认排序机制不一致，会存在查询结果不一致
不支持select table.*、select *
如果是多表关联，只支持主键或唯一键作为关联键。

示例

--原始SQL
SELECT o.customer_name, o.price
FROM group_by_order_by_2025_6_19 o
GROUP BY o.customer_name order by o.customer_name,o.price;

--经过统一SQL转换后
SELECT o.customer_name AS customer_name,
unisql.any_value(o.price) AS price
FROM group_by_order_by_2025_6_19 AS o
GROUP BY o.customer_name
ORDER BY unisql.any_value(o.customer_name),unisql.any_value(o.price)

3.3.5.3.4. 查询distinct列时order by中的列可以不在distinct列中(没有group by时)

语法

select distinct column[,column...][alias]
from table_reference_where_condition
order by column[,column...][alias]

警告

表名列名，别名创建的时候和使用的时候必须一致
查询的列不支持表达式运算，只支持实际的列
不支持多层子查询
不支持查询函数
order by 不支持函数和表达式运算
两边数据库默认排序机制不一致，会存在查询结果不一致
不支持select distinct table.*、select distinct *
如果是多表关联，只支持主键或唯一键作为关联键。

示例

--原始SQL
SELECT o.customer_name, o.price
FROM group_by_order_by_2025_6_19 o
GROUP BY o.customer_name order by o.customer_name,o.price;

--经过统一SQL转换后
SELECT o.customer_name AS customer_name,
unisql.any_value(o.price) AS price
FROM group_by_order_by_2025_6_19 AS o
GROUP BY o.customer_name
ORDER BY unisql.any_value(o.customer_name),unisql.any_value(o.price)