如何从重复数据中找出日期最后的一条???
tbgc_id是唯一值,不会重复
ass_segment1是物料号,可能会重复
std_item_id 是标准机号,会重复
表一
tbgc_id ass_segment1 std_item_id creation_date
56815 NB5E012 319055 2012-8-8 14:43:14
56816 RB51019 319055 2012-8-1 14:43:14
56817 CC51019 319055 2012-7-1 14:43:14
26810 QQ12123 101011 2012-6-1 14:43:14
11810 GG22331 212100 2012-5-1 14:43:14 我要找出std_item_id重复的数据,
表二
tbgc_id ass_segment1 std_item_id creation_date
56815 NB5E012 319055 2012-8-8 14:43:14
56816 RB51019 319055 2012-8-1 14:43:14
56817 CC51019 319055 2012-7-1 14:43:14
再在表二中找出creation_date最近的这条数据:
表三
tbgc_id ass_segment1 std_item_id creation_date
56815 NB5E012 319055 2012-8-8 14:43:14我写的sql语句,只能得到表二,哪位能帮忙得到表三呢??????????????
(用select top得出的不算)select zth.tbgc_id,zth.ass_segment1,zth.std_item_id,zth.creation_date
from zdoe_tbgc_head zth
where zth.std_item_id in
(select std_item_id
from zdoe_tbgc_head t
where std_item_id = zth.std_item_id)
tbgc_id是唯一值,不会重复
ass_segment1是物料号,可能会重复
std_item_id 是标准机号,会重复
表一
tbgc_id ass_segment1 std_item_id creation_date
56815 NB5E012 319055 2012-8-8 14:43:14
56816 RB51019 319055 2012-8-1 14:43:14
56817 CC51019 319055 2012-7-1 14:43:14
26810 QQ12123 101011 2012-6-1 14:43:14
11810 GG22331 212100 2012-5-1 14:43:14 我要找出std_item_id重复的数据,
表二
tbgc_id ass_segment1 std_item_id creation_date
56815 NB5E012 319055 2012-8-8 14:43:14
56816 RB51019 319055 2012-8-1 14:43:14
56817 CC51019 319055 2012-7-1 14:43:14
再在表二中找出creation_date最近的这条数据:
表三
tbgc_id ass_segment1 std_item_id creation_date
56815 NB5E012 319055 2012-8-8 14:43:14我写的sql语句,只能得到表二,哪位能帮忙得到表三呢??????????????
(用select top得出的不算)select zth.tbgc_id,zth.ass_segment1,zth.std_item_id,zth.creation_date
from zdoe_tbgc_head zth
where zth.std_item_id in
(select std_item_id
from zdoe_tbgc_head t
where std_item_id = zth.std_item_id)
26810 QQ12123 101011 2012-6-1 14:43:14
11810 GG22331 212100 2012-5-1 14:43:14
这两条应写有的
--看下这个是不是你要结果
select * from zdoe_tbgc_head zth
where creation_date=(select max(creation_date)
from zdoe_tbgc_head zth1 where zth.std_item_id=zth1.std_item_id);
select zth.tbgc_id,zth.ass_segment1,zth.std_item_id,zth.creation_date
from zdoe_tbgc_head zth
where zth.std_item_id in
(select std_item_id
from zdoe_tbgc_head t
where std_item_id = zth.std_item_id)
--你这个sql应该不能找到重复的std_item_id的吧
select * from (
select tbgc_id,ass_segment1,std_item_id,creation_date,
row_number()over(partition by std_item_id order by creation_date desc)
rn from zdoe_tbgc_head where std_item_id=some(
select std_item_id from zdoe_tbgc_head group by std_item_id having count(1)>1
)) where rn=1
select tbgc_id,ass_segment1,std_item_id,creation_date,
row_number() over(partition by std_item_id order by creation_date desc) rn
from zdoe_tbgc_head
) where rn = 1;