如何查询,同一张表内,一字段不相等的内容,相等的内容就不查询出来
例:de表
d e
22010102 2.5
22010102 3
22010103 2.5
22010103 3
22010401 1
22010401 1
12010101 5
12010101 6
想要的结果是
de表
d e
22010102 2.5
22010102 3
22010103 2.5
22010103 3
例:de表
d e
22010102 2.5
22010102 3
22010103 2.5
22010103 3
22010401 1
22010401 1
12010101 5
12010101 6
想要的结果是
de表
d e
22010102 2.5
22010102 3
22010103 2.5
22010103 3
DE WHERE D NOT IN
(SELECT D FROM DE GROUP BY D,E HAVING COUNT(1)>1)
use pubs
--建表
if object_id('test01') is not null drop table test01create table test01 (d varchar(10),e float)--插入测试数据
insert into test01
select '22010102','2.5' union all
select '22010102','3' union all
select '22010103','2.5' union all
select '22010103','3' union all
select '22010401','1' union all
select '22010401','1' union all
select '12010101','5' union all
select '12010101','6' --查看
select * from test01--1楼语句测试
SELECT * FROM
test01 WHERE D NOT IN
(SELECT D FROM test01 GROUP BY D,E HAVING COUNT(1)>1)--1楼语句的查询结果
d e
22010102 2.5
22010102 3.0
22010103 2.5
22010103 3.0
12010101 5.0
12010101 6.0
--我这个也可
select d,e from test01 group by e,d having count(1)=1--我的查询结果,顺序嘛,嘿嘿
d e
12010101 5.0
12010101 6.0
22010102 2.5
22010102 3.0
22010103 2.5
22010103 3.0
drop table tb
go
create table tb (d varchar(10),e float)
insert into tb
select '22010102','2.5' union all
select '22010102','3' union all
select '22010103','2.5' union all
select '22010103','3' union all
select '22010401','1' union all
select '22010401','1' union all
select '12010101','5' union all
select '12010101','6' select * from ( select d,e,count(*) as gg from tb group by d,e) t where gg=1