group by 多个字段的问题

面试的时候碰到一道面试题
有一张表，表名：Test 
表结构： 
          id   int 
          name   varchar(20) 
有以下数据： 
        [id]         [name] 
           001              aaa 
           002              aaa 
           003              bbb 
           004              ccc 
要求写一个查询语句，只显示[name]有重复的数据，没有重复的不显示出来。

只显示重复的name的话
我是这样写的：

select   count(name)   from     Test   group   by   name   Having   count(name)> 1 

如果要显示重复的name和它所对应的id的话
我是这样写的：

select * from test1 where [name] in (select [name] from test1 group by  [name] having count(test1.[name])<>1)

 
上面这两句都能正常查询到结果，然后，我想换种方法去查询重复的name和它所对应的id值 这样写：

select id, name from test1 group by id,name having count(test1.name) > 1

 但是查询不到相应的结果
我想知道为什么会这样，谁能帮我解答一下？多谢了！

18 个解决方案

#1

SELECT NAME,ID
FROM TEST1 T1
WHERE EXISTS(
SELECT 1 FROM TEST1 T2 WHERE T2.NAME=T1.NAME AND T2.ID<>T1.ID
)
ORDER BY NAME,ID

#2

SELECT * FROM TEST A
WHERE EXISTS (
SELECT 1
FROM TEST
WHERE NAME = A.NAME
AND ID <> A.ID
)

#3

引用 2 楼  的回复:

SQL code

SELECT * FROM TEST A
WHERE EXISTS (
SELECT 1
FROM TEST
WHERE NAME = A.NAME
AND ID <> A.ID
)

恩，我想问的是为什么我执行这句 select id, name from test1 group by id,name having count(test1.name) > 1
查询不到重复的name和它所对应的id,

#4

--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
create table [test]([id] varchar(3),[name] varchar(3))
insert [test]
select '001','aaa' union all
select '002','aaa' union all
select '003','bbb' union all
select '004','ccc'


select * from test a 
where exists(select 1 from test b where a.name=b.name and a.id<>b.id)
/*
id	name
001	aaa
002	aaa
*/

--这个结果？

#5

引用 3 楼  的回复:

引用 2 楼  的回复:
SQL code

SELECT * FROM TEST A
WHERE EXISTS (
SELECT 1
FROM TEST
WHERE NAME = A.NAME
AND ID <> A.ID
)

恩，我想问的是为什么我执行这句 select id, name from test1 group by id,name having count……

兄弟，group by 是根据你填写的字段进行分组排列，如果只是按照name进行分组，没有问题
但是如果你按照ID跟name联合分组是不可能的，因为ID是不重复的，你对一个不重复的列进行分组，是没有任何意义的

#6

引用 5 楼  的回复:

引用 3 楼  的回复:

引用 2 楼  的回复:
SQL code

SELECT * FROM TEST A
WHERE EXISTS (
SELECT 1
FROM TEST
WHERE NAME = A.NAME
AND ID <> A.ID
)

恩，我想问的是为什么我执行这句 select id, name from test1 group by id,n……

-- 也可以用group by all实现
select avg(id), name 
from test1 
group by all name 
having count(1) > 1

-- 如果使用 ALL 关键字，那么查询结果将包括由 GROUP BY 子句产生的所有组
-- 即使某些组没有符合搜索条件的行。没有 ALL 关键字
-- 包含 GROUP BY 子句的 SELECT 语句将不显示没有符合条件的行的组。

#7

引用 5 楼  的回复:

引用 3 楼 的回复:

引用 2 楼 的回复:
SQL code

SELECT * FROM TEST A
WHERE EXISTS (
SELECT 1
FROM TEST
WHERE NAME = A.NAME
AND ID <> A.ID
)

恩，我想问的是为什么我执行这句 select id, name from test1 group by id,name……

对不重复的列进行分组是没有意义的，这我明白呀，group by 后面有两个字段 id和name,那在执行的时候应该是先按照id分组，然后才对name分组，可是好像我执行这句 select id, name from test1 group by id,name having count(test1.name) > 1 并没有起到按照name分组的作用，不然的话怎么会查询不到结果呢

#8

引用 6 楼  的回复:

引用 5 楼  的回复:

引用 3 楼  的回复:

引用 2 楼  的回复:
SQL code

SELECT * FROM TEST A
WHERE EXISTS (
SELECT 1
FROM TEST
WHERE NAME = A.NAME
AND ID <> A.ID
)

恩，我想问的是为什么我执行这句 select id, name from tes……

错了，还是不对，用all的必须是需要进行计算才能输出其他列。

#9

--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
create table [test]([id] varchar(3),[name] varchar(3))
insert [test]
select '001','aaa' union all
select '002','aaa' union all
select '003','bbb' union all
select '004','ccc'

with t
as(
select *,px=COUNT(1)over(partition by name)
from test
)
select id,name from t where px>=2
/*
id	name
001	aaa
002	aaa
*/

#10

引用 7 楼  的回复:

引用 5 楼  的回复:
引用 3 楼 的回复:

引用 2 楼 的回复:
SQL code

SELECT * FROM TEST A
WHERE EXISTS (
SELECT 1
FROM TEST
WHERE NAME = A.NAME
AND ID <> A.ID
)

恩，我想问的是为什么我执行这句 select id, name from test1 g……

兄弟你还是不明白group by 啊

GROUP BY 子句限制结果集中的行；对于分组列中的每个非重复值只有一行。

#11

引用 10 楼  的回复:

引用 7 楼 的回复:

引用 5 楼 的回复:
引用 3 楼 的回复:

引用 2 楼 的回复:
SQL code

SELECT * FROM TEST A
WHERE EXISTS (
SELECT 1
FROM TEST
WHERE NAME = A.NAME
AND ID <> A.ID
)

恩，我想问的是为什么我执行这句 select id, name……

我知道group by的意思呀，我的想法是先照id分组，结果应为
id name
001 aaa
002 aaa
003 bbb
004 ccc
然后再按照name分组，那结果应该是
id name
001 aaa
003 bbb
004 ccc 
才对呀，可为什么不是这样呢？
我想问的就是这个问题呀

#12

引用 11 楼  的回复:

引用 10 楼  的回复:
引用 7 楼 的回复:

引用 5 楼 的回复:
引用 3 楼 的回复:

引用 2 楼 的回复:
SQL code

SELECT * FROM TEST A
WHERE EXISTS (
SELECT 1
FROM TEST
WHERE NAME = A.NAME
AND ID <> A.ID
)

恩，我想问的是为什么我执行这句……

使用group by进行多列分组，其实就是按照原表中选中列的所有不同情况分组，该语句的结果数目与select distinct 是一样多的。

#13

--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
create table [test]([id] varchar(3),[name] varchar(3))
insert [test]
select '001','aaa' union all
select '002','aaa' union all
select '003','bbb' union all
select '004','ccc'

select * from test a
where not exists(select 1 from test b 
where a.name=b.name and a.id>b.id)
/*
id	name
001	aaa
003	bbb
004	ccc
*/

--早吧这个结果给出来不就好了么？

#14

select id, name from test1 group by id,name having count(test1.name) > 1

Group By 后面加两个字段名 分组时按哪一个分组呢。后面家having子句的可以Group by两个字段名么-.-

#15

明白怎么回事了，group by后面如果接多个字段的话，先按照group by后的第一列分组，如果第一列值相同，在按照第二列分组，而这里的id值没有重复的，所以没有对name字段进行分组，起不到按照name字段进行分组的作用，所以自然查询不到结果

#16

引用 9 楼  的回复:

SQL code

--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
create table [test]([id] varchar(3),[name] varchar(3))
insert [test]
select '001','aaa' union all
select '002','aaa' un……

想请问一下这个 select 1 是怎么理解呢，好像是跟select * 一样的结果

#17

大家真的很利害。

#18

引用 16 楼  的回复:

引用 9 楼 的回复:

SQL code

--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
create table [test]([id] varchar(3),[name] varchar(3))
insert [test]
select '001','aaa' union all
s……

select 1 其实没什么特别的意思，就是select 一个指定的值，因为我的目地是判断是否有存在，所以不需要返回任何字段信息。写select 1比返回字段信息效率更高。。
select 1 from table where 1=1  。。
这样查询结果就是 1 ，行数等同于 select * from table where 1=1  。。的行数。