想将2个sql 拼成一个sql 单独用都没问题, 现在需要将2个拼成一个,来查询,上面的左连接没问题,现在是想加个查询,当 ismajor等于真时,查询出另外的信息,如果我直接拼的话,会查询不到数据,// 一个sql select bl.name as w,bp.name as wa from c_order c
left outer join C_BPARTNER bp on bp.C_BPARTNER_ID=c.C_BPARTNER_ID
left outer join C_BPARTNER_LOCATION bl on bl.C_BPARTNER_LOCATION_ID=c.C_BPARTNER_LOCATION_ID// 一个sql , 里面只会有一条数据,当条件为真时。
select * from c_bpartner db where db.ismajor='Y'求求求
left outer join C_BPARTNER bp on bp.C_BPARTNER_ID=c.C_BPARTNER_ID
left outer join C_BPARTNER_LOCATION bl on bl.C_BPARTNER_LOCATION_ID=c.C_BPARTNER_LOCATION_ID// 一个sql , 里面只会有一条数据,当条件为真时。
select * from c_bpartner db where db.ismajor='Y'求求求
left outer join C_BPARTNER bp on bp.C_BPARTNER_ID=c.C_BPARTNER_ID) t left join C_BPARTNER_LOCATION bl on bl.C_BPARTNER_LOCATION_ID=c.C_BPARTNER_LOCATION_ID where t.ismajor='Y'
full join C_BPARTNER bp on bp.C_BPARTNER_ID=c.C_BPARTNER_ID
left outer join C_BPARTNER_LOCATION bl on bl.C_BPARTNER_LOCATION_ID=c.C_BPARTNER_LOCATION_ID
full join C_BPARTNER pn on pn.C_BPARTNER_ID=c.C_BPARTNER_ID
left outer join C_BPARTNER_LOCATION bl on bl.C_BPARTNER_LOCATION_ID=c.C_BPARTNER_LOCATION_ID
where c.issotrx='Y' or pn.ismajor='Y'
left join C_BPARTNER pn on pn.C_BPARTNER_ID=c.C_BPARTNER_ID
left outer join C_BPARTNER_LOCATION bl on bl.C_BPARTNER_LOCATION_ID=c.C_BPARTNER_LOCATION_ID
union all
select * from c_order c
where exists(select 1 from c_bpartner db where db.ismajor='Y' and db.C_BPARTNER_ID=c.C_BPARTNER_ID)
没太懂你的需求。。
//显示订单上面信息
select bp.name,bl.name from c_order c
left outer join C_BPARTNER bp on bp.C_BPARTNER_ID=c.C_BPARTNER_ID
left outer join C_BPARTNER_LOCATION bl on bl.C_BPARTNER_LOCATION_ID=c.C_BPARTNER_LOCATION_ID//显示下面,版权信息
因为版权只需一条数据就可以了,所以我还是用的 C_BPARTNER 表,当 C_BPARTNER 表中的 ismajor='y' 时,也就是对应的版权信息, ismajor='N' 默认为N ,这个
不用管, 如果只是单独查询 版权信息的话,就是 select * from C_BPARTNER where C_BPARTNER.ismajor='Y' ,即可,但是现在是想用一条sql 查询出整个订单的信息
下面的版权信息 只有当 ismajor='Y' 时,才会查询的到这条记录,因为只有一条记录,如果要变更的话,只需在这条记录上面更改就好。。
left join C_BPARTNER pn on pn.C_BPARTNER_ID=c.C_BPARTNER_ID
left outer join C_BPARTNER_LOCATION bl on bl.C_BPARTNER_LOCATION_ID=c.C_BPARTNER_LOCATION_ID
union all
select * from c_order c
where c.issotrx='Y'
left join C_BPARTNER pn on pn.C_BPARTNER_ID=c.C_BPARTNER_ID
left outer join C_BPARTNER_LOCATION bl on bl.C_BPARTNER_LOCATION_ID=c.C_BPARTNER_LOCATION_ID
union all
select C1.* from c_order c1
where c1.issotrx='Y'
//查询客户信息,在c_bpater 中有 N 条记录,
select * from c_bpater c where c.issotrx='Y'// 查询自己公司信息,为固定记录,只有一条记录在c_bpater 表中,也只会有一条记录
select * from c_bpater c where c.ismajor='Y'这样,可能怪我当时没说清楚现在在打印时,在上面显示客户信息,下面显示自己公司的信息。在ireport 里面来做报表,写sql ,只能写一个sql 不能写多个,所以要将这2条sql 写成一句,并且查询出他们的信息
left join C_BPARTNER pn on pn.C_BPARTNER_ID=c.C_BPARTNER_ID
left outer join C_BPARTNER_LOCATION bl on bl.C_BPARTNER_LOCATION_ID=c.C_BPARTNER_LOCATION_ID
union all
select pn.name from C_BPARTNER pn
where pn.ismajor='Y'
两个sql连成一个,可以用union all,但是返回列应该一样。你试试吧