高手看过来,高分请教SQL语句

[bladewing]

test表的内容:
ID a b
1 1 2
2 1 3
3 2 3
4 2 3
5 3 1
6 4 1
要求：列出字段a的重复项，即得到结果为:
ID a b
1 1 2
2 1 3
3 2 3
4 2 3

[bladewing]

自己顶

[Softlee81307]

select * from test where a in
(select a from test group by a having count(*)>1)

[dzhfly]

支持楼上的

[Softlee81307]

Create table test(id int,a int,b int)
insert into test
select 1,1,2 union all
select 2,1,3 union all
select 3,2,3 union all
select 4,2,3 union all
select 5,3,1 union all
select 6,4,1

----------------實現語句------------------

select * from test where a in
(select a from test group by a having count(*)>1)

--------------結果---------------------
ID a b
1 1 2
2 1 3
3 2 3
4 2 3
--------------------------------
drop table test ---------刪除測試

[hai2003xp]

也可以這樣

select a.* from test a ,(select a from test group by a having count(*)>1)b
where a.a=b.a

[liricn]

上面的很好啊，可以揭帖了

[子陌红尘]

select t.* from test t
where exists (select 1 from test where a = t.a and ID != t.id)

[winternet]

select t.ID,t.a,t.b
from test as t
inner join
(select a from test group by a having count(a)>1)d
on t.a=d.a

[lishengyu]

select distinct a.* from test a join test b on a.a=b.a and a.id<>b.id

[wjmsino]

我想借此问下：

如果在得到上面结果的我要求只得a例有重复项的且b例项是最小的。
也是只得到
ID a b
1 1 2
3 2 3
而下面这些不要显示

2 1 3
4 2 3

[zhaovbo]

select t.* from test t
where exists (select 1 from test where a = t.a and ID > t.id)

[Softlee81307]

Create Table Test(Id int,a int,b int)
insert into test
select 1,1,2 union all
select 2,1,3 union all
select 3,2,3 union all
select 4,2,3 union all
select 5,3,1 union all
select 6,4,1

-----------下面語句----------------
select min(id),a,min(b) from test group by a having count(*)>1
-------------結果------------------
ID a b
1 1 2
3 2 3

drop table test ----------刪除測試

[jiang130]

呵呵!having和where的區別是having可帶含聚合函數的條件!

[txlicenhe]

如果在得到上面结果的我要求只得a例有重复项的且b例项是最小的。
也是只得到
ID a b
1 1 2
3 2 3
而下面这些不要显示

2 1 3
4 2 3
-------------------------------------
是ID最小的吧？


select t.* from test t
where exists (select 1 from test where a = t.a and ID > t.id)
and not exists(select 1 from test where a = t.a and ID < t.id)

[bladewing]

感谢各位，马上结帖！