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。