SQL之总结（一）

                                                  导游通项目之总结SQL

1.选择前面的某几个

          oracle：  select * from tb_article where rownum<5 order by article_id         (选择4列数据)

          MySQL： select * from tb_article where limit 5

          SQL:       select top 4 * from tb_article

 

2.选择某列的最大值：

      select Max（article_id） from tb_article where article_id>190

 

3.聚合函数的问题  分组的问题解决  注意下面的两个的区别在group by 里面，（感谢梁工的帮助！）

select d.POSITION,substr(d.PHOTO, 2,length(d.PHOTO)-1) photo,d.SEX,d.USER_NAME,c.ENT_NAME,c.CONTACTER,c.CONTACT_PHONE,d.PHONE,SUM(b.REPLY_COUNT) REPLY_COUNT,SUM(b.VISIT_COUNT) VISIT_COUNT
from TB_USER a,TB_ARTICLE b,TB_TRAVEL_AGENCY c,TB_TRAVELAGENCY_USER d
where c.AGENT_CODE=a.USER_ID and a.USER_ID=b.CREATE_USER and b.SECTION_ID=32
and d.USER_ID=a.USER_ID and a.USER_ID =84
GROUP BY d.POSITION,substr(d.PHOTO, 2,length(d.PHOTO)-1),d.SEX,d.USER_NAME,c.ENT_NAME,c.CONTACTER,c.CONTACT_PHONE,d.PHONE

 

select  c.GNAME,nvl(c.LVLNO,'&nbsp') LVLNO,nvl(c.PHONE,'&nbsp') PHONE,ceil(MONTHS_BETWEEN(sysdate, c.sendtime)/12) workTime,substr(c.GIMG, 2,length(c.GIMG)-1) gimg,c.SEX ,SUM(b.REPLY_COUNT) REPLY_COUNT,SUM(b.VISIT_COUNT) VISIT_COUNT
      from TB_USER a,TB_ARTICLE b,TB_GUIDE c 
     where a.card_no=c.idcard_no and b.CREATE_USER=a.USER_ID and b.SECTION_ID=32 and a.USER_ID=84
GROUP BY c.SEX,ceil(MONTHS_BETWEEN(sysdate, c.sendtime)/12),c.GNAME,substr(c.GIMG, 2,length(c.GIMG)-1),c.GNAME,c.LVLNO,c.PHONE