TBL:
ID OldGoodsID NewGoodsID
1 1 2 --这条第一次更换
2 1 3
3 2 1 --这条他又换回来了,要找的就是这样的记录这是一张货号更换记录表 现在要找到更换过的GoodsID后又被更换回来的记录。
如ID是1的和ID是3的记录,第一次(ID为1)从 GoodsID=1 更换成GoodsID=2。 可是第2次(ID为3)他又换回来了
ID OldGoodsID NewGoodsID
1 1 2 --这条第一次更换
2 1 3
3 2 1 --这条他又换回来了,要找的就是这样的记录这是一张货号更换记录表 现在要找到更换过的GoodsID后又被更换回来的记录。
如ID是1的和ID是3的记录,第一次(ID为1)从 GoodsID=1 更换成GoodsID=2。 可是第2次(ID为3)他又换回来了
Select * from tb as t where exists(select * from tb
where oldGoodsID=t.NewGoodsID and t.oldGoodsID=NewGoodsID)
(
select OldGoodsID,NewGoodsID from
(
select OldGoodsID,NewGoodsID from tbl
union all
select NewGoodsID OldGoodsID,OldGoodsID NewGoodsID from tbl
) t
group by OldGoodsID,NewGoodsID having count(*) > 1
) m
where (tbl.OldGoodsID = m.OldGoodsID and tbl.NewGoodsID = NewGoodsID) or
(tbl.OldGoodsID = m.NewGoodsID and tbl.NewGoodsID = OldGoodsID)
where a.oldGoodsID=b.NewGoodsID and b.oldGoodsID=a.NewGoodsID group by a.ID)
Select * from tb where ID=(select max(a.ID) from tb a,tb b
where a.oldGoodsID=b.NewGoodsID and b.oldGoodsID=a.NewGoodsID)
insert t select 1,1,2
union all select 2,1,3
union all select 3,2,1Select * from t as tt where exists(select * from t
where oldGoodsID=tt.NewGoodsID and tt.oldGoodsID=NewGoodsID and tt.id>id)id OldGoodsID NewGoodsID
----------- ----------- -----------
3 2 1
(
select id,NewGoodsID OldGoodsID,OldGoodsID NewGoodsID from tb
union
select id,OldGoodsID,NewGoodsID from tb
) a group by OldGoodsID,NewGoodsID
) b
on tb.id=b.id
where sumid>1
where oldgoodsid in (select newgoodsid from tab_example)
and newgoodsid in (select oldgoodsid from tab_example)
目前暂且把ID作为时间参考好了
取最原始货号以ID最小为准,最新货号以ID最大为标准(假定ID越大,时间越近)SELECT OLD_ID,OldGoodsID,NEW_ID,NewGoodsID
FROM
(SELECT MIN(ID) AS OLD_ID,OldGoodsID FROM TBL GROUP BY OldGoodsID) AS TB1
LEFT JOIN
(SELECT MAX(ID) AS NEW_ID,NewGoodsID FROM TBL GROUP BY NewGoodsID) AS TB2
ON TB1.OldGoodsID=TB2.NewGoodsID结果形式:
OLD_ID OldGoodsID NEW_ID NewGoodsID
----------- ----------- ----------- -----------
1 1 3 1
3 2 1 2以上只是大致根据个人理解写了一下,仅供LZ做个参考
此外,这个表和查询是不是可以增加一下时间字段的考虑(个人建议而已 ^_^)
insert t select 1,1,2
union all select 2,1,3
union all select 3,2,1
union all select 4,3,1select * from t t1,t t2 where
t1.oldgoodsid=t2.newgoodsid and t1.newgoodsid=t2.oldgoodsid
and t1.id>t2.id这样就可以把所有交换的情况都列出来了