27,580
社区成员
发帖
与我相关
我的任务
分享【在线急待】怎样排除重复的数据
select distinct
i.locationid,p.controlid,p.partid,p.name,p.model,r.recedate,i.stdprice,i.onhand,i.onhandmny
from
parts p,inventory i,receitem r
where
(p.partid = i.partid and i.partid = r.partid)
//and
//
//r.recedate between '2004-1-1' and '2004-4-30'
and
(
i.locationid like "Z%"
and
i.onhand <> 0
)
;
因为进行了3表联合，所以，p.partid，有重复，怎样剔除重复数据，得到正确的视图。
i.locationid,p.controlid,p.partid,p.name,p.model,r.recedate,i.stdprice,i.onhand,i.onhandmny
from
parts p,inventory i,receitem r
where
(p.partid = i.partid and i.partid = r.partid)
//and
//
//r.recedate between '2004-1-1' and '2004-4-30'
and
(
i.locationid like "Z%"
and
i.onhand <> 0
)
;
因为进行了3表联合，所以，p.partid，有重复，怎样剔除重复数据，得到正确的视图。
...全文
请发表友善的回复…
发表回复
xwqjamky 2007-01-03
- 打赏
- 举报
SELECT * FROM @T AS T
WHERE EXISTS
(SELECT 1 FROM @T
WHERE T.ID = ID
AND ....
)
WHERE EXISTS
(SELECT 1 FROM @T
WHERE T.ID = ID
AND ....
)
hidebluer 2006-12-28
- 打赏
- 举报
t 是什么，谢谢
caixia615 2006-12-28
- 打赏
- 举报
select * from (select
i.locationid,p.controlid,p.partid,p.name,p.model,r.recedate,i.stdprice,i.onhand,i.onhandmny
from
parts p,inventory i,receitem r
where
(p.partid = i.partid and i.partid = r.partid)
//and
//
//r.recedate between '2004-1-1' and '2004-4-30'
and
(
i.locationid like "Z%"
and
i.onhand <> 0
))t
where partid in(selelct distinct partid from t)
i.locationid,p.controlid,p.partid,p.name,p.model,r.recedate,i.stdprice,i.onhand,i.onhandmny
from
parts p,inventory i,receitem r
where
(p.partid = i.partid and i.partid = r.partid)
//and
//
//r.recedate between '2004-1-1' and '2004-4-30'
and
(
i.locationid like "Z%"
and
i.onhand <> 0
))t
where partid in(selelct distinct partid from t)
hidebluer 2006-12-28
- 打赏
- 举报
错了，是select,不是select distinct
