沈阳CF
表有个inxh的字段 是numeric 类型,我想得到的是查询之后序号从新排列,大的号码是连续的, 10以内的为什么不能连续了呢.下面是我的查询语句和结果,请大家帮忙看下
SELECT (SELECT COUNT(inxh)
FROM 沈阳CF
WHERE INXH <= a.INXH) AS row_num, INXH
FROM 沈阳CF a
WHERE (INCITY = '沈阳') AND (INFOR LIKE '%询%')
ORDER BY INXH DESC
row_num INXH
2208 2552
2207 2551
2206 2550
2205 2549
2204 2548
2203 2547
2202 2546
2201 2545
2200 2544
2199 2543
............................
12 17
11 16
10 15 这以下就不连续了
8 9
7 8
5 6
4 5
2 2
1 1
表有个inxh的字段 是numeric 类型,我想得到的是查询之后序号从新排列,大的号码是连续的, 10以内的为什么不能连续了呢.下面是我的查询语句和结果,请大家帮忙看下
SELECT (SELECT COUNT(inxh)
FROM 沈阳CF
WHERE INXH <= a.INXH) AS row_num, INXH
FROM 沈阳CF a
WHERE (INCITY = '沈阳') AND (INFOR LIKE '%询%')
ORDER BY INXH DESC
row_num INXH
2208 2552
2207 2551
2206 2550
2205 2549
2204 2548
2203 2547
2202 2546
2201 2545
2200 2544
2199 2543
............................
12 17
11 16
10 15 这以下就不连续了
8 9
7 8
5 6
4 5
2 2
1 1
不过,不连续也没有问题吧.
select *
from
(
select row_number() over(order by INXH ) row_num,*
from 沈阳CF WHERE (INCITY = '沈阳') AND (INFOR LIKE '%询%')
) a
order by row_num desc
( SELECT COUNT(inxh)
FROM 沈阳CF
where (INCITY = '沈阳') AND (INFOR LIKE '%询%') and INXH <= a.INXH
) AS row_num, INXH
FROM 沈阳CF a
WHERE (INCITY = '沈阳') AND (INFOR LIKE '%询%')
ORDER BY a.INXH DESC
因为INXH中具有重复的值,这些值,跳过去了,
如果 是2005,可以使用row_number(),rank()排序函数