姓名 年度
牟志琼 2012
牟志琼 2012
牟志琼 2013
牟志琼 2013
牟志琼 2014
牟志琼 2014
陈辉 2012
陈辉 2013
陈绩 2012
陈绩 2014
马银武 2013
马银武 2013
想要年度字段里出现两个不同年度以上的记录，大概是这样的结果：
姓名 年度
牟志琼 2012
牟志琼 2013
牟志琼 2014
陈辉 2012
陈辉 2013
陈绩 2012
陈绩 2014
谢谢各位了！！！！！
------解决思路----------------------
DECLARE @t_TB TABLE ([Name] NVARCHAR(10),[YR] INT);
INSERT INTO @t_TB VALUES
('A','2012'),
('A','2012'),
('A','2013'),
('A','2013'),
('A','2014'),
('A','2014'),
('B','2012'),
('B','2013'),
('C','2012'),
('C','2014'),
('D','2013'),
('D','2013');
SELECT DISTINCT T.NAME,T.YR
FROM @t_TB T
INNER JOIN ( SELECT A.NAME
FROM (
SELECT DISTINCT [Name] ,[YR]
FROM @t_TB ) A
GROUP BY A.NAME
HAVING COUNT(A.NAME)>1 ) B ON T.NAME = B.NAME
/*
NAME YR
---------- -----------
A 2012
A 2013
A 2014
B 2012
B 2013
C 2012
C 2014
*/
------解决思路----------------------
SELECT DISTINCT A.NAME,A.YR
FROM @t_TB A
WHERE EXISTS( SELECT 1 FROM @t_TB B WHERE B.NAME = A.NAME AND B.YR <> A.YR )