我有两张桌子。每次更新行时,都会插入一个带有变体id的新行。该表如下所示:
第一个表:
ID1 variation_id1
111 1
111 1第二个表:
ID2 ID-LineNO variation_id2
111 10 1
111 20 1
111 10 2
111 20 2结果应该是:
ID1 ID2 ID-LineNO variation_id2
111 111 10 2
111 111 20 2 我想根据最新的变体ID以及相应的行号来选择id。
Select DISTINCT ID2,ID1,ID-LineNO,variationid1
from second table
inner join first table on ID1 = ID2
where variation_id2 in (select max(variation_id2)
from second table
where variation_id2 = variation_id2
group by ID2)我尝试了上面的代码来获得结果,但仍然得到重复的rows.Can,请您帮助我如何消除基于变体id的重复行。
发布于 2018-04-20 21:36:27
在子查询中,您需要为表添加别名,以便子查询与外部查询相关,而不是与自身相关:
Select DISTINCT ID2,ID1,ID-LineNO,variationid1
from second_table t2
inner join first_table t1 on t1.ID1 = t2.ID2
where variation_id2 in (select max(variation_id2)
from second_table t3
where t3.variation_id2 = t2.variation_id2
group by ID2)发布于 2018-04-20 21:09:12
我不确定是否理解这个问题,在第一个表中没有主键,在第二个表中您没有指定主键,但我认为您在select子句中写错了"variationid“。我想有一个最清楚的例子,在这不需要写WHERE子句
SELECT DISTINCT ID2,ID1,ID-LineNO,variationid2
FROM second_table INNER JOIN first_table on ID2 = ID1
WHERE variationid2 in (
SELECT max(variation_id2)
FROM second_table
GROUP BY ID2
)发布于 2018-04-20 22:50:55
如果您再次使用Max(variation_Id2)加入,您将能够获得所需的结果。
Select DISTINCT [second table].ID2,ID1,[ID-LineNO],variation_id2
from [second table]
inner join [first table] on ID1 = [second table].ID2
INNER JOIN (SELECT Id2, MAX(variation_Id2) AS MaxiD FROM [second table] GROUP BY Id2) S ON S.Id2 = [second table].Id2
WHERE variation_id2 = S.MaxiDhttps://stackoverflow.com/questions/49941952
复制相似问题