表结构如下序号 类型 编号
---------------------------------
1 A 10
2 A 10
3 A 11
4 B 10
4 B 11现在我的查询是同类型的编号不能相同
1:查询出表中类型相同,编号相同的记录(不合格的记录)2:查询出表中类型相同,但编号不相同的记录(合格的记录)
请达人们指点下,在线等,
---------------------------------
1 A 10
2 A 10
3 A 11
4 B 10
4 B 11现在我的查询是同类型的编号不能相同
1:查询出表中类型相同,编号相同的记录(不合格的记录)2:查询出表中类型相同,但编号不相同的记录(合格的记录)
请达人们指点下,在线等,
where exists(select * from T where 类型=tmp.类型 and 编号=tmp.编号)select * from T as tmp
where exists(select * from T where 类型=tmp.类型 and 编号<>tmp.编号)
select * from tab a
where exists (
select 1 from tab
where 类型=a.类型 and 编号=a.编号
and 序号<>a.序号
)2
select * from tab a
where not exists (
select 1 from tab
where 类型=a.类型 and 编号=a.编号
and 序号<>a.序号
)
INSERT @TB
SELECT 1, 'A', 10 UNION ALL
SELECT 2, 'A', 10 UNION ALL
SELECT 3, 'A', 11 UNION ALL
SELECT 4, 'B', 10 UNION ALL
SELECT 4, 'B', 11SELECT * FROM @TB AS A WHERE EXISTS(SELECT 1 FROM @TB WHERE COL=A.COL AND COL2=A.COL2 AND ID<>A.ID)
/*
ID COL COL2
----------- ---- -----------
1 A 10
2 A 10
*/
SELECT * FROM @TB AS A WHERE NOT EXISTS(SELECT 1 FROM @TB WHERE COL=A.COL AND COL2=A.COL2 AND ID<>A.ID)
/*
ID COL COL2
----------- ---- -----------
3 A 11
4 B 10
4 B 11
*/
--Sorry 上面的理解错了create table T
(
序号 int,
类型 nvarchar(10),
编号 int
)
insert T
select 1, 'A', 10
union all select 2, 'A', 10
union all select 3, 'A', 11
union all select 4, 'B', 10
union all select 4, 'B', 11 select * from T as tmp
where (select count(*) from T where 类型=tmp.类型 and 编号=tmp.编号)>1
2:查询出表中类型相同,但编号不相同的记录(合格的记录) ??2 A 10
3 A 11
1 A 10
3 A 11 这样的组合符合要求吗
(
id int,
ttype nvarchar(20),
Num int
)
insert into #EE select 1,'A',10
union all select 2,'A',10
union all select 3,'A',11
union all select 4,'B',10
union all select 4,'B',11--1:查询出表中类型相同,编号相同的记录(不合格的记录)
select * from #EE EE where Exists (select * from #EE where ttype=EE.ttype and Num=EE.Num and id<>EE.id)
select * from #EE EE where Exists (select * from #EE where ttype=EE.ttype and Num<>EE.Num)