[转载]案例讨论：Oracle两表连接
<P>信息来源: <FONT color=#cc0000>IT专家网</FONT></P><P style="TEXT-INDENT: 2em">Oracle的两表连接怎么实现,原来是SQL的现在想换成oracle的，SQL的如下:
<P> 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</P>
<P> oracle里该怎么办呢?</P>
<P> <STRONG>讨论一:</STRONG></P>
<P> SELECT AREACODE.AREA_NAME,COUNT(*) AS COUNT,</P>
<P> SUM(TIMELEN) AS TOTLEKEEPTIME,</P>
<P> SUM(MONEYS) AS TOTLETIMEFEE,</P>
<P> SUM(REALMONEYS) AS TOTLENEWFEE,</P>
<P> FROM PHONEDATA INNER JOIN AREACODE ON (LEFT(PHONEDATA.ANI,LEN(AREACODE.AREA_CODE)) = AREACODE.AREA_CODE)</P>
<P> WHERE STARTTIME>'2005-12-27 00:00:00'</P>
<P> AND STARTTIME<'2005-12-27 23:59:59'</P>
<P> AND ENDTIME IS NOT NULL</P>
<P> AND USERID LIKE '001136%'</P>
<P> GROUP BY AREACODE.AREA_NAME</P>
<P> ORDER BY TOTLEKEEPTIME</P>
<P> /</P>
<P> 说明:只是不知道你没有限定哪个表的那些字段是不是只有一个表里面有，如果不是，会报错的。</P>
<P> <STRONG>点评:有错误:</STRONG></P>
<P><STRONG> 1.oracle里两表连接不是用INNER JOIN AREACODE ON 而是用外连接LEFT OUTER JOIN .. ON或内连接LEFT in JOIN .. ON</STRONG></P>
<P><STRONG> 2.oracle里没有LEFT函数,取字接数是用length;</STRONG></P>
<P> <STRONG>讨论二:</STRONG></P>
<P> SELECT AREACODE.AREA_NAME,COUNT(*) AS COUNT,</P>
<P> SUM(TIMELEN) AS TOTLEKEEPTIME,</P>
<P> SUM(MONEYS) AS TOTLETIMEFEE,</P>
<P> SUM(REALMONEYS) AS TOTLENEWFEE,</P>
<P> FROM PHONEDATA ,AREACODE</P>
<P> where substring(PHONEDATA.ANI,1,length(AREACODE.AREA_CODE)) = AREACODE.AREA_CODE</P>
<P> and to_date(STARTTIME,'yyyy-mm-dd hr24:mi:ss')>'2005-12-27 00:00:00'</P>
<P> AND to_date(STARTTIME,'yyyy-mm-dd hr24:mi:ss')<'2005-12-27 23:59:59'</P>
<P> AND ENDTIME IS NOT NULL</P>
<P> AND USERID LIKE '001136%'</P>
<P> GROUP BY AREACODE.AREA_NAME</P>
<P> ORDER BY TOTLEKEEPTIME</P>
<P> <STRONG>点评:两表连接那错了，但这地方: substring(PHONEDATA.ANI,1,length(AREACODE.AREA_CODE)) = AREACODE.AREA_CODE是对的;</STRONG></P>
<P> <STRONG>最佳答案:</STRONG></P>
<P> SELECT COUNT(*) AS COUNT, SUM(timelen) AS totlekeeptime, SUM(moneys) AS totletimefee,</P>
<P> SUM(realmoneys) AS totlenewfee, areacode.area_name</P>
<P> FROM phonedata ,areacode</P>
<P> WHERE starttime>'2005-12-27 00:00:00' and starttime<'2005-12-27 23:59:59'</P>
<P> and endtime is not null and userid like '001136'</P>
<P> /*and LEFT(phonedata.ani, length(areacode.area_code)) = areacode.area_code */</P>
<P> and substr(phonedata.ani, 1,length(areacode.area_code)) = areacode.area_code</P>
<P> GROUP BY areacode.area_name order by totlekeeptime</P>
<P> 说明:inner join 直接相等就可以了，left换成substr。<BR></P>
页:
[1]