SQL 如何在查询中不使用不同的情况下获得相同的结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7084511/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get same results without using distinct in query
提问by Amanda Kitson
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:
我想检索一个记录列表，显示具有相同 ID 的记录之间的关系。例如，我想从我的查询中得到以下结果集：
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.
无论如何，我是否可以在不使用 select 语句中的“distinct”的情况下实现相同的结果集？如果我不包括不同的，我会得到 16 条记录，而不是 4 条。
回答by Matt Fenwick
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.
您在没有 DISTINCT 的情况下获得重复行的原因是因为 ID = x 的每一行都将与 ID = x 的所有其他行连接。由于原始表有 (1, "Bob") 两次，这两个都将连接到另一个表中 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):
类似于（使用 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
Edit: is B an alias for table A?
编辑：B 是表 A 的别名吗？
回答by Geek
In SQL and MY SQL
在 SQL 和 MY SQL 中
SELECT COLUMN_NAME FROM TABLE_NAME group by COLUMN_NAME
回答by Amanda Kitson
Have you tried using a group byclause?
您是否尝试过使用group by子句？
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 distinctquery above. As for the result set that you want, a simple self join should do it:
这应该让你得到与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)
回答by pooranendu patel
Eliminating duplicate values with union without using distinct
使用union消除重复值而不使用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
http://www.w3hattrick.com/2016/05/getting-distinct-rows-or-value-using.html
