如何对内连接进行连接？

发布于 2024-12-02 12:45:16 字数 1064 浏览 3 评论 0原文

我有这个数据库：

table

id  fname       dphone      count_pic   dup_id  

6055903 Karla       5126xxx798  1       57  
6173767 Aaliyah     4082xxx534  4       39  
5611411 Aaliyah     4082xxx534  15      39  
5611211 Aaliyah     4082xxx534  18      39  
4234798 Abby        3057xxx974  31      16  
6166691 Walter      6178xxx280  1       74  
3375576 Walter      6178xxx280  17      74

，我发现了如何对其进行内部联接，如下所示：

SELECT *
  FROM table t1
INNER JOIN (SELECT MIN(count_pic) AS minpic,
               MAX(count_pic) AS maxpic,
               dup_id
          FROM table
      GROUP BY dup_id) t2 ON t1.dup_id = t2.dup_id
                         AND (t1.count_pic = minpic
                           OR t1.count_pic = maxpic)

但是如果我想根据id将该表与另一个表联接怎么办？并从第二个表中返回一些值，例如日期：

table2

    id  date

6055903 111111111
6173767 111111111
5611411 111111111

对此有什么想法吗？

编辑：

内部联接就这样，我需要在该查询之上添加 table2

原文

I have this database:

table

id  fname       dphone      count_pic   dup_id  

6055903 Karla       5126xxx798  1       57  
6173767 Aaliyah     4082xxx534  4       39  
5611411 Aaliyah     4082xxx534  15      39  
5611211 Aaliyah     4082xxx534  18      39  
4234798 Abby        3057xxx974  31      16  
6166691 Walter      6178xxx280  1       74  
3375576 Walter      6178xxx280  17      74

and I found out how to do an inner join on it like this:

SELECT *
  FROM table t1
INNER JOIN (SELECT MIN(count_pic) AS minpic,
               MAX(count_pic) AS maxpic,
               dup_id
          FROM table
      GROUP BY dup_id) t2 ON t1.dup_id = t2.dup_id
                         AND (t1.count_pic = minpic
                           OR t1.count_pic = maxpic)

but what if I want to join this table with another one based on id and return some values , like date,from the second table as well:

table2

    id  date

6055903 111111111
6173767 111111111
5611411 111111111

Any ideas on this?

edit:

the inner join is fine the way it is, i need to add the table2 on top of that query

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

野却迷人 2024-12-09 12:45:16

只需在末尾添加另一个 JOIN 即可：

SELECT *
  FROM table t1
INNER JOIN (SELECT MIN(count_pic) AS minpic,
               MAX(count_pic) AS maxpic,
               dup_id
          FROM table
      GROUP BY dup_id) t2 ON t1.dup_id = t2.dup_id
                         AND (t1.count_pic = minpic
                           OR t1.count_pic = maxpic)

INNER JOIN table2 ON t1.id = table2.id -- add this

Just add another JOIN at the end:

SELECT *
  FROM table t1
INNER JOIN (SELECT MIN(count_pic) AS minpic,
               MAX(count_pic) AS maxpic,
               dup_id
          FROM table
      GROUP BY dup_id) t2 ON t1.dup_id = t2.dup_id
                         AND (t1.count_pic = minpic
                           OR t1.count_pic = maxpic)

INNER JOIN table2 ON t1.id = table2.id -- add this

回复收藏 0 原文

~没有更多了~