mysql - 当我尝试将它从 mysql 迁移到 Oracle 时出现 SQL 语句错误

Question

我应该将我的数据库从 mysql 迁移到 oracle，我的问题是我的陈述不再起作用了!我应该保持相同的格式(别名 champ1，champ2)

这是我的 SQL 语句:

select count(j.id) as nb, p.pname as champ1,proj.pname as champ2 
from  jiraissue j  
join priority p on (p.id=j.priority)
join project proj on(proj.id=j.project)
join issuestatus iss on (iss.id=j.issuestatus)
join issuetype isst on(isst.id=j.issuetype )
where j.CREATED between '2011-05-18 00:00:00.0' and '2011-06-05 00:00:00.0'
and iss.pname='Open' 
group by champ1,champ2;

错误:

ORA-00904: "CHAMP2" : identificateur non valide
00904. 00000 -  "%s: invalid identifier"

PS: 当我从 select 和 execute 中删除 groupby 和别名时，也会出现错误，表明日期格式无效

ORA-01861: le littéral ne concorde pas avec le format chaîne de caractères

Answer 1

出于多种原因，大多数 SQL 实现不允许您对在查询的 SELECT 部分中设置别名的内容进行 GROUP BY。

select count(j.id) as nb, p.pname as champ1,proj.pname as champ2 
from  jiraissue j  
join priority p on (p.id=j.priority)
join project proj on(proj.id=j.project)
join issuestatus iss on (iss.id=j.issuestatus)
join issuetype isst on(isst.id=j.issuetype )
where
  j.CREATED between '2011 May 18' and '2011 June 05'
  and iss.pname='Open' 
group by
  p.pname,
  proj.pname
;

编辑 - 跟进评论:

我不确定为什么上面的答案对你没有多大帮助，也许你不想在代码中重复自己？ (您通过按别名分组获得的东西，因此在所有其他 rdbms 中丢失)如果是这样，以下模式可能会有所帮助...

SELECT
  COUNT(j_id) AS nb, champ1, champ2
FROM
(
  select j.id AS j_id, p.pname as champ1, proj.pname as champ2
  from  jiraissue j  
  join priority p on (p.id=j.priority)
  join project proj on(proj.id=j.project)
  join issuestatus iss on (iss.id=j.issuestatus)
  join issuetype isst on(isst.id=j.issuetype )
   where
    j.CREATED between '2011 May 18' and '2011 June 05'
    and iss.pname='Open' 
)
  AS data
GROUP BY
  champ1,
  champ2
;