mysql - 将 SQL 语法从 MySQL 5.0 转换为 Oracle 12c

Question

我正在尝试将 MySQL 5.0 View 迁移到 Oracle 12c。这是我在 MySQL 中的语法:

select count(`table1`.`uuid`) AS `Count`,
`table2`.`description` AS `STATUS`,
`table3`.`name` AS `Court` 
from ((`table2` join `table1`) 
join `table3`) 
where ((`table1`.`statusCode` = `table2`.`code`) 
and (`table3`.`uuid` = `table1`.`courtUuid`)) 
group by `table1`.`courtUuid`,`table2`.`description`;

这是我的 Oracle 12c 语法:

select count(table1.uuid) AS Count,
table2.description AS STATUS,
table3.NAME as Court
from table2
join table1
on (table1.statuscode = table2.code)
join table3
on (table3.uuid = table1.courtuuid)
group by table1.courtuuid,table2.description;

当我运行 Oracle 12c 语法时，我得到:

ORA-00979: 不是 GROUP BY 表达式00979. 00000 - “不是 GROUP BY 表达式”*原因:
*行动:行错误:3 列:1

但是，如果我添加 table3.NAME 作为分组依据的一部分，我会得到 866 行。 MySQL 中的查询生成 1025 行。

select count(table1.uuid) AS Count,
table2.description AS STATUS,
table3.NAME as Court
from table2
join table1
on (table1.statuscode = table2.code)
join table3
on (table3.uuid = table1.courtuuid)
group by table1.courtuuid,table2.description,table3.NAME;

有人可以帮我解决这个问题吗？

Answer 1

您可能想要:

select count(table1.uuid) AS Count,
       table2.description AS STATUS,
       table3.NAME as Court
from table2 join
     table1
     on table1.statuscode = table2.code join
     table3
     on (able3.uuid = table1.courtuuid
group by table3.name, table2.description;

select 中未聚合的列应位于group by 中。不幸的是，MySQL 有一个(错误的)功能不需要这个逻辑。

严格来说，你还可以这样做:

select count(table1.uuid) AS Count,
       table2.description AS STATUS,
       max(table3.NAME) as Court
from table2 join
     table1
     on table1.statuscode = table2.code join
     table3
     on table3.uuid = table1.courtuuid
group by table1.courtuuid, table2.description;

这更相当于您的查询，但第一个版本可能就是您想要的。