--> 测试数据:#tb if object_id('tempdb.dbo.#tb') is not null drop table #tb create table #tb([id] int,[value] int) insert #tb select 1,90 union all select 1,60 union all select 2,90 union all select 2,90select [id],count(distinct [value]) from #tb group by id having count(distinct [value])>1
select [id] from #tb group by id having count(distinct [value])>1
if object_id('tempdb.dbo.#tb') is not null drop table #tb create table #tb([id] int,[value] int) insert #tb select 1,90 union all select 1,60 union all select 2,90 union all select 2,90select [id],count(distinct [value]) from #tb group by id having count(distinct [value])>1 (所影响的行数为 4 行)id value ----------- ----------- 1 90 1 60(所影响的行数为 2 行)
if object_id('tempdb.dbo.#tb') is not null drop table #tb create table #tb([id] int,[value] int) insert #tb select 1,90 union all select 1,60 union all select 2,90 union all select 2,90select * from #tb where [id] in (select [id] from #tb group by id having count(distinct [value])>1) (所影响的行数为 4 行)id value ----------- ----------- 1 90 1 60(所影响的行数为 2 行)
select value from tabl1 where id=1
if object_id('tempdb.dbo.#tb') is not null drop table #tb
create table #tb([id] int,[value] int)
insert #tb
select 1,90 union all
select 1,60 union all
select 2,90 union all
select 2,90select [id],count(distinct [value]) from #tb
group by id
having count(distinct [value])>1
group by id
having count(distinct [value])>1
create table #tb([id] int,[value] int)
insert #tb
select 1,90 union all
select 1,60 union all
select 2,90 union all
select 2,90select [id],count(distinct [value]) from #tb
group by id
having count(distinct [value])>1
(所影响的行数为 4 行)id value
----------- -----------
1 90
1 60(所影响的行数为 2 行)
create table #tb([id] int,[value] int)
insert #tb
select 1,90 union all
select 1,60 union all
select 2,90 union all
select 2,90select * from #tb
where [id] in
(select [id] from #tb
group by id
having count(distinct [value])>1)
(所影响的行数为 4 行)id value
----------- -----------
1 90
1 60(所影响的行数为 2 行)