SQL LEFT JOIN仅第一行 [英] SQL LEFT JOIN first row only

问题描述

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

Let's assume we have such data set:

表:DataTable1

Table: 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

Table: 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列.使其更清楚:

I want to join those tables and get only SomeId column ignoring the LanguageId column. To make it clearer:

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)

此查询应返回(如果没有错，很明显)行:

This query should return (if it wasn't wrong, clearly) rows:

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

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

Now it returns three times of identical data (with only LanguageId different).

如果我确定它始终存在，我会尝试使用WHERE t1.LanguageId = 1对其进行过滤，但是我不确定.行可以具有LanguageId从1到3，也只能具有ID 2，依此类推.行肯定会至少具有一个LanguageId.

I would try to filter it with WHERE t1.LanguageId = 1 if I was sure it always exists, but I'm not sure. Rows can be with LanguageId from 1 to 3, also they can be only with ID 2, etc. Rows surely will have at least one LanguageId.

现在我的问题是:如何将具有唯一值的表联接在一起而其中一列被完全忽略?

推荐答案

将其包装在另一个查询中有技巧吗?

Wrapping it in another query does the trick?

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)

这篇关于SQL LEFT JOIN仅第一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！