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;