select 字段A,字段B ,字段C from 表名1 inner join 表名2 on 表名1.字段A = 表名2.字段A group by 表名1.字段A order by 表名1.字段B,表名2.字段C
解决方案 »
- Oracle基本
- 求不同用户下表(表名及结构相同)的查询语句?
- 请教:start with connect by prior 使查询语句效率降低?
- 紧急!服务管理中的oracle的服务不见了,这么还原。
- 日期时间段的sql,怎么写?
- 关于PLSQL无法访问数据库的奇怪问题
- 两表关联怎么取一表中的最早的版本记录的sql语句
- 使用database configuration assistant删除数据库后,managerment server无法刷新
- 此存储过程在oracle中如何实现?
- 这个存储过程为什么编译无法通过
- 求助求助,一条SQL统计 考勤情况,下面的当天缺勤次数,统计不出来
- oracle函数中可以有return为什么还要out参数呢?
上海 2013-10-01 2014-4-01
这2条数据在同一个表中 不存在什么表1, 表2。请仔细斟酌表2实际上就是表1,只不过要自内联下,为了区分order by后的语句...
WITH T1 AS(
SELECT 'ZHANGSAN' T_PERSON_NAME,
'01/JAN/2012' START_DT,
'02/FEB/2013' END_DT
FROM DUAL
UNION ALL
SELECT 'LISI' T_PERSON_NAME, '01/JAN/2012' START_DT, '02/FEB/2013' END_DT
FROM DUAL)
SELECT T_PERSON_NAME,
TO_CHAR(ADD_MONTHS(START_DT, LEVEL - 1), 'yyyy/mm') MON
FROM T1
CONNECT BY LEVEL <= MONTHS_BETWEEN(END_DT, START_DT) + 1
AND T_PERSON_NAME = PRIOR T_PERSON_NAME
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
ORDER BY T_PERSON_NAME, MON;
WITH T1 AS(
SELECT 'ZHANGSAN' T_PERSON_NAME,
'01/JAN/2012' START_DT,
'02/FEB/2013' END_DT
FROM DUAL
UNION ALL
SELECT 'LISI' T_PERSON_NAME, '01/JAN/2012' START_DT, '02/FEB/2013' END_DT
FROM DUAL)
SELECT T_PERSON_NAME,
TO_CHAR(ADD_MONTHS(START_DT, LEVEL - 1), 'yyyy/mm') MON
FROM T1
CONNECT BY LEVEL <= MONTHS_BETWEEN(END_DT, START_DT) + 1
AND T_PERSON_NAME = PRIOR T_PERSON_NAME
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
ORDER BY T_PERSON_NAME, MON;果然牛叉 谢谢
TO_CHAR(ADD_MONTHS(TO_DATE(BGN_YM, 'YYYY-MM-DD'), rnum - 1),
'YYYY-MM') MON
--into TOTALVALUE
from (select '2013-12-01' BGN_YM, '2014-10-01' END_YM, rownum rnum
from ALL_TABLES)
where rownum <= MONTHS_BETWEEN(TO_DATE(END_YM, 'YYYY-MM-DD'),
TO_DATE(BGN_YM, 'YYYY-MM-DD'))
UNION
select 'TEST2' T_NAME,
TO_CHAR(ADD_MONTHS(TO_DATE(BGN_YM, 'YYYY-MM-DD'), rnum - 1),
'YYYY-MM') MON
--into TOTALVALUE
from (select '2013-12-01' BGN_YM, '2014-10-01' END_YM, rownum rnum
from ALL_TABLES)
where rownum <= MONTHS_BETWEEN(TO_DATE(END_YM, 'YYYY-MM-DD'),
TO_DATE(BGN_YM, 'YYYY-MM-DD'))