求一SQL语句

[荷梅月剑]

name age

a 13
a 13
a 14
a 16
b 20
b 20
b 21
b 21
c 14
c 14
d 17
d 17
e 21
e 21
f 15
f 16

求name不同 age相同的结果，如这个表中数据我希望查出这个结果

a 14
c 14
b 21
e 21
a 16
f 16

[哥眼神纯洁不]

with tb(a,b) as (
select 'a',13 union all
select 'a',13 union all
select 'a',14 union all
select 'a',16 union all
select 'b',20 union all
select 'b',20 union all
select 'b',21 union all
select 'b',21 union all
select 'c',14 union all
select 'c',14 union all
select 'd',17 union all
select 'd',17 union all
select 'e',21 union all
select 'e',21 union all
select 'f',15 union all
select 'f',16 
)
select distinct a,b from tb
where b in
(select b from (select distinct * from  tb)a group by b having COUNT(1)>1)

这样？

[faron126]

select a.name,a.age from table as a,gis.g_base_area as b where a.age=b.age and a.name<>b.name group by a.name,a.age order by age;

[bobytomm]

呵呵，来要分了。

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-05-13 14:09:31
-- Version:
--      Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) 
--	Jun 17 2011 00:54:03 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([name] varchar(1),[age] int)
insert [huang]
select 'a',13 union all
select 'a',13 union all
select 'a',14 union all
select 'a',16 union all
select 'b',20 union all
select 'b',20 union all
select 'b',21 union all
select 'b',21 union all
select 'c',14 union all
select 'c',14 union all
select 'd',17 union all
select 'd',17 union all
select 'e',21 union all
select 'e',21 union all
select 'f',15 union all
select 'f',16
--------------开始查询--------------------------
SELECT  *
FROM    ( SELECT DISTINCT
                    CASE WHEN a.NAME <> b.NAME
                              AND a.age = b.age THEN a.NAME
                    END NAME ,
                    CASE WHEN a.NAME <> b.NAME
                              AND a.age = b.age THEN a.age
                    END age
          FROM      ( SELECT DISTINCT
                                NAME ,
                                age
                      FROM      [huang]
                    ) a
                    CROSS JOIN ( SELECT DISTINCT
                                        NAME ,
                                        age
                                 FROM   [huang]
                               ) b
        ) c
WHERE   name IS NOT NULL 

----------------结果----------------------------
/* 
NAME age
---- -----------
a    14
a    16
b    21
c    14
e    21
f    16
*/

[羊茂林]

select * from Table_1 where age in (select age from Table_1 group by age having count(*)>1)

[羊茂林]

select * from Table_1 where age in (select age from Table_1 group by age having count(*)>1) 

原理：子查询select age from Table_1 group by age having count(*)>1查出了年龄有重复的年龄