Oracle两表连接
2007-05-10 12:15:32 来源:WEB开发网Oracle的两表连接怎么实现,原来是SQL的现在想换成oracle的,SQL的如下:
SELECT COUNT(*) AS COUNT, SUM(timelen) AS totlekeeptime, SUM(moneys) AS totletimefee, SUM(realmoneys) AS totlenewfee, areacode.area_name FROM phonedata INNER JOIN areacode ON LEFT(phonedata.ani, LEN(areacode.area_code)) = areacode.area_code WHERE starttime>'2005-12-27 00:00:00' and starttime<'2005-12-27 23:59:59' and endtime is not null and userid like '001136' GROUP BY areacode.area_name order by totlekeeptime
oracle里该怎么办呢?
讨论一:
SELECT AREACODE.AREA_NAME,COUNT(*) AS COUNT,
SUM(TIMELEN) AS TOTLEKEEPTIME,
SUM(MONEYS) AS TOTLETIMEFEE,
SUM(REALMONEYS) AS TOTLENEWFEE,
FROM PHONEDATA INNER JOIN AREACODE ON (LEFT(PHONEDATA.ANI,LEN(AREACODE.AREA_CODE)) = AREACODE.AREA_CODE)
WHERE STARTTIME>'2005-12-27 00:00:00'
AND STARTTIME<'2005-12-27 23:59:59'
AND ENDTIME IS NOT NULL
AND USERID LIKE '001136%'
GROUP BY AREACODE.AREA_NAME
ORDER BY TOTLEKEEPTIME
/
说明:只是不知道你没有限定哪个表的那些字段是不是只有一个表里面有,如果不是,会报错的。
点评:有错误:
1.oracle里两表连接不是用INNER JOIN AREACODE ON 而是用外连接LEFT OUTER JOIN .. ON或内连接LEFT in JOIN .. ON
2.oracle里没有LEFT函数,取字接数是用length;
讨论二:
SELECT AREACODE.AREA_NAME,COUNT(*) AS COUNT,
SUM(TIMELEN) AS TOTLEKEEPTIME,
SUM(MONEYS) AS TOTLETIMEFEE,
SUM(REALMONEYS) AS TOTLENEWFEE,
FROM PHONEDATA ,AREACODE
where substring(PHONEDATA.ANI,1,length(AREACODE.AREA_CODE)) = AREACODE.AREA_CODE
and to_date(STARTTIME,'yyyy-mm-dd hr24:mi:ss')>'2005-12-27 00:00:00'
AND to_date(STARTTIME,'yyyy-mm-dd hr24:mi:ss')<'2005-12-27 23:59:59'
AND ENDTIME IS NOT NULL
AND USERID LIKE '001136%'
GROUP BY AREACODE.AREA_NAME
ORDER BY TOTLEKEEPTIME
点评:两表连接那错了,但这地方: substring(PHONEDATA.ANI,1,length(AREACODE.AREA_CODE)) = AREACODE.AREA_CODE是对的;
最佳答案:
SELECT COUNT(*) AS COUNT, SUM(timelen) AS totlekeeptime, SUM(moneys) AS totletimefee,
SUM(realmoneys) AS totlenewfee, areacode.area_name
FROM phonedata ,areacode
WHERE starttime>'2005-12-27 00:00:00' and starttime<'2005-12-27 23:59:59'
and endtime is not null and userid like '001136'
/*and LEFT(phonedata.ani, length(areacode.area_code)) = areacode.area_code */
and substr(phonedata.ani, 1,length(areacode.area_code)) = areacode.area_code
GROUP BY areacode.area_name order by totlekeeptime
说明:inner join 直接相等就可以了,left换成substr。
- ››oracle 恢复误删除的表和误更新的表
- ››Oracle分页查询排序数据重复问题
- ››Oracle创建dblink报错:ORA-01017、ORA-02063解决
- ››Oracle 提高SQL执行效率的方法
- ››Oracle 动态查询,EXECUTE IMMEDIATE select into...
- ››Oracle 11g必须开启的服务及服务详细介绍
- ››oracle性能34条优化技巧
- ››oracle数据库生成随机数的函数
- ››Oracle 数据库表空间容量调整脚本
- ››oracle单库彻底删除干净的方法
- ››Oracle创建表空间、创建用户以及授权、查看权限
- ››oracle 中 UPDATE nowait 的使用方法
赞助商链接