100分解决Hibernate中的left outer join问题,在线等 [问题点数:100分,结帖人zxm_dgcstars]

Bbs5
本版专家分:3500
结帖率 100%
Bbs5
本版专家分:3500
Bbs5
本版专家分:3045
Bbs2
本版专家分:294
Bbs5
本版专家分:3500
Bbs3
本版专家分:526
Bbs6
本版专家分:6171
Bbs5
本版专家分:3500
Bbs2
本版专家分:294
Bbs2
本版专家分:294
Bbs5
本版专家分:3884
Bbs1
本版专家分:0
Bbs2
本版专家分:196
Bbs2
本版专家分:196
Bbs2
本版专家分:196
left outer join 写法
ruhuo 表结构 (入货表) rnid theTypeName operation supplyPermoney goodsNum rn1 汽水 入货 1.5 <em>100</em> rn..................... rnrntypeTable 表结构 (名称表) rnid theTypeName rn1 汽水 rn2 水杯 rnrn我想统计名称表里面所有品种各自的进货数量并且取最近一次进货价格作为单价,我如下写法: rnrnrnSELECT t.theTypeName, a.calNum,b.supplyPermoney rnFROM typetable t LEFT OUTER JOIN rn (SELECT theTypeName, SUM(goodsnum) AS calNum rn FROM ruhuo rn WHERE operation = '入货' AND supplyPermoney > 0 rn GROUP BY theTypeName) a ON t.theTypeName = a.theTypeName rn<em>left</em> <em>outer</em> <em>join</em> (select top 1 theTypeName,supplyPermoney from ruhuo where theTypeName=t.theTypeName and operation = '入货' AND supplyPermoney > 0 order by id desc) b on b.theTypeName=t.theTypeName rnrn提示列前缀 ‘t’与查询中所有的表名或者名称不匹配 rn
left outer join 与INNER JOIN
今天碰到个<em>问题</em>.rnselect a.issue_type,a.issue_req_no,b.issue_req_nornfrom scb010t as a rn<em>left</em> <em>outer</em> <em>join</em> sda010t brnon a.issue_req_no=b.issue_req_nornand a.issue_type not in ('99')rn结果是60159条记录.正好是select * from scb010t的记录rn本来我是这样理解的.用and a.issue_type not in ('99')这个去限制SCB010T表使他只显示除'99'以外的记录.结果还是有这个99的记录存在.如果用这样写成rnselect a.issue_type,a.issue_req_no,b.issue_req_nornfrom scb010t as a rninner <em>join</em> sda010t brnon a.issue_req_no=b.issue_req_nornand a.issue_type not in ('99')rn或rnselect a.issue_type,a.issue_req_no,b.issue_req_nornfrom scb010t as a rn<em>left</em> <em>outer</em> <em>join</em> sda010t brnon a.issue_req_no=b.issue_req_nornwhere a.issue_type not in ('99')rn的话就可以,是不是LEFT <em>outer</em> <em>join</em> <em>问题</em>.能不能帮我解答解答?
请问 left joinleft outer join 的区别
请问 <em>left</em> <em>join</em> 和 <em>left</em> <em>outer</em> <em>join</em> 的区别
left outer join求助
有一个货品资料表goods,一个价格表goodsprice,针对一个客户会对所有货品生成一套价格rn所以我希望即使货品没有价格,也要显示成0。我想先从价格表中提取该客户的价格清单,然后再和货品资料表连接,使用如下语句,但总是提示语法错误,请帮忙指点。谢谢。rnstrParam.Format( SELECT [goods].group, [goods].name, [goodsprice].price FROM ( goods LEFT OUTER JOIN ( SELECT * FROM goodsprice WHERE [goodsprice].clientid = '%s' ) ON [goods].group = [goodsprice].group AND [goods].name = [goodsprice].goods )", strClientID );rn
left outer join?
A:rn id destmobile orgmobilern 1 13845645666rn 2 13845645666rn 3 13564853445rn 4 13564853445rn 5 13564852555rn 6 13456456456rn 7 13456456456rnB:rn mobile cityrn 1384564 0766rn 1356485 0745rn 1345645 0733rnrn大家好,在上面两表中,我想根据A左外连接B,条件为:SUBSTR(a.DESTMOBILE,1,7)=b.mobile or SUBSTR(a.ORGMOBILE,1,7)=b.mobile,结果:a.*,b.city请问该SQL要怎样写?rn理想结果:rn id destmobile orgmobile cityrn 1 13845645666 0766 rn 2 13845645666 0766rn 3 13564853445 0745rn 4 13564853445 0745rn 5 13564852555 0745rn 6 13456456456 0733rn 7 13456456456 0733rnrnrnrn
left outer join 格式问题
select a.theMisid,de.missionName from theLockMis as a <em>left</em> <em>outer</em> <em>join</em> select * from missionArrange as de on a.theMisid=de.idrnrnrn提示 select 附近语法错误
SQL left outer join问题
table arnName Specrn001 1;2;3;4;5;6rn002 1;2;3;4;5;6rn003 1;3;4;5;6rnrntable brnrnid valuern1 a001rn2 a002rn3 a003rn4 a004rn5 a005rn6 a006rnrna.Spec 对应 b.id,可以一对多,';’隔开rnrn怎么查询到rnName=001,所对应的value的数据、rnrn即得到rnName valuern001 a001rn001 a002rn001 a003rn001 a004rn001 a005rn001 a006rnrn
在线等待Hibernate outer join问题
订单表(Order):product_id,customer_id,order_timern产品表(product):id,product_name,product_typernrn找出没被订单订过的产品的SQL语句:rnselect a.product_name,b.customer_id from product a <em>left</em> <em>outer</em> <em>join</em> order b on a.id=b.product_id rnwhere b.customer_id is nullrnrn假定product与order的hbm.xml中没有设置双向和单向关联,怎么转化为HQL?
left join 和Left outer joinjoin有什么区别??
谢谢.
Join (left,inner,right outer)
[code=SQL]主表:MID Namern 1 op1rn 2 op2rn 3 op3rn 4 op4rn子表1:MID SIDrn 1 1rn 1 2rn 2 1rn 2 2rn 2 3rn 3 2rn 3 1rn子表2:MID UIDrn 1 2rn 2 2rn 1 1rn写一个视图把主表,子表1,子表2的记录全部显示出来该如何写[/code]rn
请教left outer join和no in的不同
not Inrn[code=SQL]select * from table where id not in (select top <em>100</em> id from table order by id desc)[/code]rn<em>left</em> <em>outer</em> <em>join</em>rn[code=SQL]rnselect * from table <em>left</em> <em>outer</em> <em>join</em> (select top <em>100</em> id as tId from table order by id desc) t on t.tId = table.id where t.tId is null[/code]rnrn两句的效果是一样的,效率上来说哪个比较好..手上没有大数据库进行测试,有没人帮忙看看
请教left outer join 写法
ruhuo 表结构 (入货表)rnid theTypeName operation supplyPermoney goodsNumrn1 汽水 入货 1.5 <em>100</em>rn.....................rnrntypeTable 表结构 (名称表)rnid theTypeNamern1 汽水rn2 水杯rnrn我想统计名称表里面所有品种各自的进货数量并且取最近一次进货价格作为单价,我如下写法:rnrnrnSELECT t.theTypeName, a.calNum,b.supplyPermoneyrnFROM typetable t LEFT OUTER JOINrn (SELECT theTypeName, SUM(goodsnum) AS calNumrn FROM ruhuorn WHERE operation = '入货' AND supplyPermoney > 0rn GROUP BY theTypeName) a ON t.theTypeName = a.theTypeNamern<em>left</em> <em>outer</em> <em>join</em> (select top 1 theTypeName,supplyPermoney from ruhuo where theTypeName=t.theTypeName and operation = '入货' AND supplyPermoney > 0 order by id desc) b on b.theTypeName=t.theTypeNamernrn提示列前缀 ‘t’与查询中所有的表名或者名称不匹配rn
谁用过'left outer join'?
特别慢,要用好几分钟才完成。rnhttp://community.csdn.net/Expert/topic/3414/3414791.xml?temp=.3467676
hibernate left outer join 出错 Path expected for join!
查询语句是:rnQuery query=session.createQuery("select s.studentName from Student as s <em>left</em> <em>outer</em> <em>join</em> StuResult as sr where s.studentId=8");rn报错:rnException in thread "main" org.<em>hibernate</em>.hql.ast.QuerySyntaxException: Path expected for <em>join</em>! [select s.studentName from gjs.pojo.Student as s <em>left</em> <em>outer</em> <em>join</em> StuResult as sr where s.studentId=8]rn at org.<em>hibernate</em>.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)rn at org.<em>hibernate</em>.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:225)rn at org.<em>hibernate</em>.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:158)rn at org.<em>hibernate</em>.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:109)rn at org.<em>hibernate</em>.engine.query.HQLQueryPlan.(HQLQueryPlan.java:75)rn at org.<em>hibernate</em>.engine.query.HQLQueryPlan.(HQLQueryPlan.java:54)rn at org.<em>hibernate</em>.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:71)rn at org.<em>hibernate</em>.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)rn at org.<em>hibernate</em>.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)rn at org.<em>hibernate</em>.impl.SessionImpl.createQuery(SessionImpl.java:1583)rn at gjs.dao.Test.main(Test.java:62)rnCaused by: Path expected for <em>join</em>!rn at org.<em>hibernate</em>.hql.ast.HqlSqlWalker.createFromJoinElement(HqlSqlWalker.java:307)rn at org.<em>hibernate</em>.hql.antlr.HqlSqlBaseWalker.<em>join</em>Element(HqlSqlBaseWalker.java:3268)rn at org.<em>hibernate</em>.hql.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3060)rn at org.<em>hibernate</em>.hql.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:2938)rn at org.<em>hibernate</em>.hql.antlr.HqlSqlBaseWalker.fromClause(HqlSqlBaseWalker.java:688)rn at org.<em>hibernate</em>.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:544)rn at org.<em>hibernate</em>.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:281)rn at org.<em>hibernate</em>.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:229)rn at org.<em>hibernate</em>.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:218)rn ... 9 morern很困惑,看了网上很多例子也这样写,不知道为什么我的就总报这错,希望高手指点。
left outer join 查询疑问
我有两张表,表名分别是T_SINGLE_PERSON_INFO 和 T_RENTAL_PERSON_INFOrnrn这两张表的自段完全一样,都含有 ID, NAME,CERTIFICATE_NO,PHONE字段rnrnT_SINGLE_PERSON_INFO 中有一条数据rnID, NAME, CERTIFICATE_NO, PHONErn1 张三 530127195309165930 82345689rnrnT_RENTAL_PERSON_INFO 中有一条数据rnID, NAME, CERTIFICATE_NO, PHONErn1 李四 511525198911227313 85319625rnrn现在我用<em>left</em> <em>outer</em> Join 对这两张表进行关联查询rn rnsql 语句如下rn rnselect a.NAME,a.PHONE,b.NAME,b.PHONErnfrom T_SINGLE_PERSON_INFO as a rn<em>left</em> <em>outer</em> <em>join</em> T_RENTAL_PERSON_INFO as b on a.ID = b.ID rnrn查询出的结果是:rna.NAME, a.PHONE, b.NAME, b.PHONErn张三 82345689 张三 82345689rnrn不知道是为什么?请指点!rnrnrnrn
left outer join 无效字符
[code=SQL] select ta.account_id ,ta.corpname,tfv.pk_id pk_id,tfv.state,pd.name area rn from t_account tarn <em>left</em> <em>outer</em> <em>join</em> t_freeEvaluationApply tfv on ta.account_id = tfv.fk_account_idrn <em>left</em> <em>outer</em> <em>join</em> pub_district pd on ta.fk_province_id =pd.pk_id[/code]rn为啥会酱紫?把<em>left</em> <em>outer</em> <em>join</em>改成<em>join</em>不会报错,但是就不能查到我要的数据啦。。。rn求高手帮忙。。。
请教 left outer join 的写法
我有以下2个表,1个是machineTypeName 配置表,另外一个是ruhuo 出入仓表rnrnmachineTypeName 配置表 (用于记录每个部门对于某机号的设备领取各种零配件的标配数量)rnmachineCode, tempTheTypeName, dept amount (标配数量)rnAAA 螺丝 开拓部 500rnAAA 铁板 测试部 5rnAAA 冲击钻 开发部 2rnrnrnruhuo 出入仓表 rnmachineCode dept theTypeName goodsnum operatingrnAAA 开拓部 螺丝 <em>100</em> 出货rnXXX 研发部 交换器 5 入货rnCCC 文娱部 饮料 10 出货rnrnrn那么我现在想得出一个这样的表,参考以下格式,实际上就是在配置表上面增加2个实时统计的字段,我尝试使用<em>left</em> <em>outer</em> <em>join</em>的形式写,但是结果不对,请教该如何写才对rn(需要同时考虑出入货表里面的退料情况,即operating=入货的时候),rnrn 实际已领取数量 剩余可以领取数量rnmachineCode tempTheTypeName dept amount nums leaveNumsrnAAA 螺丝 开拓部 500 400 <em>100</em>rnAAA 铁板 测试部 5 3 2rnAAA 冲击钻 开发部 2 0 2 rnrnrnrn
NOT IN , left outer join 丢失数据!!
1.SELECT count(c.org_nummer) FROM customers crnLEFT OUTER JOIN company b ON c.org_nummer=b.org_nummerrn WHERE (c.orgtype='A1' OR c.orgtype='A2' ORrnc.orgtype='A0')rn AND c.AVSLUTT < 1 得到总和结果为:302 177rnrn2.SELECT count(c.org_nummer) FROM customers crnLEFT OUTER JOIN company b ON c.org_nummer=b.org_nummerrn WHERE (c.orgtype='A1' OR c.orgtype='A2' ORrnc.orgtype='A0') AND bc.bransjeID INrn(75121,75122,55523,80423,85321,80<em>100</em>,80210,80220,80302,80303,80309,80301rn,80304,1125,80426,1122,92721,2014,80410,60220,50500,52260,51310,52112,85rn313,85140,85120,80423,85321,80210,80220) AND c.AVSLUTT < 1 . rn结果为:10 993rn3.SELECT count(c.org_nummer) FROM customers crnLEFT OUTER JOIN company b ON c.org_nummer=b.org_nummerrn WHERE (c.orgtype='A1' OR c.orgtype='A2' ORrnc.orgtype='A0') AND bc.bransjeID NOT INrn(75121,75122,55523,80423,85321,80<em>100</em>,80210,80220,80302,80303,80309,80301rn,80304,1125,80426,1122,92721,2014,80410,60220,50500,52260,51310,52112,85rn313,85140,85120,80423,85321,80210,80220) AND c.AVSLUTT < 1 .rn结果为:203 784 。rn按照not in 和in 的理解,应该第2句和第3句的和等于等一个,但是 2+3=214 784 。丢出了数据,大家帮着分析一下,<em>问题</em>出在了哪里?rnrn
hibernateleft join on问题
我有2个表,一个shop 还有一个shopcoupon 2者一对多关联关系,shop里面有2个数据,shopcoupon有且只有一条数据对应shop表,我用<em>left</em> <em>join</em> on 语句查询数据,按理说应该返回2条数据吧?,但是只返回了1条数据,而且好像还是2个对象类型的,我打印出来时2个实体对象类型的,是怎么回事啊?rn相关代码:rnshop.hbm.xmlrn-------------------------------------rnrnrnrnrn rn rn rn rn rn rn rn rnrn 。。。。中间省略了一些rnrn rn rn N-新建, A-已审核, S-暂停, D-已删除rn rn rn rn rn rn rn rn rn rn rnrn-----------------------------------rnshopcoupon.hbm.xmlrn-----------------------------------rnrnrnrnrn rn rn rn rn rn rn rn 0-禮品rn rn rn 。。。中间省略了一些rn rn rn rn rnrn----------------------------------------------------------rnshop------------实体类rn-------------------------------------------rnpackage com.<em>join</em>t.<em>join</em>t_ddw.entity;rnrnimport java.util.HashSet;rnimport java.util.Set;rnrnrnpublic class DdwShop implements java.io.Serializable rnrn // Fieldsrnrn private Integer shopid;rn private String shopname;rn private String phone;rn private String address;rn private String email;rn private String website;rn private String reArchitecture;rn private String shopinfo;rn private String hours;rn private String recommend;rn private Short chargeMod;rn private String spending;rn private Integer uid;rn private String reason;rn private String buslines;rn private String ifPark;rn private String ifBox;rn private String ifTakeaway;rn private String ifSupper;rn private String ifWirelessnet;rn private Integer areaid;rn private Integer regionid;rn private String shopimg;rn private Integer shopgrade;rn private String dishesid;rn private String shoptypeid;rn private Integer rate1;rn private Integer rate2;rn private Integer rate3;rn private Integer rate4;rn private Integer rate5;rn private Integer rate6;rn private Integer view;rn private String status;rn private String mapaddress;rn private Set shopcoupons=new HashSet();rnrn //省略get,set方法rnrn-------------------------------------rnshopcoupon-----实体类rn-------------------------------------rnrnpackage com.<em>join</em>t.<em>join</em>t_ddw.entity;rnrnimport java.util.Date;rnrn/**rn * DdwShopcoupon entity.rn * rn * @author MyEclipse Persistence Toolsrn */rnrnpublic class DdwShopcoupon implements java.io.Serializable rnrn // Fieldsrnrn private Integer couponid;rn private Integer shopid;rn private Integer classid;rn private Integer price;rn private String title;rn private String content;rn private String couponimg;rn private String logistics;rn private Date postdate;rn private Date expirydate;rn private String discount;rn //省略get,set方法rnrn-------------------------------------------rn方法rn------------------------------------rnpublic List findShopSearch()throws Exception rn rn String sql="from DdwShop s "rn +"<em>left</em> <em>join</em> s.shopcoupons sc where ";rn rn return ddwShopDao.findShop(sql);rnrn--------------------------------------------rn调用rn--------------------------------------------rn List oldList=ddwShopService.findShopSearch();rn rn rn for (int i = 0; i < oldList.size(); i++) rn System.out.println("-------------------");rn Object[] oldObj=(Object[])oldList.get(i);rn for (int j = 0; j < oldObj.length; j++) rn System.out.println(oldObj[j]);rn rn rn-------------------------------rn打印结果rn-------------------------------rnHibernate: rn selectrn ddwshop0_.shopid as shopid16_0_,rn shopcoupon1_.couponid as couponid17_1_,rn ddwshop0_.shopname as shopname16_0_,rn ddwshop0_.phone as phone16_0_,rn ddwshop0_.address as address16_0_,rn ddwshop0_.email as email16_0_,rn ddwshop0_.website as website16_0_,rn ddwshop0_.re_architecture as re7_16_0_,rn ddwshop0_.shopinfo as shopinfo16_0_,rn ddwshop0_.hours as hours16_0_,rn ddwshop0_.recommend as recommend16_0_,rn ddwshop0_.charge_mod as charge11_16_0_,rn ddwshop0_.spending as spending16_0_,rn ddwshop0_.uid as uid16_0_,rn ddwshop0_.reason as reason16_0_,rn ddwshop0_.buslines as buslines16_0_,rn ddwshop0_.if_park as if16_16_0_,rn ddwshop0_.if_box as if17_16_0_,rn ddwshop0_.if_takeaway as if18_16_0_,rn ddwshop0_.if_supper as if19_16_0_,rn ddwshop0_.if_wirelessnet as if20_16_0_,rn ddwshop0_.areaid as areaid16_0_,rn ddwshop0_.regionid as regionid16_0_,rn ddwshop0_.shopimg as shopimg16_0_,rn ddwshop0_.shopgrade as shopgrade16_0_,rn ddwshop0_.dishesid as dishesid16_0_,rn ddwshop0_.shoptypeid as shoptypeid16_0_,rn ddwshop0_.rate1 as rate27_16_0_,rn ddwshop0_.rate2 as rate28_16_0_,rn ddwshop0_.rate3 as rate29_16_0_,rn ddwshop0_.rate4 as rate30_16_0_,rn ddwshop0_.rate5 as rate31_16_0_,rn ddwshop0_.rate6 as rate32_16_0_,rn ddwshop0_.view as view16_0_,rn ddwshop0_.status as status16_0_,rn ddwshop0_.mapaddress as mapaddress16_0_,rn shopcoupon1_.shopid as shopid17_1_,rn shopcoupon1_.classid as classid17_1_,rn shopcoupon1_.price as price17_1_,rn shopcoupon1_.title as title17_1_,rn shopcoupon1_.content as content17_1_,rn shopcoupon1_.couponimg as couponimg17_1_,rn shopcoupon1_.logistics as logistics17_1_,rn shopcoupon1_.postdate as postdate17_1_,rn shopcoupon1_.expirydate as expirydate17_1_,rn shopcoupon1_.discount as discount17_1_ rn fromrn <em>join</em>t_ddw.ddw_shop ddwshop0_ rn <em>left</em> <em>outer</em> <em>join</em>rn <em>join</em>t_ddw.ddw_shopcoupon shopcoupon1_ rn on ddwshop0_.shopid=shopcoupon1_.shopid rn-------------------rncom.<em>join</em>t.<em>join</em>t_ddw.entity.DdwShop@1b258farncom.<em>join</em>t.<em>join</em>t_ddw.entity.DdwShopcoupon@140d415
谁能给我讲解下 left join, right join ,left outer join,right outer join 的用法,区别
关于他们的越详细的资料越好!
请问:inner joinleft outer join 的区别
请问:inner <em>join</em> 和 <em>left</em> <em>outer</em> <em>join</em> 的区别rnrn谢谢!
请问inner join/left outer join/right outer join/full outer join四者的区别
请问rninner <em>join</em>rn<em>left</em> <em>outer</em> <em>join</em>rnright <em>outer</em> <em>join</em>rnfull <em>outer</em> <em>join</em>rn四者的区别rn作用的区别rn各自是什么时候用到的rn
请教left outer join的查询优化的问题
请教一个数据库查询优化的<em>问题</em>rnrn--以下查询代码执行时间18秒rnselect bb.*,b.ApplyNo,b.applyStatus from releaseman bb <em>left</em> <em>outer</em> <em>join</em> flowman b on bb.id=b.bApplyId and wherern (bb.applier= 'jacky' or (bb.id in(select distinct bApplyId from flowman a,ta_flowdetail b where a.applyNo=b.applyNo rnand b.flowowner='jacky' )))rnrn--去掉where条件中的其中一个or条件 即去掉 bb.applier= 'jacky' ,执行速度就非常快,不到1秒rnselect bb.*,b.ApplyNo,b.applyStatus from releaseman bb <em>left</em> <em>outer</em> <em>join</em> flowman b on bb.id=b.bApplyId and wherern bb.id in(select distinct bApplyId from flowman a,ta_flowdetail b where a.applyNo=b.applyNo rnand b.flowowner='jacky')rnrn请问为什么呢?怎样优化呢?求数据库专家给予指导,谢谢
关于 oracle 8 中的 left outer join
在oracle 9中,<em>left</em> <em>outer</em> <em>join</em> 语法是被支持的,但在oracle 8中是不被支持的,但有(+)语法相对应,小弟有个9中的语句:rnselect A.F1,A.F2,B.F3,C.F4 from A rn<em>left</em> <em>outer</em> B on A.FX=B.FXrn<em>left</em> <em>outer</em> C on A.FX=C.FXrnrn问: 在oracle 8中怎么表达?企盼中
LEFT OUTER JOIN 多个表问题
[b]表spxx[/b] 商品信息rnid spmcrn<em>100</em>0001 统一冰红茶rn<em>100</em>0002 康师傅绿茶rnrn[b]表xssp[/b] 销售商品rnid spid sl dj zjern1 <em>100</em>0001 1 2.5 2.5rnrn[b]表jhsp[/b] 采购商品rnid spid sl dj zjern1 <em>100</em>0001 1 2 2rn2 <em>100</em>0001 1 2 2rn3 <em>100</em>0001 1 2 2rnrnrnSQL:SELECT spxx.spmc AS 商品名称, xssp.sl AS 销售数量, jhsp.sl AS 采购数量rnFROM spxx LEFT OUTER JOINrn jhsp ON spxx.id = jhsp.spid LEFT OUTER JOINrn xssp ON spxx.id = xssp.spidrnrn查询结果:rn[img=http://www.camei.net/camei_admin/edit/UploadFile/2008315131158698.jpg][/img]rnrn为什么销售商品数量是1,查询却有三次,如何得到下面的结果:rn[img=http://www.camei.net/camei_admin/edit/UploadFile/2008315131626507.jpg][/img]rnrn
关于left outer join和*=互换的问题
strSQL = "select top "+RecordNum+" a.*"+strFiledName+" from t_db_change a,"+TableName+" where 1=1"+strSQL1;rn有这么条语句,问下怎样正确用<em>left</em> <em>outer</em> <em>join</em>替换
一个关于left outer join问题
以下是系统中的两个视图联合起来查询:rnSELECT TOP (<em>100</em>) PERCENT t1.Fdate, t1.FCheckFlag, t1.FHookFlag, t1.FCostID, t1.FTrade, t1.FBillNo, t1.FDeptIDt1, t1.FEmpIDt1, t1.FCurrencyID, rn t1.FRSCBillInterIDt, t1.Fmodel, t1.Fauxqty, t1.Fauxprice, t2.Fauxprice AS FCostPrice, t1.F_Amount - t1.FTaxAmount AS FTaxAmount, rn t1.Fauxqty * t2.Fauxprice AS FCostamount, (t1.F_Amount - t1.FTaxAmount) - t1.Fauxqty * t2.Fauxprice AS FProfit, t1.FSourceBillNo, t1.FOrderBillNo, rn t2.FOrderEntryIDrnFROM AIS20110927142957.dbo.vwICBill_43 AS t1 LEFT OUTER JOINrn AIS20110927142957.dbo.vwICBill_8 AS t2 ON t2.FbillNo = t1.FSourceBillNo AND t2.Fmodel = t1.Fmodel AND t2.FOrderBillNo = t1.FSourceBillNo AND rn t2.FOrderEntryID = t1.ForderEntryIDrn结果出来的数据,只要是t2视图中的数据就出不来,不知道怎么回事?确定都有数据。
关于left outer join问题(急)
我想实现的是:有三张表,table_pd,table_kcnow,table_kclastrntable_pd: lj_code,in_amount,out_amountrntable_kcnow:lj_code,now_amountrntable_kclast:lj_code,last_amountrn这三张表都有相同的字段lj_code,通过该字段进行关联,关联后的select的字段包括:rnlj_code,in_amount,out_amount,now_amount,last_amountrn<em>问题</em>是:table_pd中存在的lj_code可能在table_kcnow,table_kclast中不存在,此时对应的now_amount,last_amount应设为0,我本来准备用<em>left</em> <em>outer</em> <em>join</em>,但是好象<em>left</em> <em>outer</em> <em>join</em> 只能连接两个表,那么连接三个表该怎么做呢?rnrnrn
left outer join与分类统计的问题
定单orderrnId ProductCode Price piecern1 A01 105.10 1rn2 B01 125.50 2rnrn发货表 SendProductrnorderId Serialrn1 <em>100</em>002rn2 <em>100</em>003rn2 <em>100</em>004rnrn产品信息表ProductionrnProductCode ProductNamernA01 水果刀rnB01 钥匙扣rnrn库存表rnProductCode Serial CostrnA01 <em>100</em>002 20.00rnB01 <em>100</em>003 30.00rnB01 <em>100</em>004 31.00rnrnrn小需要这样的报表rn定单号 产品名称 销售额 件数 成本 产品编号 rn1 水果刀 105.10 1 20.00 <em>100</em>002rn2 钥匙扣 251.00 2 30.00 <em>100</em>003rn2 钥匙扣 31.00 <em>100</em>004rn------------------------------------------------------------------------------rn合计 351.10 3 80.00rnrnrn如果sql语句中 from order a <em>left</em> <em>outer</em> <em>join</em> SendProduct b on a.Id=b.orderId得到的结果就是rn定单号 产品名称 销售额 件数 成本 产品编号 rn1 水果刀 105.10 1 20.00 <em>100</em>002rn2 钥匙扣 251.00 2 30.00 <em>100</em>003rn2 钥匙扣 251.00 2 31.00 <em>100</em>004rn------------------------------------------------------------------------------rn合计 607.10 5 81.00rnrn这样的结果明显是不对的,如何<em>解决</em>这个<em>问题</em>。rnrn定单多了,还有对不同的产品进行分类统计。rnrn
最简单的 left outer join 问题??
表 a (商品信息表)rn rn id namern 1 arnrn 2 brnrn 3 crnrnrn 4 drnrnrn 5 ernrn表 b(出库表)rnrn id name rqrn rn 1 a 20010101rnrn --------------------------------------rnrn查询结果:rnrn id name rqrn rn 1 a 20010101rnrn 2 b nullrnrn 3 c nullrnrn 4 d nullrnrn 5 e nullrnrn--------------------------------------------------rnrnrnselect from 商品信息表 a <em>left</em> <em>outer</em> <em>join</em> 出库表 b on a.id=b.id where rq='20010101'rnrn这样写查询结果不对,该怎样写?rn
hibernate中的left joinleft join fetch的问题
我的Question和Answer是一对多的关系,我想做一个查询,比如 rn允许输入<em>问题</em>的标题,<em>问题</em>提问者,答案的解答时间,答案的解答者,就可以进行关联查询,比如输出如下: rnrn <em>问题</em>标题 <em>问题</em>提问者 答案的解答时间,答案的解答者 rnrn由于一个<em>问题</em>有多个答案和解答者,而有的<em>问题</em>可能暂时没有解答者,因此采用的是<em>left</em> <em>join</em>连接, rn并且如果用户输入答案的解答时间的话,则只需要列出对于这个解答时间的‘一方“的<em>问题</em>就可以了, rnrnrn比如<em>问题</em>A有5个解答,解答时间都在2010-4-17日,则只需要列出 rnrn <em>问题</em>标题 <em>问题</em>提问者 rn A B rn我现在是这样写的: rnString hql = " select q from Question as q <em>left</em> <em>join</em> fetch q.Answer a where 1=1"; rnString c_hql = "select count(q) from Question q <em>left</em> <em>join</em> fetch q.Answer a where 1=1"; rnrn if(!"".equals(<em>问题</em>标题)&& null!=<em>问题</em>标题) rn rnhql=hql+" and q.<em>问题</em>标题= '"+<em>问题</em>标题+"' "; rnc_hql=c_hql+" and q.<em>问题</em>标题 = '"+<em>问题</em>标题+"' "; rn rnrnif((!"".equals(beginTime)&&null!=beginTime)&&(!"".equals(endTime)&&null!=endTime)) rn rnrn hql=hql+" and a.createdate >= to_date('"+beginTime+"','yyyy-MM-dd HH24:mi:ss') and a.createdate= to_date('"+beginTime+"','yyyy-MM-dd HH24:mi:ss') and a.createdate; rn rnrn 但出现了错误,说: rnquery specified <em>join</em> fetching, but the owner of the fetched association was not present in the select list rnrn为什么呢?要如何搞呀 rn
LEFT OUTER JOIN 和 LEFT JOIN是一样的意思吗?
如上
sql left outer join 排重问题
[code=SQL]rnrnselect table1.* from table1 <em>left</em> <em>outer</em> <em>join</em> table2 on table1.callnumber=table2.callnumberrnrn[/code]rnrn此时,如果table2 中有重复记录 那么这条语句就会取出重复的记录,请问在使用 <em>left</em> <em>outer</em> <em>join</em> 时如何排重重复的记录? 用DISTINCT 不合适,TABLE1的字段太多了,还包含ID!
请说明left joinleft outer join的区别
请说明<em>left</em> <em>join</em> 和<em>left</em> <em>outer</em> <em>join</em>的区别
hibernateleft join 与sql的left join的区别
sql中的 <em>left</em> <em>join</em> 左连接保留前面表的所有记录,后表中没有的补nullrnhql中的 <em>left</em> <em>join</em> 连接子表查询,如子表不符合条件,主表也显示所有吗?那还查什么?rnrn之间是什么区别?
inner joinleft join 、right joinouter join之间的区别
inner <em>join</em>、 <em>left</em> <em>join</em> 、right <em>join</em>、 <em>outer</em> <em>join</em>之间的区别
比较难的left join 问题100
SELECT b1.name FROM b1 LEFT JOIN b2 on b1.name = b2.name WHERE b2.name IS NULL AND b2.type = 'asdf'rnrn我是根据faq中的<em>left</em> <em>join</em>的思路写的select语句。rn目的是要取出b1的name在b2的name中没有的那些记录。rn但是<em>问题</em>在于我还需要增加一个b2.type = 'asdf'的限制条件。rn如何写法?
HIBERNATE left join fetch问题
例如From Qiyue q <em>left</em> <em>join</em> fetch q.yewu <em>left</em> <em>join</em> fetch q.yewu.jituan where ... rn这样的连接语句会把yewu.jituan表中所有字段全都取出来,可是用到的只是其中的某一或者某些字段,例如我只要yewu里的yewumingcheng ,jituan里的jituanmingcheng字段应该如何改这个HQL语句?取全字段的速度实在太慢,越到关系的底层速度越来越慢。
sqlanywhere中是不是没有 left join outer join
只有inner <em>join</em> 和<em>join</em> ?rnrn感觉不可思议,也没有书查看
关于hibernateleft join
我有表1 user 表2 userinfo rnuser表中有3行数据 userinfo 有2行数据 关联外键了rnrnString hql="select u from TUserinfo as ui <em>left</em> <em>join</em> ui.TUser as u "; rnrn为什么只能查到两表都有的数据,rn而user表中 未关联的一行数据查不到,rnrn是否是我配置文件有错?rnrnrn rn rnrnrnrn rn rn rn rn rn
hibernate left join 问题,,,,求助。。。。
我数据库是orcale,我写了个sql语句,在orcale客户端可以运行,sql如下rnselect m.customerId,sum(mi.integral)rnfrom Member m <em>left</em> <em>outer</em> <em>join</em> rnMemberIntegral mi on m.customerId=mi.customerId rngroup by m.customerIdrnrn其中Member 是会员表,rnrnCustomerId Char(11) N PK 会员号rnMemberCardNumber Varchar(20) 会员卡号rnLoginID Varchar(20) N 登陆名rnPassword Char(16) N 密码rnMobilePhone1 Varchar(20) Y 手机1rn..rn.rn.rnrnMemberIntegral 是会员积分表,rnrnIntegralId Int N 自动生成rnCustomerId Char(11)NPK 客户号rnIntegral Numeric(9)N 积分rnrnrnrnrn是一对多的关系rn现在我要在<em>hibernate</em>上实现刚才sql的功能,就是查出会员信息以及会员的积分总数rnrnselect m from Member m <em>left</em> <em>join</em> fetch m.memberIntegrals mi where m.customerId=mi.customerIdrnrn这样是可以的rn但我还想得到sum(mi.integral)即想rnselect m ,sum(mi.integral) from Member m <em>left</em> <em>join</em> fetch m.memberIntegrals mi where m.customerId=mi.customerIdrn就不行了报错,,,也不知道怎样加group by。。。。。rnrnrnrn怎么办呢。。。。。。rnrn
hibernate使用left join on的问题
现在有两个表 rn表1 rnID VALUErn1 Arn2 Brn3 Crn4 Drnrn表2rnrnID VALUErn1 Ern2 Frn3 Grnrn我想使用<em>left</em> <em>join</em> on达到如下效果rnrn[code=SQL]rnselect a.id,a.value,b.id,b.value from 表1 as a <em>left</em> <em>join</em> 表2 on a.id=b.idrn[/code]rn结果显示成这样rn1 A 1 Ern2 B 2 Frn3 C 3 Grn4 E rnrnrn使用<em>hibernate</em>可以做到吗?要怎么写 谢谢各位了rnrnrnrn
hibernateleft join外连接问题
Hsql语句:rnselect wp from TbWhPlanbill wp <em>left</em> <em>join</em> wp.tbCustSet as crnrnTbWhplanbill.hdm.xml:rn...rnrn rnrnrnrnrn rn rnrn...rnrnHsql语句:rnselect wp from TbWhPlanbill wp <em>left</em> <em>join</em> wp.tbCustSet as crn翻译为标准的sql为:rnselectrn tbwhplanbi0_.planbill_id as planbill1_954_,rn tbwhplanbi0_.cust_id as order2_954_rn fromrn tb_wh_planbill tbwhplanbi0_ rn <em>left</em> <em>outer</em> <em>join</em>rn TB_CUST tbcustset1_ rn on tbwhplanbi0_.planbill_id=tbcustset1_.cust_idrnrn<em>问题</em>在这里,默认是用TbWhPlanbill的主键来<em>left</em> <em>join</em> tb_cust的主键:rn on tbwhplanbi0_.planbill_id=tbcustset1_.cust_id rnrn实际上我是想rnon tbwhplanbi0_.cust_id=tbcustset1_.cust_id rn也就是用TbWhPlanbill的其中一个列cust_id来对应TbCust的主键cust_id。rnrn可以这样实现吗?怎么配置?怎么弄。rn请大家帮忙,谢谢rn
SQL中的left outer join,inner join,right outer join用法详解
SQL中的<em>left</em> <em>outer</em> <em>join</em>,inner <em>join</em>,right <em>outer</em> <em>join</em>用法详解
left join问题,,,,,,在线等!!!急急急
“订单主表”。。。。。rn“订单明细表”rnrnrn<em>left</em> <em>join</em>的时候,,,主表放前面,,还是明细表放前面啊?????? 能不能说明一下原因啊????
在线等 sql left join 查询问题
[code=SQL]A:rnrnSELECT distinct OI.OrderNo, OI.OrderItemNo, OI.OrderAmount, ISNULL(OI.UnitAmount,0), OI.OrderAmount - ISNULL(OI.UnitAmount,0) AS DiffAmount,rn OI.ID, OI.SapOrderNo, OI.MOQ, OI.DisCount, OI.DealerCode, OI.ComDeliverDate, OI.ComOrderDate, OI.ComDeliverCycle,rn OI.CommodityNo, OI.SubAmount , T.IsCostedrnFROM OrderItmes OI rnLEFT JOIN rn(rnSELECT E.IsCosted,EI.OrderItemNo rnFROM dbo.EnterStore E rnINNER JOIN dbo.EnterStoreItem EI ON E.EnterStoreNo = EI.EnterStoreNorn) TrnON OI.OrderItemNo = T.OrderItemNornrnrnB:rnrnSELECT distinct OI.OrderNo, OI.OrderItemNo, OI.OrderAmount, ISNULL(OI.UnitAmount,0), OI.OrderAmount - ISNULL(OI.UnitAmount,0) AS DiffAmount,rn OI.ID, OI.SapOrderNo, OI.MOQ, OI.DisCount, OI.DealerCode, OI.ComDeliverDate, OI.ComOrderDate, OI.ComDeliverCycle,rn OI.CommodityNo, OI.SubAmountrnFROM OrderItmes OI [/code]rnrnrnA 为什么比 B 查询出来的记录多些,求指教
一个关于left join问题在线
select a.1,a.2,b.1,b.2 from a <em>left</em> <em>join</em> b on a.1=b.1 and b.2<>1 where a.2>0rnrn但提示出错, on a.1=b.1 and b.2<>1 这地方是不是不能这么写呀,应该怎么写呢?请提示!
在线等,,LEFT JOIN问题,,着急
SELECT rn PC_DESC,rn NVL(ROUND(CALL_DUR,0),0) AS CALL_DUR,rn A.LOCALNETrn FROMrn APP_THRESHOLD_LOCALPC Arn LEFT JOIN UN_F_CDR_DPCTRAF_15 B ON A.PC_DESC = B.DPC_DESCrn AND DAY_ID = 20130904rn AND HOUR_ID = 8rn AND MI_ID = 30rnrn SELECT rn PC_DESC,rn NVL(ROUND(CALL_DUR,0),0) AS CALL_DUR,rn A.LOCALNETrn FROMrn APP_THRESHOLD_LOCALPC Arn LEFT JOIN UN_F_CDR_DPCTRAF_15 B ON A.PC_DESC = B.DPC_DESCrn WHERE 1=1rn AND DAY_ID = 20130904rn AND HOUR_ID = 8rn AND MI_ID = 30rnrnrn这两个结果不一样!!! 请问业务上有什么区别rnrn
如果挑选left outer join 里面行
数据库表employee,字段empno ,empnamern[code=SQL]rnempno empnamern5 张三rn6 李四rn7 王五rn8 赵六rn[/code]rn数据库表classtypern[code=SQL]rnclassNo className empnorn1 白班 5rn1 白班 6rn2 夜班 7rn2 夜班 8rn[/code]rn查询语句:[code=SQL]rnselect empno,empname,classno,classnamernfrom employee a <em>left</em> <em>outer</em> <em>join</em> classtype brnon a.empno=b.empnorn--得到结果rnempno empname classno classnamern5 张三 1 白班rn6 李四 1 白班rn7 王五 2 夜班rn8 赵六 2 夜班rn[/code]rn<em>问题</em>: 我想得到第1,4条数据,请问怎样写select?
多个Left Outer Join的sql语句如何写
我要做一个查询, 有五张表, 一张是主表, 对其它的表做 <em>left</em> <em>outer</em> <em>join</em> 查询,下面的语句在InterBase中查询是没<em>问题</em>的,可我不知道在VB中该怎么写?rnrnSelect goods.id,goods.number as uunumber,goods.name as name,sum(detailcheck.number) as xssl,sum(detailcheck.price*detailcheck.number) as zqje,sum(detailcheck.je) as xsje,sum(detailcheck.ml) as ml,sum(detailcheck.je)-sum(detailcheck.ml) as xscb From maincheck <em>left</em> <em>outer</em> <em>join</em> detailcheck on ( maincheck.id=detailcheck.checkid) <em>left</em> <em>outer</em> <em>join</em> customer on (maincheck.customerid=customer.id) <em>left</em> <em>outer</em> <em>join</em> store on (maincheck.jhstoreid=store.id or maincheck.chstoreid=store.id) <em>left</em> <em>outer</em> <em>join</em> employee on (maincheck.agentid=employee.id) <em>left</em> <em>outer</em> <em>join</em> goods on (detailcheck.goodsid=goods.id) where ifcheck='true' and ifrollback='false' And maincheck.djtype='零售单' group by goods.number,goods.name,goods.id
left outer join 改写标量子查询
统计每个部门员工总工资。 原SQL: select d.department_id, d.department_name, nvl((select sum(e.salary) from employees e where e.department_id = d.department_id), 0)
关于主从表的 left outer join ?
1主表 rn-------rnid, FNumber, FCustomer_ID, FTargetRegion_IDrn10 2011092201 10 <em>100</em>rn2从表rn-------rnid,FSID,hwmc,js,ztj,zzlrn1 10 A 10 20 30rn2 10 B 20 30 40rn3客户表rn-------rnid,namern10 Crn4 地区表rn---------rnid,namern<em>100</em> Drnrn我想做个视图rnselect A.id,A.FNumber,A.FCustomer_ID,A.FTargetRegion_ID,B.hwmc,B.js,C.name,D.namernfrom 主表 as A <em>left</em> <em>outer</em> <em>join</em> 客户表 as B on a.FCustomer_ID=B.id rnLEFT OUTER JOIN 地区表 as C ON A.FTargetRegion_ID =c.id rnrn怎么得出的是四条数据 应该是2条才对啊
【资料转帖】outerleft outer join有什么区别
[code=SQL]rn<em>outer</em>和<em>left</em> <em>outer</em> <em>join</em>有什么区别??rnrn使用关系代数合并数据rnrn1 关系代数rnrn合并数据集合的理论基础是关系代数,它是由E.F.Codd于1970年提出的。rnrn在关系代数的形式化语言中:rnrn 用表、或者数据集合表示关系或者实体。rnrn 用行表示元组。rnrn 用列表示属性。rnrn关系代数包含以下8个关系运算符rnrn 选取――返回满足指定条件的行。rnrn 投影――从数据集合中返回指定的列。rnrn 笛卡尔积――是关系的乘法,它将分别来自两个数据集合中的行以所有可能的方式进行组合。rnrn 并――关系的加法和减法,它可以在行的方向上合并两个表中的数据,就像把一个表垒在另一个表之上一样。rnrn 交――返回两个数据集合所共有的行。rnrn 差――返回只属于一个数据集合的行。rnrn 连接――在水平方向上合并两个表,其方法是:将两个表中在共同数据项上相互匹配的那些行合并起来。rnrn 除――返回两个数据集之间的精确匹配。rnrn此外,作为一种实现现代关系代数运算的方法,SQL还提供了:rnrn 子查询――类似于连接,但更灵活;在外部查询中,方式可以使用表达式、列表或者数据集合的地方都可以使用子查询的结果。rnrn本章将主要讲述多种类型的连接、简单的和相关的子查询、几种类型的并、关系除以及其他的内容。rnrn2 使用连接rnrn2.1 连接类型rnrn在关系代数中,连接运算是由一个笛卡尔积运算和一个选取运算构成的。首先用笛卡尔积完成对两个数据集合的乘运算,然后对生成的结果集合进行选取运算,确保只把分别来自两个数据集合并且具有重叠部分的行合并在一起。连接的全部意义在于在水平方向上合并两个数据集合(通常是表),并产生一个新的结果集合,其方法是将一个数据源中的行于另一个数据源中和它匹配的行组合成一个新元组。rnrnSQL提供了多种类型的连接方式,它们之间的区别在于:从相互交叠的不同数据集合中选择用于连接的行时所采用的方法不同。rnrn连接类型 定义rnrn内连接 只连接匹配的行rnrn左外连接 包含左边表的全部行(不管右边的表中是否存在与它们匹配的行),以及右边表中全部匹配的行rnrn右外连接 包含右边表的全部行(不管左边的表中是否存在与它们匹配的行),以及左边表中全部匹配的行rnrn全外连接 包含左、右两个表的全部行,不管另外一边的表中是否存在与它们匹配的行。rnrn(H)(theta)连接 使用等值以外的条件来匹配左、右两个表中的行rnrn交叉连接 生成笛卡尔积-它不使用任何匹配或者选取条件,而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配rnrn在INFORMIX中连接表的查询rnrn如果FROM子句指定了多于一个表引用,则查询会连接来自多个表的行。连接条件指定各列之间(每个表至少一列)进行连接的关系。因为正在比较连接条件中的列,所以它们必须具有一致的数据类型。rnrnSELECT语句的FROM子句可以指定以下几种类型的连接rnrnFROM子句关键字 相应的结果集rnrnCROSS JOIN 笛卡尔乘积(所有可能的行对)rnrnINNER JOIN 仅对满足连接条件的CROSS中的列rnrnLEFT OUTER JOIN 一个表满足条件的行,和另一个表的所有行rnrnRIGHT OUTER JOIN 与LEFT相同,但两个表的角色互换rnrnFULL OUTER JOIN LEFT OUTER 和 RIGHT OUTER中所有行的超集rnrnrn2.2 内连接(Inner Join)rnrn内连接是最常见的一种连接,它页被称为普通连接,而E.FCodd最早称之为自然连接。下面是ANSI SQL-92标准rnrnselect * from t_institution i inner <em>join</em> t_teller t on i.inst_no = t.inst_no where i.inst_no = "5801"rnrn其中inner可以省略。等价于早期的连接语法rnrnselect * from t_institution i, t_teller t where i.inst_no = t.inst_no and i.inst_no = "5801"rnrnrn2.3 外连接rnrn2.3.1 左外连接(Left Outer Jion)rnrnselect * from t_institution i <em>left</em> <em>outer</em> <em>join</em> t_teller t on i.inst_no = t.inst_nornrn其中<em>outer</em>可以省略。rnrn2.3.2 右外连接(Rigt Outer Jion)rnrnselect * from t_institution i right <em>outer</em> <em>join</em> t_teller t on i.inst_no = t.inst_nornrn2.3.3 全外连接(Full Outer)rnrn全外连接返回参与连接的两个数据集合中的全部数据,无论它们是否具有与之相匹配的行。在功能上,它等价于对这两个数据集合分别进行左外连接和右外连接,然后再使用消去重复行的并操作将上述两个结果集合并为一个结果集。rnrn在现实生活中,参照完整性约束可以减少对于全外连接的使用,一般情况下左外连接就足够了。在数据库中没有利用清晰、规范的约束来防范错误数据情况下,全外连接就变得非常有用了,你可以使用它来清理数据库中的数据。rnrnselect * from t_institution i full <em>outer</em> <em>join</em> t_teller t on i.inst_no = t.inst_nornrn2.3.4 外连接与条件配合使用rnrn当在内连接查询中加入条件是,无论是将它加入到<em>join</em>子句,还是加入到where子句,其效果是完全一样的,但对于外连接情况就不同了。当把条件加入到<em>join</em>子句时,SQL Server、Informix会返回外连接表的全部行,然后使用指定的条件返回第二个表的行。如果将条件放到where子句中,SQL Server将会首先进行连接操作,然后使用where子句对连接后的行进行筛选。下面的两个查询展示了条件放置位子对执行结果的影响:rnrn条件在<em>join</em>子句rnrnselect * from t_institution i <em>left</em> <em>outer</em> <em>join</em> t_teller t on i.inst_no = t.inst_noand i.inst_no = “5801”rnrn结果是:rnrninst_no inst_name inst_no teller_no teller_namernrn5801 天河区 5801 0001 tomrnrn5801 天河区 5801 0002 davidrnrn5802 越秀区rnrn5803 白云区rnrn条件在where子句rnrnselect * from t_institution i <em>left</em> <em>outer</em> <em>join</em> t_teller t on i.inst_no = t.inst_no where i.inst_no = “5801”rnrn结果是:rnrninst_no inst_name inst_no teller_no teller_namernrn5801 天河区 5801 0001 tomrnrn5801 天河区 5801 0002 davidrnrn2.4 自身连接rnrn自身连接是指同一个表自己与自己进行连接。这种一元连接通常用于从自反关系(也称作递归关系)中抽取数据。例如人力资源数据库中雇员与老板的关系。rnrn下面例子是在机构表中查找本机构和上级机构的信息。rnrnselect s.inst_no superior_inst, s.inst_name sup_inst_name, i.inst_no, i.inst_namernrnfrom t_institution i <em>join</em> t_institution s on i.superior_inst = s.inst_nornrnrn结果是:rnrnsuperior_inst sup_inst_name inst_no inst_namernrn800 广州市 5801 天河区rnrn800 广州市 5802 越秀区rnrn800 广州市 5803 白云区rnrnrn2.5 交叉(无限制) 连接rnrn交叉连接用于对两个源表进行纯关系代数的乘运算。它不使用连接条件来限制结果集合,而是将分别来自两个数据源中的行以所有可能的方式进行组合。数据集合中一的每个行都要与数据集合二中的每一个行分别组成一个新的行。例如,如果第一个数据源中有5个行,而第二个数据源中有4个行,那么在它们之间进行交叉连接就会产生20个行。人们将这种类型的结果集称为笛卡尔乘积。rnrn大多数交叉连接都是由于错误操作而造成的;但是它们却非常适合向数据库中填充例子数据,或者预先创建一些空行以便为程序执行期间所要填充的数据保留空间。rnrnselect * from t_institution i cross <em>join</em> t_teller trnrn在交叉连接中没有on条件子句rnrn rn[/code]
Hibernate中多级LEFT JOIN问题
产品表,库存控制表(主要是指定在某个仓库中的默认货位),货位表rnrnrn库存控制表结构:产品ID,仓库ID,货位IDrnrnrn现在需要做查询,产生表中所有的产品,如果在设置库存控制的就显示出来货位编号来,没有就是nullrnrn现在的HQL是rnselect 产品ID,库存控制.货位.货位编号rnfrom 产品 as prodrn<em>left</em> <em>join</em> prod.库存控制srnrnrn这样产品的SQL语句不正确,只有产品表与库存控制表<em>left</em> <em>join</em>了,但是没有货位与表<em>left</em> <em>join</em>rnrnrn
能不能介绍一下left outer join
RT 谢谢
VFP6.0的"LEFT OUTER JOIN"比VFP9.0的性能好
如下:rnSELECT kqjl.*, kqjl_f.ygdm ;rn FROM hsmdmhdbc!kqjl LEFT OUTER JOIN hsmdmhdbc!kqjl_f ;rn ON STR(kqjl.ygdm)+DTOC(kqjl.kqrq) = STR(kqjl_f.ygdm)+DTOC(kqjl_f.kqrq) ;rn WHERE kqjl.kqrq >= ?dkqrq AND DOW(kqjl.kqrq)#1 INTO CURSOR csrkqjlrnrn在VFP6.0下很正常,执行很快就出结果,但在9.0中就半天还反应了,记录少了可以出结果,多了就象死了!不得其解,请高手帮忙啊!?
请教left outer join 的where语句写法
我只是想显示 t. alarmNum字段的数值大于 t.jiandangkucun + isnull(r.c, 0) as kucun 字段的记录,于是我如此写rnrn select t.id,t.thetypename, t. alarmNum,t.jiandangkucun + isnull(r.c, 0) as kucun from typetable t <em>left</em> <em>outer</em> <em>join</em> (select thetypename, sum(case when operation = '入货' then isnull(goodsnum, 0) else - isnull(goodsnum, 0) end) as c from ruhuo r group by thetypename) r on t.thetypename = r.thetypename where (t.disFlag = 1 and (t.jiandangkucun + isnull(r.c, 0)) t.jianDangKuCun + ISNULL(r.c, 0) AND rn t.alarmNum > t.jianDangKuCun + ISNULL(r.c, 0) AND rn t.theTypeName = r.thetypenamernWHERE (t.disFlag = 1)
oracle816能不能用left outer join
我是在用oracle9i开发的,但现在要导入816内,而我在9i上建立的视图不能在816上建立,如果要改动视图的建立方式,那么相对应的程序也要改动。有没有其他的<em>解决</em>方法。谢谢
表的join操作:left、right、outer、inner
尽量不用where in …的句子,效率不高首先<em>join</em>操作是为了扩展列的数目。内连接内连接只查询匹配的记录,匹配不上的记录无法查询。 select * from A inner <em>join</em> B on A.field1=B.field2 或 select * from A,B where A.field1=B.field2外连接select * from A <em>left</em> <em>join</em>
LEFT OUTER LOOP JOIN 的LOOP是什么意思?
LEFT OUTER LOOP JOIN 和LEFT OUTER JOIN 有区别么?谢谢!rnrn这东西是不是SQL2008新加的,以前没见过。。
How to: Perform a Left Outer Join
[code=C#]rnclass Personrnrn public string FirstName get; set; rn public string LastName get; set; rnrnrnclass Petrnrn public string Name get; set; rn public Person Owner get; set; rnrnrnpublic static void LeftOuterJoinExample()rnrn Person magnus = new Person FirstName = "Magnus", LastName = "Hedlund" ;rn Person terry = new Person FirstName = "Terry", LastName = "Adams" ;rn Person charlotte = new Person FirstName = "Charlotte", LastName = "Weiss" ;rn Person arlene = new Person FirstName = "Arlene", LastName = "Huff" ;rnrn Pet barley = new Pet Name = "Barley", Owner = terry ;rn Pet boots = new Pet Name = "Boots", Owner = terry ;rn Pet whiskers = new Pet Name = "Whiskers", Owner = charlotte ;rn Pet bluemoon = new Pet Name = "Blue Moon", Owner = terry ;rn Pet daisy = new Pet Name = "Daisy", Owner = magnus ;rnrn // Create two lists.rn List people = new List magnus, terry, charlotte, arlene ;rn List pets = new List barley, boots, whiskers, bluemoon, daisy ;rnrn var query = from person in peoplern <em>join</em> pet in pets on person equals pet.Owner into gjrn from subpet in gj.DefaultIfEmpty()rn select new person.FirstName, PetName = (subpet == null ? String.Empty : subpet.Name) ;rnrn foreach (var v in query)rn rn Console.WriteLine("0,-151", v.FirstName + ":", v.PetName);rn rnrnrn// This code produces the following output:rn//rn// Magnus: Daisyrn// Terry: Barleyrn// Terry: Bootsrn// Terry: Blue Moonrn// Charlotte: Whiskersrn// Arlene:rnrn[/code]rnrnOuter Join网上都找不到,无意发现vs2008自带的msdn里有,哈哈rnms-help://MS.VSCC.v90/MS.MSDNQTR.v90.en/dv_csref/html/18e32be8-93db-4d30-8dea-ec6596e18f43.htm
Oracle支持LEFT OUTER JOIN吗?
我用这个来创建一个view,但是报错,说ORA-00933 sql command not properly ended。语句如下:rnSELECT I.STUFF_ID, I.STUFF_NAME, I.STUFF_AMOUNT, I.UNIT_PRICE, I.ACCOUNTUNIT_NO, I.NOW_QUANTITY,I.IN_PRINT, I.OUT_PRINT,rn O.AMOUNT_OUT_SUM, O.UNITPRICE_OUT_AVG,rn A.ACCOUNTUNIT_NAMErnFROM T_QINGQIAN_STUFFIN I LEFT OUTER JOIN V_QINGQIAN_STUFFOUT_SUM O ON O.STUFF_ID = I.STUFF_ID,rn DICT_ACCOUNTUNIT A INNER JOIN I ON A.ACCOUNTUNIT_NO = I.ACCOUNTUNIT_NOrnrnrn如果不能用的话,用什么方法来取得同样的效果?也就是说即使O Table中没有 I Table关联数据,也能把I Table中的所有数据都取出来??rn
sqlce2.0 支持left outer join不?
各位大侠,小弟刚接触sqlce2.0,发现<em>left</em> <em>outer</em> <em>join</em> 老不成功,是不是不支持呀?如果支持,那位大侠帮帮忙,提供一个标准的语法,谢谢了
小谈数据库的Left Outer Join和CTE
这篇文章介绍了数据库中LEFT OUTER JOIN的实际项目中的应用。
select into from left outer join on的错误
"select * into [Excel 8.0;database=C:\\Users\\kinglion\\Desktop\\1234.xls].[Sheet1] from [叙永县分等成果综合数据表$] rn<em>left</em> <em>outer</em> <em>join</em> rn[Excel 8.0;database=C:\\Users\\kinglion\\Desktop\\总表.xls].[叙永县分等成果综合数据表$]rn on rn(rn[叙永县分等成果综合数据表$].OBJECTID =rn [Excel 8.0;database=C:\\Users\\kinglion\\Desktop\\总表.xls].[叙永县分等成果综合数据表$].OBJECTID1rn)"rnrn无效的括号名称 '[Excel 8.0;database=C:\Users\kinglion\Desktop\总表.xls].[叙永县分等成果综合数据表$].OBJECTID1'。rnrn请大家指教哪儿错了呢,没有多少分了
LEFT OUTER JOIN 与 *= 到底有什么不同?
假定 表 a 中有数据,表b 中没有数据rnrnselect a.id,rn b.datarnfrom a <em>left</em> <em>outer</em> <em>join</em> b on a.id = b.idrnwhere a.id > 0 and b.id > 0 rnrn结果集为空rnrnselect a.id,rn b.data rnfrom a,b rnwhere a.id *= b.id rnand a.id > 0 rnand b.id > 0rnrn数据结果正确rnrnrn使用<em>left</em> <em>outer</em> <em>join</em>如何才能<em>解决</em>这个<em>问题</em>呢?
请问Access中5层left outer join的一个古怪问题
我的查询是rnSELECTrn t_act_subcategory.subcategory_name,rn t_subactivity.subactivity_id as SubID,rn t_vendorlist.vendor_description as Vendor,rn t_act_subcategory.subcategory_id as CatID,rn t_act_subcategory.subcategory_name as CatName,rn t_subactivity.title as Title,rn t_subactivity.internalcategory_id as InternalCategory,rn t_subactivity.option_id,rn SUM(t_subactivity_phase_info.budget) AS Totalbudget,rn t_subactivity_estimate_info.estimate_date,rn t_subactivity_estimate_info.estimate_number,rn t_subactivity_estimate_info.estimate_value,rn t_subactivity_estimate_info.accessment_valuernFROMrn (rn (rn (rn (t_act_subcategory <em>left</em> <em>outer</em> <em>join</em> t_subactivity on rn (t_subactivity.subcategory_id = t_act_subcategory.subcategory_id rn and t_subactivity.activity_id = '05-A008' rn and t_subactivity.status_id <> 'ST007' rn and t_subactivity.option_id ='OPT002')rn ) <em>left</em> <em>outer</em> <em>join</em> t_vendorlist on rn (t_subactivity.vendor_id = t_vendorlist.vendor_id)rn ) <em>left</em> <em>outer</em> <em>join</em> t_subactivity_phase_info on rn (t_subactivity.subactivity_id=t_subactivity_phase_info.subactivity_id)rn ) <em>left</em> <em>outer</em> <em>join</em> t_subactivity_estimate_info on rn (t_subactivity.subactivity_id=t_subactivity_estimate_info.subactivity_id)rn )rnGROUP BYrn t_act_subcategory.subcategory_name,rn t_subactivity.subactivity_id ,rn t_vendorlist.vendor_description ,rn t_act_subcategory.subcategory_id ,rn t_act_subcategory.subcategory_name ,rn t_subactivity.title ,rn t_subactivity.internalcategory_id ,rn t_subactivity.option_id,rn t_subactivity_estimate_info.estimate_date,rn t_subactivity_estimate_info.estimate_number,rn t_subactivity_estimate_info.estimate_value,rn t_subactivity_estimate_info.accessment_valuernORDER BYrn 1,rn 4,rn 2,rn 10rnrn这个查询的结果是t_subactivity.activity_id 不是'05-A008'也出来了,出来了很多无关的数据rn假设只留from条件执行:rnSELECTrn *rnFROMrn (rn (rn (rn (t_act_subcategory <em>left</em> <em>outer</em> <em>join</em> t_subactivity on rn (t_subactivity.subcategory_id = t_act_subcategory.subcategory_id rn and t_subactivity.activity_id = '05-A008' rn and t_subactivity.status_id <> 'ST007' rn and t_subactivity.option_id ='OPT002')rn ) <em>left</em> <em>outer</em> <em>join</em> t_vendorlist on rn (t_subactivity.vendor_id = t_vendorlist.vendor_id)rn ) <em>left</em> <em>outer</em> <em>join</em> t_subactivity_phase_info on rn (t_subactivity.subactivity_id=t_subactivity_phase_info.subactivity_id)rn ) <em>left</em> <em>outer</em> <em>join</em> t_subactivity_estimate_info on rn (t_subactivity.subactivity_id=t_subactivity_estimate_info.subactivity_id)rn )rn这样的结果是对的,很奇怪rn假设在第一个sql中将[and t_subactivity.status_id <> 'ST007' ]删除进行查询rn那么数据的个数又恢复正常。rnrn将sql变化一下,group by 去掉rnSELECTrn t_act_subcategory.subcategory_name,rn t_subactivity.subactivity_id as SubID,rn t_vendorlist.vendor_description as Vendor,rn t_act_subcategory.subcategory_id as CatID,rn t_act_subcategory.subcategory_name as CatName,rn t_subactivity.title as Title,rn t_subactivity.internalcategory_id as InternalCategory,rn t_subactivity.option_id,rn t_subactivity_estimate_info.estimate_date,rn t_subactivity_estimate_info.estimate_number,rn t_subactivity_estimate_info.estimate_value,rn t_subactivity_estimate_info.accessment_valuernFROMrn (rn (rn (rn (t_act_subcategory <em>left</em> <em>outer</em> <em>join</em> t_subactivity on rn (t_subactivity.subcategory_id = t_act_subcategory.subcategory_id rn and t_subactivity.activity_id = '05-A008' rn and t_subactivity.status_id <> 'ST007' rn and t_subactivity.option_id ='OPT002')rn ) <em>left</em> <em>outer</em> <em>join</em> t_vendorlist on rn (t_subactivity.vendor_id = t_vendorlist.vendor_id)rn ) <em>left</em> <em>outer</em> <em>join</em> t_subactivity_phase_info on rn (t_subactivity.subactivity_id=t_subactivity_phase_info.subactivity_id)rn ) <em>left</em> <em>outer</em> <em>join</em> t_subactivity_estimate_info on rn (t_subactivity.subactivity_id=t_subactivity_estimate_info.subactivity_id)rn )rn这样的结果还是错的,但是将select中的rn去掉,执行的结果又是对的rn完全不能解释了rn按道理来说,最终结果的件数只应该和<em>join</em>条件和group by 有关,怎么会和select 中的东西有关呢rn那么最后只留一个select项目,也就是刚才去除后数据正确的项目rnSELECTrn t_subactivity.internalcategory_id as InternalCategoryrnFROMrn (rn (rn (rn (t_act_subcategory <em>left</em> <em>outer</em> <em>join</em> t_subactivity on rn (t_subactivity.subcategory_id = t_act_subcategory.subcategory_id rn and t_subactivity.activity_id = '05-A008' rn and t_subactivity.status_id <> 'ST007' rn and t_subactivity.option_id ='OPT002')rn ) <em>left</em> <em>outer</em> <em>join</em> t_vendorlist on rn (t_subactivity.vendor_id = t_vendorlist.vendor_id)rn ) <em>left</em> <em>outer</em> <em>join</em> t_subactivity_phase_info on rn (t_subactivity.subactivity_id=t_subactivity_phase_info.subactivity_id)rn ) <em>left</em> <em>outer</em> <em>join</em> t_subactivity_estimate_info on rn (t_subactivity.subactivity_id=t_subactivity_estimate_info.subactivity_id)rn )rn结果的件数是正确的。。晕哦rn请大家帮忙看看。。。多谢~~!
请教一直困扰的问题,三张表,A Left outer joinB ……left outer join C……
A <em>left</em> <em>outer</em> <em>join</em> B on……<em>left</em> <em>outer</em> <em>join</em> C on……rn这种情况,是(1)A分别与B和C左外连接,还是rn (2)A与B左外连接的结果再与C左外连接rn请问是上面那种情况。
hibernate outer join问题解决立马结贴
<em>hibernate</em> 查询的时候 OneToMany 如何让Criteria 查询自动为<em>left</em> <em>outer</em> <em>join</em>rn具体情况如下rn实体类rnpublic class BaseStation extends BaseEntityrn private String stationId;rn private District districts;rn private List deviceViolations = new ArrayList();rnrn @ManyToOne(fetch = FetchType.EAGER)rn @JoinColumn(name = "districtcode")rn public District getDistricts() rn return this.districts;rn rnrn @Fetch(FetchMode.JOIN) rn @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)rn @JoinColumn(name="stationid")rn public List getDeviceViolations() rn return deviceViolations;rn rnrnrn通过 Criteria查询,没有查询条件rn打印出来的sql为rn selectrn this_.stationid as stationid0_2_,rn deviceviol2_.stationid as stationid4_,rn deviceviol2_.deviceid as deviceid4_,rn deviceviol2_.deviceid as deviceid2_0_,rn deviceviol2_.stationid as stationid2_0_,rn deviceviol2_.checkcrosslines as checkcro2_2_0_,rn deviceviol2_.checkmaxspeed as checkmax3_2_0_,rn deviceviol2_.checkminspeed as checkmin4_2_0_,rn deviceviol2_.checkoccupy as checkocc5_2_0_,rn deviceviol2_.checkoppsite as checkopp6_2_0_,rn deviceviol2_.checkyellowplate as checkyel7_2_0_,rn deviceviol2_.deviceip as deviceip2_0_,rn deviceviol2_.maxspeed as maxspeed2_0_,rn deviceviol2_.minspeed as minspeed2_0_,rn deviceviol2_.speedratio as speedratio2_0_,rn deviceviol2_.updatedate as updatedate2_0_,rn deviceviol2_.updateuser as updateuser2_0_,rn deviceviol2_.workmode as workmode2_0_,rn district3_.districtcode as district1_5_1_,rn district3_.districtname as district2_5_1_ rnfromrn WZCFUSR.basestations this_,rn WZCFUSR.deviceviolation deviceviol2_,rn WZCFUSR.districts district3_rnwhere rn this_.stationid=deviceviol2_.stationid(+) rn and this_.districtcode=district3_.districtcode(+)rnrn为什么sql不是用 <em>left</em> <em>outer</em> <em>join</em> rn我以前做的一个项目我记得打印的sql 都是用 <em>left</em> <em>outer</em> <em>join</em>的rn怎么做才能让生成的sql 用 <em>left</em> <em>outer</em> <em>join</em> 呢rn望各位大侠指点迷津!rnrn
INNER JOIN 是啥意思 LEFT OUTER JOIN 是啥意思
[img=http://hi.csdn.net/attachment/201101/11/76627_12947170044fmu.jpg][/img]rnrnrnrn[img=http://hi.csdn.net/attachment/201101/11/76627_1294717005JCAb.jpg][/img]
inner join,left outer join,right outer join各自的优缺是什么?
如题
union ,inner join 与(left,right)outer join 的区别
请问各位大侠union ,inner <em>join</em> 与(<em>left</em>,right)<em>outer</em> <em>join</em> 的区别rn 最好能举例说明一下~
老式 outer join问题
在一段代码中,看到了下面的一段sql语句,搞了半天明白了(+)=相当于<em>left</em> <em>outer</em> <em>join</em>rn但是(+)<>就弄不明白了,好像是老式的<em>outer</em> <em>join</em>语句,而且不能和<em>left</em> <em>outer</em> <em>join</em>混用rnrnSELECT *rnFROM A,BrnWHERE A.ID (+)= B.IDrnAND A.origin(+)<>'U' rnrn大家帮忙看看,谢谢了
请问:应该用LEFT OUTER JOIN还是INNER JOIN?
有一个信息资费表(信息类型,资费,信息代码),还有一个定制情况表(手机号,信息类型)rn我想查看每个手机号应取的费用,用下面的语句查询时有的手机号对应的收费是 null,选出的记录数多rnselect a.手机号,b.信息代码,b.资费 from 信息资费表 as a <em>left</em> <em>outer</em> <em>join</em> 定制情况表 as brnon a.信息类型=b.信息类型rn用另外一条语句rnselect a.手机号,b.信息代码,b.资费 from 信息资费表 as a inner <em>join</em> 定制情况表 as brnon a.信息类型=b.信息类型rn不会出现手机号对应收费值和信息代码是null的记录,但是选出的记录数少。说明:在定制情况表中手机号对应的每一种信息类型在信息资费表中都有,而且资费和信息代码都不是null。rn我想这是LEFT OUTER JOIN和INNER JOIN的选择<em>问题</em>,最奇怪的是:定制同一种信息类型的手机号有的资费是null 但是有的就不是,而是实际的资费值。rn请各位帮我分析分析这是什么原因啊。谢谢了
100分,sqlserver中full outer join为什么不好用 ??
a 表rnfull <em>outer</em> <em>join</em>rnb 表rnrna 表 10万条数据rnb 表 <em>100</em>万条数据rnrn但是现在查出来 只有10万条数据!rnrn
Full Outer Join 的问题
表R 表S rnrnA B C D A E F Grn1 xx yy zz 1 mm nn kkrn2 ss pp nn 3 ff jj llrnrnSQL Server 中rnR Full Outer Join S on R.A = S.A 结果rnrnA B C D A E F Grn1 xx yy zz 1 mm nn kkrn2 ss pp nn Null Null Null NullrnNull Null Null Null 3 ff jj llrnrnrn我想得到如下结果,SQL Server 中的SQL如何写??rnrnA B C D E F Grn1 xx yy zz mm nn kkrn2 ss pp nn 0 0 0rn3 0 0 0 ff jj llrn
full outer join 问题
用 full <em>outer</em> <em>join</em> 连接两个表rnrnselect isnull(t1.部门编码,t2.销售部门)部门编码,t1.数量,t2.数量,rnfromrnrn(select 部门编码,sum(数量) 数量rnfrom kcgl_出库单rngroup by 部门编码) t1rnrnfull <em>outer</em> <em>join</em>rn(select 销售部门,sum(数量)没回数量rnfrom XSGL_销售发票rngroup by 销售部门) t2rnrnon t1.部门编码 = t2.销售部门 rnrn运行一段时间后提示:rnODBC: 消息 0,级别 19,状态 1rnSqlDumpExceptionHandler: 进程 58 发生了严重的异常 c0000005 EXCEPTION_ACCESS_VIOLATION。SQL Server 将终止该进程。rnrn连接中断rnrn不知是什么错误rnrnrn
关于hibernateleft join fetch
最近看了下<em>hibernate</em> 的左外迫切连接,例子是这样的,有一个主表Main里有2条数据 字段为 id,name 从表Sub有3条数据,字段有id,mainid(引用主表的id),从表的前2条数据都引用主表的第一条数据中的id,第3条数据引用主表的第2条数据的id,最后使用左外迫切连接返回数据list,为什么List里有3个Main对象,其中前两个完全一样??按照我的理解应该是只有两个对象才对啊 第一个对象的HashSet里 存了从表的两个对象, 第二个对象里存了主表的第3个对象, 为什么会出现前两个完全一样的重复对象???
关于JOIN、LEFT JOIN、 LEFT OUTER JOIN,很困惑,大家帮忙看看阿,谢谢
现在我想把JOIN、LEFT JOIN、 LEFT OUTER JOIN语句转换成FROM ... WHERE型的语句,因为ORACLE不支持JOIN.rn我想知道是不是能直接把JOIN、LEFT JOIN、 LEFT OUTER JOIN语句转换为FROM ... WHERE语句,不知道有没有什么影响。rn例如:rn源语句:rnSELECT T_Subject.*, T_User.User_Name AS Responsibler_Name rnFROM T_Subject rnLEFT OUTER JOIN T_User rnON T_Subject.Responsibler_ID = T_User.User_ID rn目标语句:rnSELECT T_Subject.*, T_User.User_Name AS Responsibler_Name rnFROM T_Subject , T_User rnwhere T_Subject.Responsibler_ID = T_User.User_ID rnAND ( T_Subject.Proceeding_ID = 'sp01' ) AND T_Subject.PROJECT_NO='sp01' rnAND T_User.PROJECT_NO='sp01' ORDER BY T_Subject.Subject_Nornrn大家帮忙分析一下吧,谢谢了
100解决棘手问题在线
ORACLE服务器里面有两个数据库testa和testb。rntesta数据库中有用户名a,密码a,表空间a。利用EXP导出为一个1.dmp文件。rn然后将这个1.dmp文件导入到testb数据库下的用户名b,密码b,表空间b下,怎么导?rnrn我试了半天也没有试出来,用imp fromuser=a touser=b也不对。
Hibernate 中如何处理left join on 的啊?
听说<em>hibernate</em>中没有<em>left</em> <em>join</em> on 事实我也证明了 用ON会报错。但是如何处理查到<em>left</em> <em>join</em> on 的数据呢rnrn比如两个model A , B 有公共字段ID。rnA有数据 rnid namern1 arn2 brn3 crnrnB 有数据rnid namern1 aarn2 bbrn4 ccrn那么如果select * from a <em>left</em> <em>join</em> b on a.id = b.id where b.id is null 那么结果肯定是 3 crn那么如何用<em>hibernate</em> 查出来?????rnrn<em>解决</em>有分 如果有hbm的配置也可以贴上来rn谢谢各位了。rn
C++编写删除单链表下载
建立一个单链表大家都很熟悉,但删除一个单链表却很少接触 相关下载链接:[url=//download.csdn.net/download/wstgxx/3287761?utm_source=bbsseo]//download.csdn.net/download/wstgxx/3287761?utm_source=bbsseo[/url]
武汉理工信号与系统03-09真题下载
武汉理工大学信号与系统真题03-09,武汉理工,信号与系统 相关下载链接:[url=//download.csdn.net/download/u010127154/5211388?utm_source=bbsseo]//download.csdn.net/download/u010127154/5211388?utm_source=bbsseo[/url]
网上书店系统下载
本网上书店系统是本科设计时设计,功能相当齐全,可以和当当相媲美,是一个不可多得的网站示例 相关下载链接:[url=//download.csdn.net/download/xiaogejun/8506747?utm_source=bbsseo]//download.csdn.net/download/xiaogejun/8506747?utm_source=bbsseo[/url]
相关热词 c#入门推荐书 c# 解码海康数据流 c# xml的遍历循环 c# 取 查看源码没有的 c#解决高并发 委托 c#日期转化为字符串 c# 显示问号 c# 字典对象池 c#5.0 安装程序 c# 分页算法
我们是很有底线的