create table #t(ID int,price decimal(5,2),dscription varchar(20)) insert into #T select 1000,1.0,'wwwssa' insert into #T select 1001,1.2,'xhghfgh' insert into #T select 1000,1.0,'wwwssa' insert into #T select 1002,1.3,'hjj' insert into #T select 1001,2.0,'xhghfgh' insert into #T select 1002,1.3,'hjj' insert into #T select 1003,1.5,'bhghh' go select * from #T where id in ( select t.id from (select id from #T group by id,price)t group by t.id having count(t.id)>1 ) go drop table #T /* ID price dscription ----------- --------------------------------------- -------------------- 1001 1.20 xhghfgh 1001 2.00 xhghfgh */
相同ID出现两次和两次以上且price全不同 if object_id('tempdb.dbo.#T') is not null drop table #T create table #T (ID int,price numeric(2,1),dscription varchar(7)) insert into #T select 1000,1.0,'wwwssa' union all select 1000,1.0,'wwwssa' union all select 1001,1.2,'xhghfgh' union all select 1001,2.0,'xhghfgh' union all select 1002,1.3,'hjj' union all select 1002,1.3,'hjj' union all select 1003,1.5,'bhghh' union all select 1003,1.6,'bhghh' union all select 1003,1.5,'bhghh' select * from #t where id in( select id from #t a where (select count(*) from #T where a.id=id and a.price=price)=1 group by id having count(*)>=2)/* 1001 1.2 xhghfgh 1001 2.0 xhghfgh */
if object_id('tempdb.dbo.#T') is not null drop table #T create table #T (ID int,price numeric(2,1),dscription varchar(7)) insert into #T select 1000,1.0,'wwwssa' union all select 1000,1.0,'wwwssa' union all select 1001,1.2,'xhghfgh' union all select 1001,2.0,'xhghfgh' union all select 1002,1.3,'hjj' union all select 1002,1.3,'hjj' union all select 1003,1.5,'bhghh' union all select 1003,1.6,'bhghh' union all select 1003,1.5,'bhghh' union all select 1004,1.2,'bhghh' union all select 1004,1.1,'bhghh' union all select 1004,1.1,'bhghh' /* select distinct id,price from #t where id in (select id from #T group by id having count(*)>1 and max(price)>min(price)) /* id price 1001 1.2 1001 2.0 1003 1.5 1003 1.6 1004 1.1 1004 1.2 */ */
5楼是:相同ID出现两次和两次以上且price全不同 本次是:相同ID出现两次和两次以上且price有不同但并不一定是全不同 if object_id('tempdb.dbo.#T') is not null drop table #T create table #T (ID int,price numeric(2,1),dscription varchar(7)) insert into #T select 1000,1.0,'wwwssa' union all select 1000,1.0,'wwwssa' union all select 1001,1.2,'xhghfgh' union all select 1001,2.0,'xhghfgh' union all select 1002,1.3,'hjj' union all select 1002,1.3,'hjj' union all select 1003,1.5,'bhghh' union all select 1003,1.6,'bhghh' union all select 1003,1.5,'bhghh' union all select 1004,1.2,'bhghh1' union all select 1004,1.1,'bhghh2' union all select 1004,1.1,'bhghh3' select distinct id,price, (select top 1 dscription from #t where id=a.id and price=a.price) as dscription --这里面临当id 和 price相同时,取哪个description的问题 from #t as a where id in (select id from #T group by id having count(*)>1 and max(price)>min(price)) /* --结果 1001 1.2 xhghfgh 1001 2.0 xhghfgh 1003 1.5 bhghh 1003 1.6 bhghh 1004 1.1 bhghh2 1004 1.2 bhghh1 */
insert into #T select 1000,1.0,'wwwssa'
insert into #T select 1001,1.2,'xhghfgh'
insert into #T select 1000,1.0,'wwwssa'
insert into #T select 1002,1.3,'hjj'
insert into #T select 1001,2.0,'xhghfgh'
insert into #T select 1002,1.3,'hjj'
insert into #T select 1003,1.5,'bhghh'
go
select * from #T where id in (
select t.id from (select id from #T group by id,price)t group by t.id having count(t.id)>1
)
go
drop table #T
/*
ID price dscription
----------- --------------------------------------- --------------------
1001 1.20 xhghfgh
1001 2.00 xhghfgh
*/
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (ID int,price numeric(2,1),dscription varchar(7))
insert into #T
select 1000,1.0,'wwwssa' union all
select 1000,1.0,'wwwssa' union all
select 1001,1.2,'xhghfgh' union all
select 1001,2.0,'xhghfgh' union all
select 1002,1.3,'hjj' union all
select 1002,1.3,'hjj' union all
select 1003,1.5,'bhghh' union all
select 1003,1.6,'bhghh' union all
select 1003,1.5,'bhghh' select * from #t where id in(
select id from #t a where (select count(*) from #T where a.id=id and a.price=price)=1
group by id having count(*)>=2)/*
1001 1.2 xhghfgh
1001 2.0 xhghfgh
*/
表加上insert into tb select 1004 , 1.2 , 'bhghh'
insert into tb select 1004 , 1.1 , 'bhghh'
insert into tb select 1004 , 1.1 , 'bhghh' 就出现
/*
id price decription
1001 1.20 xhghfgh
1001 2.00 xhghfgh
1004 1.20 bhghh
1004 1.10 bhghh
1004 1.10 bhghh
*/按需求 答案应该是
/*
id price decription
1001 1.20 xhghfgh
1001 2.00 xhghfgh
1004 1.20 bhghh
1004 1.10 bhghh
*/所以与需求不符合
create table #T (ID int,price numeric(2,1),dscription varchar(7))
insert into #T
select 1000,1.0,'wwwssa' union all
select 1000,1.0,'wwwssa' union all
select 1001,1.2,'xhghfgh' union all
select 1001,2.0,'xhghfgh' union all
select 1002,1.3,'hjj' union all
select 1002,1.3,'hjj' union all
select 1003,1.5,'bhghh' union all
select 1003,1.6,'bhghh' union all
select 1003,1.5,'bhghh' union all
select 1004,1.2,'bhghh' union all
select 1004,1.1,'bhghh' union all
select 1004,1.1,'bhghh' /*
select distinct id,price from #t where id in (select id from #T group by id having count(*)>1 and max(price)>min(price))
/*
id price
1001 1.2
1001 2.0
1003 1.5
1003 1.6
1004 1.1
1004 1.2
*/
*/
本次是:相同ID出现两次和两次以上且price有不同但并不一定是全不同
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (ID int,price numeric(2,1),dscription varchar(7))
insert into #T
select 1000,1.0,'wwwssa' union all
select 1000,1.0,'wwwssa' union all
select 1001,1.2,'xhghfgh' union all
select 1001,2.0,'xhghfgh' union all
select 1002,1.3,'hjj' union all
select 1002,1.3,'hjj' union all
select 1003,1.5,'bhghh' union all
select 1003,1.6,'bhghh' union all
select 1003,1.5,'bhghh' union all
select 1004,1.2,'bhghh1' union all
select 1004,1.1,'bhghh2' union all
select 1004,1.1,'bhghh3' select distinct id,price,
(select top 1 dscription from #t where id=a.id and price=a.price) as dscription --这里面临当id 和 price相同时,取哪个description的问题
from #t as a where id in (select id from #T group by id having count(*)>1 and max(price)>min(price))
/*
--结果
1001 1.2 xhghfgh
1001 2.0 xhghfgh
1003 1.5 bhghh
1003 1.6 bhghh
1004 1.1 bhghh2
1004 1.2 bhghh1
*/