英文:
Merge two tables and remove duplicates using join
问题
我尝试将两个表连接起来，但出现了重复的条目。以下是我的查询。
table1
| col_id | col_name |
|---|---|
| 1 | 1234 |
| 2 | 2345 |
| 2 | 2654 |
| 3 | 4654 |
| 3 | 4054 |
table2
| col_id | col_status |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
我希望的结果如下：
| col_id | col_status | col_name |
|---|---|---|
| 1 | 1 | 1234 |
| 2 | 2 | 2345 |
| 3 | 1 | 4654 |
我尝试使用distinct和join，但是仍然出现了重复。
英文:
I am trying to join two tables but I am getting duplicate entries. below is my query.
table1
| col_id | col_name |
|---|---|
| 1 | 1234 |
| 2 | 2345 |
| 2 | 2654 |
| 3 | 4654 |
| 3 | 4054 |
table2
| col_id | col_status |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
I am looking for result like
| col_id | col_status | col_name |
|---|---|---|
| 1 | 1 | 1234 |
| 2 | 2 | 2345 |
| 3 | 1 | 4654 |
I tried with distinct and join but I am getting duplicate.
答案1
得分: 0
你可以使用 GROUP BY 子句结合聚合函数 MAX() 来从查询中去除重复项。
例如：
SELECT table1.col_id, table2.col_status, MAX(table1.col_name) AS col_name
FROM table1
JOIN table2 ON table1.col_id = table2.col_id
GROUP BY table1.col_id, table2.col_status;
英文:
You can use the GROUP BY clause along with the aggregate function MAX() to remove the duplicates from your query.
For example:
SELECT table1.col_id, table2.col_status, MAX(table1.col_name) AS col_name
FROM table1
JOIN table2 ON table1.col_id = table2.col_id
GROUP BY table1.col_id, table2.col_status;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论