SQL 多个不明显
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/269605/
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
Multiple NOT distinct
提问by Charles Bretana
I've got an MS access database and I would need to create an SQL query that allows me to select all the not distinct entries in one column while still keeping all the values.
我有一个 MS Access 数据库，我需要创建一个 SQL 查询，它允许我选择一列中所有不不同的条目，同时仍然保留所有值。
In this case more than ever an example is worth thousands of words:
在这种情况下，一个例子比以往任何时候都更有价值：
Table:
桌子：
A B C
1 x q
2 y w
3 y e
4 z r
5 z t
6 z y
SQL magic
SQL魔法
Result:
结果：
B C
y w
y e
z r
z t
z y
Basically it removes all unique values of column B but keeps the multiple rows of the data kept. I can "group by b" and then "count>1" to get the not distinct but the result will only list one row of B not the 2 or more that I need.
基本上它删除了 B 列的所有唯一值，但保留了多行数据。我可以“按 b 分组”然后“计数> 1”以获得不明显的但结果只会列出 B 的一行，而不是我需要的 2 个或更多。
Any help?
有什么帮助吗？
Thanks.
谢谢。
回答by Charles Bretana
Select B, C
From Table
Where B In
(Select B From Table
Group By B
Having Count(*) > 1)
回答by Dave DuPlantis
Another way of returning the results you want would be this:
返回您想要的结果的另一种方法是：
select *
from
my_table
where
B in
(select B from my_table group by B having count(*) > 1)
回答by a2800276
select
*
from
my_table t1,
my_table t2
where
t1.B = t2.B
and
t1.C != t2.C
-- apparently you need to use <> instead of != in Access
-- Thanks, Dave!
Something like that?
类似的东西？
回答by Paul Morgan
join the unique values of B you determined with group by b and count > 1 back to the original table to retrieve the C values from the table.
将您用 group by b 和 count > 1 确定的 B 的唯一值连接回原始表以从表中检索 C 值。
