SQL LEFT JOIN 仅第一行

Question

假设我们有这样的数据集:

表:DataTable1

ID      ExperienceId   LanguageId    ...
-------------------------------------------
1       1              1
2       1              2
3       1              3
4       2              1
5       2              2
6       2              3
7       3              1
8       3              2
9       3              3
...

表:DataTable2

ID      SomeId OtherId LanguageId    ...
-------------------------------------------
1       459    1       1
2       459    1       2
3       459    1       3
4       245    2       1
5       245    2       2
6       245    2       3
7       295    3       1
8       295    3       2
9       295    3       3
...

我想加入这些表并只获取 SomeId 列而忽略 LanguageId 列。为了更清楚:

SELECT
    t2.SomeId AS RequiredId
    -- ...other data mainly from t2
FROM DataTable1 AS t1
LEFT JOIN DataTable2 AS t2 
    ON t2.OtherId = t1.ExperienceId 
    AND t2.LanguageId = 
        (SELECT TOP 1 t1.LanguageId
         ORDER BY t1.LanguageId)

这个查询应该返回(如果它没有错，很明显)行:

SomeId    ...
----------------
459       ...
245       ...
295       ...
...

现在它返回三倍的相同数据(只有 LanguageId 不同)。

我会尝试用 WHERE t1.LanguageId = 1 过滤它如果我确定它总是存在，但我不确定。行可以用 LanguageId从 1 到 3，它们也只能是 ID 2，等等。行肯定会有至少一个 LanguageId .

现在我的问题是:如何在完全忽略一列的情况下连接具有唯一值的表？

Answer 1

将它包装在另一个查询中是否有用？

SELECT RequiredId, <all_the_other_fields> from (
SELECT t2.SomeId AS RequiredId
-- ...other data mainly from t2
FROM DataTable1 AS t1
   LEFT JOIN DataTable2 AS t2 
   ON t2.OtherId = t1.ExperienceId 
   AND t2.LanguageId = 
      (SELECT TOP 1 t1.LanguageId
      ORDER BY t1.LanguageId)
   ) group by RequiredId, <all_the_other_fields> 

甚至不首先提取列？

SELECT distinct t2.SomeId AS RequiredId
-- ...other data mainly from t2 BUT not the Language id
FROM DataTable1 AS t1
   LEFT JOIN DataTable2 AS t2 
   ON t2.OtherId = t1.ExperienceId 
   AND t2.LanguageId = 
      (SELECT TOP 1 t1.LanguageId
      ORDER BY t1.LanguageId)