？？？请问这样的语句应如何写？？？

[lantianhf]

如何选出相同的id对应不同的cpid记录，只选出这些记录就行。

id cpid
0001 01
0001 01
0003 01
0015 03
0015 05
0018 07
0018 09

选出的结果

id cpid
0015 03
0015 05
0018 07
0018 09

[Mr_Nice]

--> 测试数据：[T1]
if object_id('[T1]') is not null drop table [T1]
create table [T1]([id] varchar(4),[cpid] varchar(2))
insert [T1]
select '0001','01' union all
select '0001','01' union all
select '0003','01' union all
select '0015','03' union all
select '0015','05' union all
select '0018','07' union all
select '0018','09'

select * from [T1]

SELECT  id ,cpid FROM T1 WHERE id IN (SELECT id FROM (SELECT DISTINCT id ,cpid FROM T1)B GROUP BY id HAVING COUNT(1) >=2)

/*
id	cpid
0015	03
0015	05
0018	07
0018	09*/

[Shawn]

CREATE TABLE #temp
(
	id VARCHAR(10),
	cpid VARCHAR(10)
)
INSERT #temp
select '0001', '01' union all
select '0001', '01' union all
select '0003', '01' union all
select '0015', '03' union all
select '0015', '05' union all
select '0018', '07' union all
select '0018', '09'
go
--SQL:
SELECT A.* FROM #temp A
INNER JOIN
(
	SELECT id FROM #temp
	GROUP BY id
	HAVING COUNT(*) = COUNT(DISTINCT cpid) AND COUNT(*) > 1
) B
ON A.id = B.id
/*
0015	03
0015	05
0018	07
0018	09
*/

[liang145]

create table #t1
(id nvarchar(10), cpid nvarchar(10))
insert #t1 select '0001', '01'union all
select '0001', '01'union all
select '0003', '01'union all
select '0015', '03'union all
select '0015', '05'union all
select '0018', '07'union all
select '0018', '09'

select * from #t1 as t1 
where exists(select 1 from #t1 as t2 where t2.id=t1.id and t2.cpid<>t1.cpid)

[一直学习]

if object_id('[T1]') is not null drop table [T1]
create table [T1]([id] varchar(4),[cpid] varchar(2))
insert [T1]
select '0001','01' union all
select '0001','01' union all
select '0003','01' union all
select '0015','03' union all
select '0015','05' union all
select '0018','07' union all
select '0018','09'



select * from t1 as a where 2<=
(select count(*) from (
(select  distinct * from t1 where a.id=id )
) as tt)


/*
id     cpid
----  ----
0015	03
0015	05
0018	07
0018	09

*/

[zy35147972122]

select * from 表名 where id in
(select id from 表名 where id not in
(select id from 表名 group by cpid,id having count(cpid)>1)
group by id having count(id)>1)