我有一个表应用程序，它有10列。类别是一列，此列具有重复的值。为了获得不同的值，我有一个查询
SELECT distinct(CATEGORY) as CategoryName FROM APPLICATION where applicationid=?。
我得到的结果没有任何问题。现在，我想在此处添加另一列作为categoryId。没有这样的字段，我必须生成一个。我尝试了以下查询。
SELECT distinct(CATEGORY) as CategoryName,rownum as categoryId FROM APPLICATION where applicationid=?
然后显示重复的类别，其中rownum为id。我可以使用任何数字作为ID，但类别名称不应重复。谁能在一个查询中建议如何做到这一点。
您需要按如下方式使用子查询：
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 CATEGORY as CategoryName,sum(rownum) FROM APPLICATION WHERE applicationId=? GROUP BY CATEGORY
尝试使用此
|line|