ORA-00904无效标识符错误

Question

我遇到了ORA-00904 'c' invalid identifier错误。这个别名出了什么问题？

select NO, count(JOINT_NO) as c
from JOINT
WHERE HOLDER = 'Y' AND JOINT.c > 1
GROUP BY NO;

- noway

Answer 1

我认为你想获取那些计数大于1的记录。所以你不能在同一个查询的WHERE子句中使用识别符C。

select NO, count(JOINT_NO) as c
from JOINT
WHERE HOLDER = 'Y'
GROUP BY NO
HAVING COUNT(JOINT_NO)>1;

- Gaurav Soni

Answer 2

你所遇到的错误信息是由于表 JOINT 中不存在列c。

请按如下方式修改你的查询语句：

select NO, count(JOINT_NO) as c
from JOINT
WHERE HOLDER = 'Y'
GROUP BY NO
HAVING count(JOINT_NO) > 1;

- Daniel Hilgarth

Answer 3

您可以在FROM子句中使用子查询(subselect)：

 select T1.NO,T1.c
    from
    (
      select NO, count(JOINT_NO) as c
      from JOINT
      WHERE HOLDER = 'Y' 
      GROUP BY NO
    ) as T1
    where T1.c > 1

你也可以使用having：

    select NO, count(JOINT_NO) as c
    from JOINT
    WHERE HOLDER = 'Y'
    GROUP BY NO
    HAVING COUNT(JOINT_NO)>1

- Vahid Heydarinezhad

---

你好，欢迎来到Stack Overflow。我想鼓励你解释一下你的答案（不必过于冗长），这样未来的读者就能理解发生了什么。我建议你看一下[答案]。 - mmgross