左连接那里怎么改写select a.deviceid,
a.assetname,
a.devicemodelid,
b.assettype,
a.rackid,
a.roomid,
nvl(a.ustart, 0) as ustart,
nvl(b.uheigh, 0) as uheigh,
(uheigh + ustart) as ustop,
a.utotal,
a.principal,
c.branchid,
c.branch as branchname,
d.devicetypeid,
d.devicetype,
e.devicetoptype as devicetoptypeid,
e.devicetop as devicetoptype
from t_devices a, t_devicemodel b, t_branch c,t_devicetype d,t_devicetoptype e
where a.devicemodelid = b.devicemodelid
and to_number(trim(a.branchid)) = c.branchid(+)
and b.devicetypeid=d.devicetypeid
and d.devicetoptype=e.devicetoptype
a.assetname,
a.devicemodelid,
b.assettype,
a.rackid,
a.roomid,
nvl(a.ustart, 0) as ustart,
nvl(b.uheigh, 0) as uheigh,
(uheigh + ustart) as ustop,
a.utotal,
a.principal,
c.branchid,
c.branch as branchname,
d.devicetypeid,
d.devicetype,
e.devicetoptype as devicetoptypeid,
e.devicetop as devicetoptype
from t_devices a, t_devicemodel b, t_branch c,t_devicetype d,t_devicetoptype e
where a.devicemodelid = b.devicemodelid
and to_number(trim(a.branchid)) = c.branchid(+)
and b.devicetypeid=d.devicetypeid
and d.devicetoptype=e.devicetoptype
解决方案 »
- 关于greenplum日期函数请教
- 数据在另个事务更新后,在一个事务中再次查询不出来
- mysql高手们,指点下吧,mysql给一个表连接一个主键列!
- mysql 出错了.
- MySQL的一个简单问题,由于初学,所以不清楚了,麻烦大大们解答解答
- 高手指点:mysql5的触发器什么情况下会失效,急!!
- 用prepared statements还是 statement语句好
- 一个SQL语句问题
- 各位牛人老大,关于postgres的一点菜问题,进来帮帮忙呀分数我一次只能给这么多会再给分的!
- 如何根据查询条件进行排序?
- mysql索引能否实现如此的功用?
- mysql中,myisamchk的使用中,怎样修复数据中的所有出问题的表。(错误提示说什么crashed,tryrepair)
有内连接、左连接
from t_devices a, t_devicemodel b, ,t_devicetype d,t_devicetoptype e
left join t_branch c
on
to_number(trim(a.branchid)) = c.branchid(+) where a.devicemodelid = b.devicemodelid
and b.devicetypeid=d.devicetypeid
and d.devicetoptype=e.devicetoptype
NVL->IF F1 IS NULL
CASE WHEN a.ustart IS NULL THEN 0 ELSE a.ustart END
to_number(trim(a.branchid)) = c.branchid(+) -->此为左联接,用left join替换
to_number函数不知道有没有,如果是mysql,可以用 "to_number(trim(a.branchid))" --> cast(trim(a.branchid) as unsigned int)
a.assetname,
a.devicemodelid,
b.assettype,
a.rackid,
a.roomid,
COALESCE(a.ustart, (0)::numeric) AS ustart,
COALESCE(b.uheigh, (0)::numeric) as uheigh,
(uheigh + ustart) as ustop,
a.utotal,
a.principal,
c.branchid,
c.branch as branchname,
d.devicetypeid,
d.devicetype,
e.devicetoptype as devicetoptypeid,
e.devicetop as devicetoptype
from t_devices a, t_devicemodel b,t_devicetype d,t_devicetoptype e
left join t_branch c
on to_number(trim(a.branchid)) = c.branchid
where a.devicemodelid = b.devicemodelid
and b.devicetypeid=d.devicetypeid
and d.devicetoptype=e.devicetoptype不对
[Err] ERROR: invalid reference to FROM-clause entry for table "a" at character 595
HINT: There is an entry for table "a", but it cannot be referenced from this part of the query.
a.assetname,
a.devicemodelid,
b.assettype,
a.rackid,
a.roomid,
COALESCE(a.ustart, (0)::numeric) AS ustart,
COALESCE(b.uheigh, (0)::numeric) as uheigh,
(uheigh + ustart) as ustop,
a.utotal,
a.principal,
c.branchid,
c.branch as branchname,
d.devicetypeid,
d.devicetype,
e.devicetoptype as devicetoptypeid,
e.devicetop as devicetoptype
from t_devices a
inner join t_devicemodel b on a.devicemodelid = b.devicemodelid
inner join t_devicetype d on b.devicetypeid=d.devicetypeid
inner join t_devicetoptype e on d.devicetoptype=e.devicetoptype
left join t_branch c on to_number(trim(a.branchid)) = c.branchid
a.assetname,
a.devicemodelid,
b.assettype,
a.rackid,
a.roomid,
COALESCE(a.ustart, (0)::numeric) AS ustart,
COALESCE(b.uheigh, (0)::numeric) as uheigh,
(uheigh + ustart) as ustop,
a.utotal,
a.principal,
c.branchid,
c.branch as branchname,
d.devicetypeid,
d.devicetype,
e.devicetoptype as devicetoptypeid,
e.devicetop as devicetoptype
from (((t_devices a inner join t_devicemodel b on a.devicemodelid = b.devicemodelid )
inner join t_devicetype d on b.devicetypeid=d.devicetypeid)
inner join t_devicetoptype e on d.devicetoptype=e.devicetoptype)
left join t_branch c on to_number(trim(a.branchid)) = c.branchid