求一SQL语句，在线等大神。。

[yinsuxia]

表结构：
ID MDM_ID DEPT_ID HCPNAME
1 12345 1 aaa
2 12345 2 aaa
3 12345 3 bbb
4 12345 4 bbb
5 12345 5 ccc
6 12345 6 eee

结果：

12345 1 aaa
12345 2 aaa
12345 3 bbb
12345 4 bbb
从数据里面可以看到，同一个MDMID里面存在HCPname相同，但是deptID不同的的情况，我要怎么把这些人都查出来？

[人间太皮]

什么意思啊，查询MDM_ID,HCPNAME相同，DEPT_ID不同的记录？

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-11-13 16:01:28
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--	Dec 28 2012 20:23:12 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([ID] int,[MDM_ID] int,[DEPT_ID] int,[HCPNAME] varchar(3))
insert [huang]
select 1,12345,1,'aaa' union all
select 2,12345,2,'aaa' union all
select 3,12345,3,'bbb' union all
select 4,12345,4,'bbb' union all
select 5,12345,5,'ccc' union all
select 6,12345,6,'eee'
--------------开始查询--------------------------
SELECT *
FROM HUANG A
WHERE EXISTS (SELECT 1 FROM (
select MDM_ID,HCPNAME
from [huang]
GROUP BY MDM_ID,HCPNAME
HAVING COUNT(HCPNAME)>1)B WHERE A.MDM_ID=b.MDM_ID AND a.HCPNAME=b.HCPNAME)
----------------结果----------------------------
/* 
ID          MDM_ID      DEPT_ID     HCPNAME
----------- ----------- ----------- -------
1           12345       1           aaa
2           12345       2           aaa
3           12345       3           bbb
4           12345       4           bbb
*/

[lis_mode]

CREATE TABLE #A( ID INT , MDM_ID INT , DEPT_ID INT , HCPNAME VARCHAR(10) ) INSERT INTO #A SELECT 1, 12345, 1, 'aaa' UNION ALL SELECT 2, 12345, 2, 'aaa' UNION ALL SELECT 3, 12345, 3, 'bbb' UNION ALL SELECT 4, 12345, 4, 'bbb' UNION ALL SELECT 5, 12345, 5, 'ccc' UNION ALL SELECT 6, 12345, 6, 'eee' SELECT A.* FROM #A AS A, #A AS B WHERE A.MDM_ID = B.MDM_ID AND A.HCPNAME = B.HCPNAME AND A.DEPT_ID <> B.DEPT_ID ORDER BY A.MDM_ID, A.DEPT_ID

[阳泉酒家小当家]

适合2005及以后版本：

--drop table tb
--go

CREATE TABLE tb(
  ID INT
, MDM_ID INT
, DEPT_ID INT
, HCPNAME VARCHAR(10)
)

INSERT INTO tb
SELECT 1, 12345, 1, 'aaa' UNION ALL
SELECT 2, 12345, 2, 'aaa' UNION ALL
SELECT 3, 12345, 3, 'bbb' UNION ALL
SELECT 4, 12345, 4, 'bbb' UNION ALL
SELECT 5, 12345, 5, 'ccc' UNION ALL
SELECT 6, 12345, 6, 'eee'


select ID,MDM_ID,DEPT_ID,HCPNAME
from
(
select *,
       COUNT(*) over(partition by MDM_ID,HCPNAME) as c
from tb
)t
where c >= 2
/*
ID	MDM_ID	DEPT_ID	HCPNAME
1	12345	1	    aaa
2	12345	2	    aaa
3	12345	3	    bbb
4	12345	4	    bbb
*/