如何在查询中不使用不同的情况下获得相同的结果

发布于 2024-11-30 01:52:09 字数 445 浏览 0 评论 0原文

我有一个包含如下数据的表：

[ID, Name]
1, Bob
1, Joe
1, Joe
1, Bob

我想检索一个记录列表，显示具有相同 ID 的记录之间的关系。例如，我希望从查询中得到以下结果集：

Bob, Joe
Joe, Bob
Bob, Bob
Joe, Joe

这显示了表中每个项目的“from”和“to”。

我可以通过使用以下查询获得此结果：

SELECT DISTINCT [NAME] 
FROM TABLE A
INNER JOIN TABLE B ON A.ID = B.ID

是否有办法在不使用 select 语句中的“distinct”的情况下获得相同的结果集？如果我不包括不同的记录，我会返回 16 条记录，而不是 4 条。

原文

I have a table with data like so:

[ID, Name]
1, Bob
1, Joe
1, Joe
1, Bob

I want to retrieve a list of records showing the relationship between the records with the same ID.
For instance, I want the following result set from my query:

Bob, Joe
Joe, Bob
Bob, Bob
Joe, Joe

This shows me the "from" and "to" for every item in the table.

I can get this result by using the following query:

SELECT DISTINCT [NAME] 
FROM TABLE A
INNER JOIN TABLE B ON A.ID = B.ID

Is there anyway for me to achieve the same result set without the use of the "distinct" in the select statement? If I don't include the distinct, I get back 16 records, not 4.

分享到QQ

分享到微博

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

发布评论

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

小兔几 2024-12-07 01:52:09

在没有 DISTINCT 的情况下获得重复行的原因是，ID = x 的每一行都将与 ID = x 的其他所有行连接起来。由于原始表有 (1, "Bob") 两次，因此这两个表都将连接到 ID = 1 的另一个表中的每一行。

在进行连接之前删除重复项将做两件事：减少运行查询的时间，并防止重复行出现在结果中。

类似于（使用 MySQL 版本的 SQL）：

SELECT L.NAME, R.NAME
FROM (SELECT DISTINCT ID, NAME FROM A) AS L
INNER JOIN (SELECT DISTINCT ID, NAME FROM B) AS R
ON L.ID = R.ID

编辑：B 是表 A 的别名吗？

The reason you get duplicate rows without DISTINCT is because every row of ID = x will be joined with every other row with ID = x. Since the original table has (1, "Bob") twice, both of those will be joined to every row in the other table with ID = 1.

Removing duplicates before doing a join will do two things: decrease the time to run the query, and prevent duplicate rows from showing up in the result.

Something like (using MySQL version of SQL):

SELECT L.NAME, R.NAME
FROM (SELECT DISTINCT ID, NAME FROM A) AS L
INNER JOIN (SELECT DISTINCT ID, NAME FROM B) AS R
ON L.ID = R.ID

Edit: is B an alias for table A?

回复收藏 0 原文

虚拟世界 2024-12-07 01:52:09

在 SQL 和 MY SQL 中

SELECT COLUMN_NAME FROM TABLE_NAME group by COLUMN_NAME

In SQL and MY SQL

SELECT COLUMN_NAME FROM TABLE_NAME group by COLUMN_NAME

回复收藏 0 原文

谁的新欢旧爱 2024-12-07 01:52:09

您是否尝试过使用group by子句？

select name
from table a
inner join table b
on a.id=b.id
group by name

这应该会得到与上面的 distinct 查询相同的结果。至于您想要的结果集，一个简单的自连接应该可以做到：

select name1,name2
from(
  select id,name as name1
  from table
  group by 1,2
  )a
join(
  select id,name as name2
  from table
  group by 1,2
  )b
using(id)

Have you tried using a group by clause?

select name
from table a
inner join table b
on a.id=b.id
group by name

That should get you the same thing as your distinct query above. As for the result set that you want, a simple self join should do it:

select name1,name2
from(
  select id,name as name1
  from table
  group by 1,2
  )a
join(
  select id,name as name2
  from table
  group by 1,2
  )b
using(id)

回复收藏 0 原文

靖瑶 2024-12-07 01:52:09

通过联合消除重复值而不使用不同

Declare @TableWithDuplicateValue Table(Name Varchar(255))
Insert Into @TableWithDuplicateValue Values('Cat'),('Dog'),('Cat'),('Dog'),('Lion')

Select Name From @TableWithDuplicateValue

union

select null where 1=0

Go

Output
---------
Cat
Dog
Lion

有关更多替代方案，请访问我的博客

http://www.w3hattrick.com/2016/05/getting-distinct-rows-or-value-using.html

Eliminating duplicate values with union without using distinct

Declare @TableWithDuplicateValue Table(Name Varchar(255))
Insert Into @TableWithDuplicateValue Values('Cat'),('Dog'),('Cat'),('Dog'),('Lion')

Select Name From @TableWithDuplicateValue

union

select null where 1=0

Go

Output
---------
Cat
Dog
Lion

For more alternate kindly visit my blog

http://www.w3hattrick.com/2016/05/getting-distinct-rows-or-value-using.html

回复收藏 0 原文

~没有更多了~