数据是verchar型的
表A中的字段是数据项是3011,3012,2001,3001,3005,2002,2003,3006,3009,3010,3013,3014
我需要得到的是2001-2003,3001,3005-3006,3009-3014
这是一个报表,数据没有规律存放并且是中断的。 如果是按从小到大有规律存放,可以解决。
CREATE TABLE A(COL VARCHAR2(10));
INSERT INTO A VALUES('2001');
INSERT INTO A VALUES('2002');
INSERT INTO A VALUES('2003');
INSERT INTO A VALUES('3001');
INSERT INTO A VALUES('3005');
INSERT INTO A VALUES('3006');
INSERT INTO A VALUES('3009');
INSERT INTO A VALUES('3010');
INSERT INTO A VALUES('3011');
INSERT INTO A VALUES('3012');
INSERT INTO A VALUES('3013');
INSERT INTO A VALUES('3014');
COMMIT;SELECT DECODE(MAX(COL),MIN(COL),MIN(COL),MIN(COL) || '-' || MAX(COL)) COL FROM(
SELECT COL,COL-ROWNUM COL1 FROM A ORDER BY COL)
GROUP BY COL1;COL
------------
2001-2003
3001
3005-3006
3009-3014
但是如果无规律呢?
CREATE TABLE A(COL VARCHAR2(10));
INSERT INTO A VALUES('3013');
INSERT INTO A VALUES('3002');
INSERT INTO A VALUES('3014');
INSERT INTO A VALUES('2001');
INSERT INTO A VALUES('3014');
INSERT INTO A VALUES('2002');
INSERT INTO A VALUES('2003');
INSERT INTO A VALUES('3001');
INSERT INTO A VALUES('3005');
INSERT INTO A VALUES('3006');
INSERT INTO A VALUES('3009');
INSERT INTO A VALUES('3010');
INSERT INTO A VALUES('3011');
INSERT INTO A VALUES('3012');COMMIT;
解决方案 »
- oracle如何连接mysql
- 如果获取某个用户下所有的表中的记录数?
- select * from 表,请问如何给结果编号。
- sql语句的优化问题
- 两个SELECT语句执行后产生两个结果集,想将这两个结果集合并成一个结果集
- 导入数据时出错
- oracle8i temp表空间已有5G被使用,oracle shutdown并重起后5G的空间未释放,怎么办呢?
- windowsXP下oracle8.0.5的启动问题
- 入门问题,什么是 oracle developer suite?
- 在XP上安装ORACLE9i需要装什么补丁吗?为什么我装会出问题
- varchar2(10)和varchar2(4000)有什么区别
- 初学者问一个设计表的问题
可以这么取
SELECT MIN(COL) || '-' || MAX(COL)
FROM A
WHERE COL >= '2001'
AND COL <= '2003'
UNION ALL
SELECT COL
FROM A
WHERE COL = '3001'
UNION ALL
SELECT MIN(COL) || '-' || MAX(COL)
FROM A
WHERE COL >= '3005'
AND COL <= '3006'
UNION ALL
SELECT MIN(COL) || '-' || MAX(COL)
FROM A
WHERE COL >= '3009'
AND COL <= '3014'
SELECT DECODE(MAX(COL),MIN(COL),MIN(COL),MIN(COL) || '-' || MAX(COL)) COL FROM(
SELECT COL,COL-ROWNUM COL1 from A ORDER by COL desc)
GROUP BY COL1;这样也不行啊!
如果是
SELECT DECODE(MAX(COL),MIN(COL),MIN(COL),MIN(COL) || '-' || MAX(COL)) COL FROM(
SELECT COL,COL-ROWNUM COL1 FROM (select col from A order by col desc) ORDER BY COL)
GROUP BY COL1;这样也不行。不知道怎么弄了
FROM (SELECT col, col - rownum col1 FROM (SELECT col FROM a ORDER BY col))
GROUP BY col1
ORDER BY col1LZ可以结贴了
FROM (SELECT COL, COL - ROWNUM COL1
FROM (SELECT DISTINCT COL FROM A ORDER BY COL))
GROUP BY COL1;