Chinaunix首页 | 论坛 | 博客
  • 博客访问: 470221
  • 博文数量: 17
  • 博客积分: 5000
  • 博客等级: 大校
  • 技术积分: 1435
  • 用 户 组: 普通用户
  • 注册时间: 2005-11-09 15:10
文章分类
文章存档

2010年(1)

2009年(3)

2008年(13)

我的朋友

分类: LINUX

2008-05-05 10:49:58



顺便附带一个笔记:
sql语句同结构多表联合查询,合并时如果选择字段太简单数据没有唯一确定行,会把相同记录合并的,如下示例多选几个字段能保证每行都不同就可得到正确结果
   
    for(int i=sday;i                    if(null!=getResultSet("SELECT name FROM sysobjects WHERE (name = '"+getdate(year,month,i)+"')")) {
                        if(stab.length()>0)stab+=" union ";
                        stab+="SELECT subscriber.tel_num, talk_dur, charge,cli, dnis,call_time FROM "+getdate(year,month,i);
                        stab+=" LEFT OUTER JOIN subscriber ON subscriber.tel_num = ";
                        stab+=getdate(year,month,i)+".card WHERE (subscriber.account_num = '"+val+"')";
                        //stab+=" WHERE ("+getdate(year,month,i)+".card = '"+val+"')";
                    }
                }
                if(stab.length()>0){
                    sql="SELECT tel_num,COUNT(talk_dur) AS times,(COUNT(talk_dur)-SUM((talk_dur + 2)/(talk_dur + 1)-1))";
                    sql+=" AS talks, SUM(talk_dur) AS talkdur, SUM((talk_dur + 59) / 60) AS talklen, ";
                    sql+="SUM(charge) AS sumcharge FROM ("+stab+") DERIVEDTBL GROUP BY tel_num order by tel_num";
                }

-----
不同结构多表也可联合搜索某字段是否存在
 sql="select company_worker_info.id,company_expert_info.id,company_monitor_info.id from ";
 sql+="company_worker_info,company_expert_info ,company_monitor_info where (";
 sql+="company_worker_info.workid='"+name+"' and company_worker_info.companyid='"+cid+"') or(";
 sql+="company_expert_info.workid='"+name+"' and company_expert_info.companyid='"+cid+"') or(";
 sql+="company_monitor_info.workid='"+name+"' and company_monitor_info.companyid='"+cid+"')";
阅读(1348) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~