案例討論：Oracle兩表連接
2024-08-29 13:50:12
供稿：網友
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。
