select jh ,yxbh ,count(*)from tablename group by jh yxbh having count(*)>1
解决方案 »
- 关于case when 的问题
- SQL各连接区别
- sql server 可不可以联机?
- 关于smallDateTime类型字段排序的问题
- 邹健您在吗?存储过程在查询分析器中可无限次执行,但在C#中SqlComand中只能正确执行一次,第二次就报错?我要SqlComand中多次执行
- 当网络断开后,先前开始的事务没有提交,服务器的相关表无法被访问
- mysql数据导入sql server 数据库 是否可行?
- 100求解:怎么把查询到的一个结果用竖的形式显示出来?
- SQL语句 比较难的,急用!
- 急:建触发器后表数据丢失问题
- 请问一更新触发器问题!
- 现在在数据列表中插入UTF-8编码数据,在数据库中的 varchar类型是否要修改?
(select jh,yxbh from YTK_YXBH group by jh,yxbh having count(*) >= 2) b
where a.jh = b.jh and a.yxbh = b.yxbh
INSERT INTO @tb
SELECT 'A', 'B', 1, 2, 3
UNION ALL SELECT 'A', 'C', 4, 5, 6
UNION ALL SELECT 'A', 'B', 7, 8, 9
UNION ALL SELECT 'E', 'B', 10, 0, 5
UNION ALL SELECT 'E', 'E', 3, 2, 1SELECT * FROM @tb A
WHERE (SELECT COUNT(1) FROM @tb WHERE JH = A.JH AND YXBH = A.YXBH) > 1
from YTK_YXBH a
join (select JH, YXBH
from YTK_YXBH
group by JH, YXBH
having count(1) > 1) b on a.JH = b.JH and a.YXBH = b.YXBH
declare @ta table(JH varchar(5), YXBH varchar(5), ZD1 int, ZD2 int, ZD3 int)
insert @ta
select 'A' , 'B' , 1 , 2 , 3 union all
select 'A' , 'C' , 4 , 5 , 6 union all
select 'A' , 'B' , 7 , 8 , 9 union all
select 'E' , 'B' , 10 , 0 , 5 union all
select 'E' , 'E' , 3 , 2 , 1select * from @ta a where
exists (select 1 from @ta where JH=a.JH and YXBH=a.YXBH group by JH,YXBH having count(*)>1 )
(所影响的行数为 5 行)JH YXBH ZD1 ZD2 ZD3
----- ----- ----------- ----------- -----------
A B 1 2 3
A B 7 8 9(所影响的行数为 2 行)
drop table YTK_YXBH
gocreate table YTK_YXBH
(
JH char(1),
YXBH char(1),
ZD1 int,
ZD2 int,
ZD3 int
)
insert into YTK_YXBH(JH,YXBH,ZD1,ZD2,ZD3) values('A','B', 1, 2, 3)
insert into YTK_YXBH(JH,YXBH,ZD1,ZD2,ZD3) values('A','C', 4, 5, 6)
insert into YTK_YXBH(JH,YXBH,ZD1,ZD2,ZD3) values('A','B', 7, 8, 9)
insert into YTK_YXBH(JH,YXBH,ZD1,ZD2,ZD3) values('E','B', 10, 0, 5)
insert into YTK_YXBH(JH,YXBH,ZD1,ZD2,ZD3) values('E','E', 3 , 2, 1)
goselect a.* from YTK_YXBH a,
(select jh,yxbh from YTK_YXBH group by jh,yxbh having count(*) >= 2) b
where a.jh = b.jh and a.yxbh = b.yxbhdrop table YTK_YXBH
JH YXBH ZD1 ZD2 ZD3
---- ---- ----------- ----------- -----------
A B 1 2 3
A B 7 8 9(所影响的行数为 2 行)
from YTK_YXBH a,YTK_YXBH b
where a.JH= b.JH and a.YXBH = b.a.YXBH
其他重复条件-----
insert into @tt select 'A','B',1,2,3
insert into @tt select 'A','C',4,5,6
insert into @tt select 'A','B',7,8,9
insert into @tt select 'E','B',10,0,5
insert into @tt select 'E','E',3,2,1
select * from @tt where JH+YXBH in(select JH+YXBH from (select count(JH+YXBH) as num,JH,YXBH from @tt
group by JH,YXBH) as tt2 where tt2.num >1)
查询结果A B 1 2 3
A B 7 8 9
roy_88(论坛新星_燃烧你的激情!!) 的不对,并不符合要求.楼主是指第一个语句是查出来记录JH,YXBH重复大于1的JH,YXBH。
下面测试的为查出重复记录
insert into @tt select 'A','B',1,2,3
insert into @tt select 'A','C',4,5,6
insert into @tt select 'A','B',7,8,9
insert into @tt select 'E','B',10,0,5
insert into @tt select 'E','E',3,2,1select a.* from @tt a where exists (select 1 from @tt b where a.jh=b.jh and a.yxbh=b.yxbh group by b.jh,b.yxbh having count(*) >1)查询结果A B 1 2 3
A B 7 8 9