几个表联查,现在的sql是这样,不写DISTINCT会查出多条一样的数据,有更好的写法吗select DISTINCT
T_DEVICES.DEVICEID,T_DEVICES.ASSETNAME,T_DEVICES.PRINCIPAL
from
((T_DEVICES
inner join
T_DEVICEMODEL
on
T_DEVICES.DEVICEMODELID = T_DEVICEMODEL.DEVICEMODELID)
inner join
T_DEVICETYPE
on
T_DEVICEMODEL.DEVICETYPEID = T_DEVICETYPE.DEVICETYPEID)
inner join
T_DEVICETOPTYPE
on
T_DEVICETYPE.DEVICETOPTYPE = T_DEVICETOPTYPE.DEVICETOPTYPE
where
T_DEVICES.ROOMID = 12001
and
T_DEVICES.BRANCHID = 5
and
T_DEVICETOPTYPE.DEVICETOPTYPE = 1
T_DEVICES.DEVICEID,T_DEVICES.ASSETNAME,T_DEVICES.PRINCIPAL
from
((T_DEVICES
inner join
T_DEVICEMODEL
on
T_DEVICES.DEVICEMODELID = T_DEVICEMODEL.DEVICEMODELID)
inner join
T_DEVICETYPE
on
T_DEVICEMODEL.DEVICETYPEID = T_DEVICETYPE.DEVICETYPEID)
inner join
T_DEVICETOPTYPE
on
T_DEVICETYPE.DEVICETOPTYPE = T_DEVICETOPTYPE.DEVICETOPTYPE
where
T_DEVICES.ROOMID = 12001
and
T_DEVICES.BRANCHID = 5
and
T_DEVICETOPTYPE.DEVICETOPTYPE = 1
解决方案 »
- oracleDBconsoleorcl不能启动
- 十万条记录,求高效更新SQL
- 用JDBC提交的查询为什么不返回
- 释放表空间
- 在init.ora参数文件中设置job_queue_interval,为什么ORACLE系统报告该参数已是obsolete
- oracle连接问题
- 存储过程中where语句部分是否能实现关系运算
- 违反唯一约束条件(TSSH PK_SDSK_CPCODE_THS_NO)
- 哪些工具需要配置环境?是否必须?
- oracle中function如何调用带输出参数的存储过程?
- [求助]怎样实现循环插入
- oracle中对树形结构的查询,我想除了得到一列显示起始节点到当前节点的完整路径path外,还要有一列class2,如何实现?
否则就先用子查询查出唯一记录,然后再连接.
如果都不行,就只能用distinct了.
FROM T_DEVICES
inner join T_DEVICEMODEL on T_DEVICES.DEVICEMODELID = T_DEVICEMODEL.DEVICEMODELID
inner join T_DEVICETYPE on T_DEVICEMODEL.DEVICETYPEID = T_DEVICETYPE.DEVICETYPEID
inner join T_DEVICETOPTYPE on T_DEVICETYPE.DEVICETOPTYPE = T_DEVICETOPTYPE.DEVICETOPTYPE
WHERE T_DEVICES.ROOMID = 12001 and T_DEVICES.BRANCHID = 5 and T_DEVICETOPTYPE.DEVICETOPTYPE = 1 有数据冗余,说明还要加条件,最好你把这几个表的主键都列出来;
把数据也帖出来
T_DEVICETYPE.DEVICETYPEID 、T_DEVICETOPTYPE.DEVICETOPTYPE 应该是主键,而且应该是
T_DEVICETOPTYPE.DEVICETOPTYPE是最顶级的,
依次是T_DEVICETYPE.DEVICETYPEID、T_DEVICEMODEL.DEVICEMODELID、T_DEVICES.DEVICE_ID
而且还是从顶级开始的一对多的关系,也应该不存在一个T_DEVICES.DEVICE_ID对应多个T_DEVICEMODEL.DEVICEMODELID
或一个T_DEVICEMODEL.DEVICEMODELID对应多个T_DEVICETYPE.DEVICETYPEID
或一个T_DEVICETYPE.DEVICETYPEID对应多个T_DEVICETOPTYPE.DEVICETOPTYPE
这样的话我就想不通为什么会有重复记录了.
不过,象这样的语句,我一般是这样写的select a.DEVICEID, a.ASSETNAME, a.PRINCIPAL
from T_DEVICES a, T_DEVICEMODEL b, T_DEVICETYPE c, T_DEVICETOPTYPE d
where a.DEVICEMODELID = b.DEVICEMODELID
and b.DEVICETYPEID = c.DEVICETYPEID
and c.DEVICETOPTYPE = d.DEVICETOPTYPE
and a.ROOMID = 12001
and a.BRANCHID = 5
and d.DEVICETOPTYPE = 1