PROSAGA码农传奇-DB2-如何连接两个表而不为表中的不同值重复相同的行

0# 产品你是狗 | 2019-08-31 10-32

1# 拥有小太阳的向日葵 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”>
  
    如果你正在使用
     <code>
 SQL Server
 </code>
    ，您可以尝试以下。
  
   <pre>
 <code>
 SELECT c.id AS country_id, 
 c.country, 
 t.[user] 
FROM (SELECT s.country_id, 
 s.[user], 
 Row_number() 
 OVER ( 
 partition BY s.country_id 
 ORDER BY s.id) rn 
 FROM @second s) t 
 LEFT JOIN @country c 
 ON t.country_id = c.id 
 AND t.rn = 1

</code>
 </pre>
  
    的
      <a href="https://rextester.com/ZYDI72034" rel="nofollow noreferrer">
        在线演示
      </A>
    </强>
  
  
    的
      产量
    </强>
  
   <pre>
 <code>
 +------------+-----------+-------+
| country_id | country | user |
+------------+-----------+-------+
| 1 | India | Ojas |
+------------+-----------+-------+
| NULL | NULL | Raj |
+------------+-----------+-------+
| NULL | NULL | Rohit |
+------------+-----------+-------+
| 2 | Australia | Robin |
+------------+-----------+-------+
| 3 | Canada | John |
+------------+-----------+-------+
| 4 | France | Kamal |
+------------+-----------+-------+
| 5 | Russia | Mary |
+------------+-----------+-------+
| NULL | NULL | Bipin |
+------------+-----------+-------+

</code>
 </pre>
  
    的
      对于其他数据库：
    </强>
  
  
    可以不用编写相同的查询
     <code>
 ROW_NUMBER()
 </code>
     喜欢以下，以便它可以在其他数据库上工作
     <code>
 mysql
 </code>
    。
  
   <pre>
 <code>
 SELECT c.id AS country_id, 
 c.country, 
 t.user 
FROM (SELECT s.country_id, 
 s.user, 
 s.id,
 CASE 
 WHEN (SELECT Min(id) 
 FROM second s2 
 WHERE s2.country_id = s.country_id) = s.id THEN 1 
 ELSE 0 
 END rn 
 FROM second s) t 
 LEFT JOIN country c 
 ON t.country_id = c.id 
 AND t.rn = 1 
ORDER BY t.country_id ,t.id

</code>
 </pre>
  
    <a href="https://www.db-fiddle.com/f/wEH5hNRZduU4qcjebym4H7/1" rel="nofollow noreferrer">
      的
        MySQL db-fiddle
      </强>
    </A>
  
</DIV>

2# CTO啊哦 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”>
  
    在这里，您可以分别使用mysql和PostgreSql GROUP_CONCAT以及string_agg（some_column，'，'）方法的聚合方法将名称与逗号连接，并为每个国家/地区获取1条记录。
  
  <BLOCKQUOTE>
    
      对于Mysql
    
  </BLOCKQUOTE>
   <pre>
 <code>
 SELECT countries.id AS Id, countries.name AS Country, GROUP_CONCAT(DISTINCT users.name) as Users
FROM countries
INNER JOIN users ON users.country_id = countries.id
GROUP BY countries.id;

</code>
 </pre>
  <BLOCKQUOTE>
    
      对于PostgreSql
    
  </BLOCKQUOTE>
   <pre>
 <code>
 SELECT countries.id AS Id, countries.name AS Country, string_agg(users.name, ',') as Users
FROM countries
INNER JOIN users ON users.country_id = countries.id
GROUP BY countries.id;

</code>
 </pre>
</DIV>