数据库中有 这样4个表 SIN,T_NODEVINDATA,tiles MotorCodeTiles。 MotorCodeTiles 表中分别有 SIN,TILES的主键,而没有T_NODEVINDATA的主键select * from
(select rownum 序列号,P.*
from (select T_NODEVINDATA.EngineVin,Sin.MotorBlockCode,tiles.TilesCode, tiles.TilesName,
count(MotorCodeTiles.tilesid) 数量,MotorCodeTiles.blendtime
from MotorCodeTiles,Sin,Tiles,T_NODEVINDATA
where MotorCodeTiles.Sid = Sin.Sid
and Tiles.TilesId = MotorCodeTiles.TilesId
and T_NODEVINDATA.BARCODE = sin.MotorBlockCode
and MotorCodeTiles.blendtime
between to_date('2009-11-24','yyyy-MM-dd')
and to_date('2009-11-25','yyyy-MM-dd')
group by T_NODEVINDATA.EngineVin,sin.motorblockcode,tiles.tilescode,tiles.tilesname,MotorCodeTiles.blendtime
order by MotorCodeTiles.blendtime)p)而查询出来的数据是应该查询出的数据的3倍。是条件给少了?请大虾指点指点而查3个表就不会出现怎样的问题
select * from
(select rownum 序列号,P.*
from (select Sin.MotorBlockCode,tiles.TilesCode, tiles.TilesName,
count(MotorCodeTiles.tilesid) 数量,MotorCodeTiles.blendtime
from MotorCodeTiles,Sin,Tiles
where MotorCodeTiles.Sid = Sin.Sid
and Tiles.TilesId = MotorCodeTiles.TilesId
and MotorCodeTiles.blendtime
between to_date('2009-11-24','yyyy-MM-dd')
and to_date('2009-11-25','yyyy-MM-dd')
group by sin.motorblockcode,tiles.tilescode,tiles.tilesname,MotorCodeTiles.blendtime
order by MotorCodeTiles.blendtime)p)
(select rownum 序列号,P.*
from (select T_NODEVINDATA.EngineVin,Sin.MotorBlockCode,tiles.TilesCode, tiles.TilesName,
count(MotorCodeTiles.tilesid) 数量,MotorCodeTiles.blendtime
from MotorCodeTiles,Sin,Tiles,T_NODEVINDATA
where MotorCodeTiles.Sid = Sin.Sid
and Tiles.TilesId = MotorCodeTiles.TilesId
and T_NODEVINDATA.BARCODE = sin.MotorBlockCode
and MotorCodeTiles.blendtime
between to_date('2009-11-24','yyyy-MM-dd')
and to_date('2009-11-25','yyyy-MM-dd')
group by T_NODEVINDATA.EngineVin,sin.motorblockcode,tiles.tilescode,tiles.tilesname,MotorCodeTiles.blendtime
order by MotorCodeTiles.blendtime)p)而查询出来的数据是应该查询出的数据的3倍。是条件给少了?请大虾指点指点而查3个表就不会出现怎样的问题
select * from
(select rownum 序列号,P.*
from (select Sin.MotorBlockCode,tiles.TilesCode, tiles.TilesName,
count(MotorCodeTiles.tilesid) 数量,MotorCodeTiles.blendtime
from MotorCodeTiles,Sin,Tiles
where MotorCodeTiles.Sid = Sin.Sid
and Tiles.TilesId = MotorCodeTiles.TilesId
and MotorCodeTiles.blendtime
between to_date('2009-11-24','yyyy-MM-dd')
and to_date('2009-11-25','yyyy-MM-dd')
group by sin.motorblockcode,tiles.tilescode,tiles.tilesname,MotorCodeTiles.blendtime
order by MotorCodeTiles.blendtime)p)
(
TILESID NUMBER not null primary key,
TILESCODE VARCHAR2(50) not null,
TILESNAME VARCHAR2(20) not null
)
MOTORCODETILES
(
MCTID NUMBER not null primary key,
SID NUMBER not null ,
TILESID NUMBER not null,
BLENDTIME DATE default sysdate not null
)
SIN
(
SID NUMBER not null primary key,
MOTORBLOCKCODE VARCHAR2(30) not null,
SINDATA VARCHAR2(500) not null,
CREATETIME DATE default sysdate not null,
PICKTIME DATE,
STATUS NUMBER default 0 not null
)
T_NODEVINDATA
(
ID NUMBER not null primary key,
BARCODE VARCHAR2(100),
ENGINEVIN VARCHAR2(100) not null,
)
表的大体结构就是怎样的
你检查下
select BARCODE,count(1) from T_NODEVINDATA
group by BARCODE
having count(1)>1
看看结果。如果有返回记录,就说明有重复值
这就是查询出来的数据是应该查询出的数据的3倍的原因所在
你看看T_NODEVINDATA这个表,再对应下自己的需求