请使用

SELECT CATEGORY as CategoryName, sum(rownum) FROM APPLICATION WHERE applicationId=? GROUP BY CATEGORY

您需要按如下方式使用子查询：

select CategoryName , rownum as categoryId from
(SELECT distinct(CATEGORY) as CategoryName FROM APPLICATION where applicationId=?)

您也可以使用 analytical function 具体如下：

SELECT distinct(CATEGORY) as CategoryName, 
       rank() over (order by CATEGORY) as categoryId 
  FROM APPLICATION where applicationId=?

例子：
我有以下数据：

SQL> SELECT WRITER_ID, TWEET FROM TWEET;

 WRITER_ID TWEET
---------- -----
         1 T1
         1 T2

SQL>

查看以上两个查询的输出：

SQL> SELECT WRITER_ID, ROWNUM AS GENERATED_NUMBER FROM
  2  (SELECT DISTINCT WRITER_ID AS WRITER_ID FROM TWEET);

 WRITER_ID GENERATED_NUMBER
---------- ----------------
         1                1

SQL> SELECT DISTINCT ( WRITER_ID ),
  2                  RANK() OVER(ORDER BY WRITER_ID) AS GENERATED_NUMBER
  3    FROM TWEET;

 WRITER_ID GENERATED_NUMBER
---------- ----------------
         1                1

现在，让我更改数据

SQL> UPDATE TWEET
  2     SET
  3  WRITER_ID = 2
  4   WHERE ID = 101;

1 row updated.

表数据已更改：

SQL> SELECT WRITER_ID, TWEET FROM TWEET;

 WRITER_ID TWEET
---------- -----
         1 T1
         2 T2

SQL>

现在让我们看看以上查询的结果是什么：

SQL> SELECT WRITER_ID, ROWNUM AS GENERATED_NUMBER FROM
  2  (SELECT DISTINCT WRITER_ID AS WRITER_ID FROM TWEET);

 WRITER_ID GENERATED_NUMBER
---------- ----------------
         1                1
         2                2

SQL> SELECT DISTINCT ( WRITER_ID ),
  2                  RANK() OVER(ORDER BY WRITER_ID) AS GENERATED_NUMBER
  3    FROM TWEET;

 WRITER_ID GENERATED_NUMBER
---------- ----------------
         2                2
         1                1

试着用这个

SELECT DISTINCT(CATEGORY) AS CategoryName, 
       ROW_NUMBER() OVER (ORDER BY CATEGORY) AS categoryId
FROM APPLICATION 
WHERE applications =?