求SQL语句由
id num
1 2
1 3
1 3
2 1
2 2
2 3
3 4
3 4
查询出
id num
1 2
1 3
1 3
3 4
3 4
id=1和id=3的所有行出现,因为它们的num有重复,如何作?
id num
1 2
1 3
1 3
2 1
2 2
2 3
3 4
3 4
查询出
id num
1 2
1 3
1 3
3 4
3 4
id=1和id=3的所有行出现,因为它们的num有重复,如何作?
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (id int,num int)
insert into #T
select 1,2 union all
select 1,3 union all
select 1,3 union all
select 2,1 union all
select 2,2 union all
select 2,3 union all
select 3,4 union all
select 3,4select * from #T as t where id in (select id from #T group by id having count(num)<>count(distinct num))/*
id num
1 2
1 3
1 3
3 4
3 4
*/
declare @tb table ([id] int,[num] int)
Insert into @tb
select 1,2 union all
select 1,3 union all
select 1,3 union all
select 2,1 union all
select 2,2 union all
select 2,3 union all
select 3,4 union all
select 3,4select * from @tb where id in(
Select [id] from @tb
group by [id],[num]
having count(1)>1)
/*
id num
----------- -----------
1 2
1 3
1 3
3 4
3 4(5 row(s) affected)
*/
having count(*)>1