选择具有相同 ID 的行并选择没有相同 ID 的剩余行

发布于 2024-12-20 13:19:48 字数 796 浏览 2 评论 0原文

我需要首先选择具有公共 ID 的行，然后选择没有公共 ID 的剩余行（无重复）。

这就是我到目前为止所拥有的：

SELECT DISTINCT a.cars,b.wheels,c.glass
FROM table auto a, tires b, window c
WHERE a.ID = b.ID AND c.ID = a.ID
????AND/OR a.ID <> b.ID????

<--最后选择 A 和 B 没有公共 ID 但没有公共 ID 的行重复记录

汽车

id cars
1   data
2   data
3   data
4   data

轮胎

id wheels
1   data
2   data
5   data  <-- ID different from table 'Auto' but still want to select it
9   data  <-- ID different from table 'Auto' but still want to select it
200 data  <-- ID different from table 'Auto' but still want to select it

车窗

id glass
1  data
2  data
3  data
4  data

原文

I need to select rows with common IDs first, then the remaining rows without the common id (no repeats.)

This is what I have so far:

SELECT DISTINCT a.cars,b.wheels,c.glass
FROM table auto a, tires b, window c
WHERE a.ID = b.ID AND c.ID = a.ID
????AND/OR a.ID <> b.ID????

<--lastly select rows where A and B do not have common ID's but with no duplicate records

Auto

id cars
1   data
2   data
3   data
4   data

Tires

id wheels
1   data
2   data
5   data  <-- ID different from table 'Auto' but still want to select it
9   data  <-- ID different from table 'Auto' but still want to select it
200 data  <-- ID different from table 'Auto' but still want to select it

Window

id glass
1  data
2  data
3  data
4  data

分享到QQ

分享到微博

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

发布评论

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

随波逐流 2024-12-27 13:19:48

使用 LEFT JOIN，而不是通过 WHERE 子句使用的隐式 INNER JOIN，将从 auto 表中返回匹配和不匹配的行。

SELECT DISTINCT a.cars, b.wheels, c.glass
    FROM auto a
        LEFT JOIN tires b
            ON a.id = b.id
        LEFT JOIN window c
            ON a.id = c.id
    ORDER BY a.cars, b.wheels, c.glass

Using a LEFT JOIN, instead of the implicit INNER JOIN you have via the WHERE clause, will return both matching and unmatched rows from the auto table.

SELECT DISTINCT a.cars, b.wheels, c.glass
    FROM auto a
        LEFT JOIN tires b
            ON a.id = b.id
        LEFT JOIN window c
            ON a.id = c.id
    ORDER BY a.cars, b.wheels, c.glass

回复收藏 0 原文

~没有更多了~