FR FRZW ID DZ YB
黄经理 同志 1 -1
李经理 同志 2 -1
李小姐 同志 3 -1
张启山 同志 4 -1
陈经理 同志 5 -1
陈经理 同志 5 -1
黄经理 同志 1 -1我只想查按照ID重复的查询 其他重复的无所谓 回答完别走啊 我还会留言
也就是说 只要ID重复的 都会显示出来数据库setupfax 表名tbl_info_qyzl
黄经理 同志 1 -1
李经理 同志 2 -1
李小姐 同志 3 -1
张启山 同志 4 -1
陈经理 同志 5 -1
陈经理 同志 5 -1
黄经理 同志 1 -1我只想查按照ID重复的查询 其他重复的无所谓 回答完别走啊 我还会留言
也就是说 只要ID重复的 都会显示出来数据库setupfax 表名tbl_info_qyzl
insert into tbl_info_qyzl select '黄经理', '同志 ', 1 , -1
insert into tbl_info_qyzl select '李经理', '同志 ', 2 , -1
insert into tbl_info_qyzl select '李小姐', '同志 ', 3 , -1
insert into tbl_info_qyzl select '张启山', '同志 ', 4 , -1
insert into tbl_info_qyzl select '陈经理', '同志 ', 5 , -1
insert into tbl_info_qyzl select '陈经理', '同志 ', 5 , -1
insert into tbl_info_qyzl select '黄经理', '同志 ', 1 , -1 select * from tbl_info_qyzl where id in (
select id from tbl_info_qyzl
group by id
having count(*)>1
)drop table tbl_info_qyzl
/*
FR FRZW ID YB
黄经理 同志 1 -1
陈经理 同志 5 -1
陈经理 同志 5 -1
黄经理 同志 1 -1*/
create table tbl_info_qyzl
(
FR nvarchar(40),
FRZW nvarchar(40),
ID int,
DZ int
)
insert tbl_info_qyzl
select
'黄经理' , '同志' , 1, -1
union all select
'李经理' , '同志' , 2 , -1
union all select
'李小姐' , '同志' , 3 , -1
union all select
'张启山 ' , '同志' , 4 , -1
union all select
'陈经理' , '同志' , 5 , -1
union all select
'陈经理' , '同志' , 5 , -1
union all select
'黄经理' , '同志' , 1, -1
select FR, FRZW,id,DZ from tbl_info_qyzl group by FR, FRZW ,id,DZ having count (id)>1
--select FR, FRZW,id,DZ from tbl_info_qyzl a where exists (select count(id) from tbl_info_qyzl )
--select id, count(id) from tbl_info_qyzl group by idselect distinct FR, FRZW,id,DZ from tbl_info_qyzl a where id in
(
select id from tbl_info_qyzl group by id having count(id) >1
)
--删除环境
drop table tbl_info_qyzl
--创建环境
create table tbl_info_qyzl
(
FR nvarchar(40),
FRZW nvarchar(40),
ID int,
DZ int
)
insert tbl_info_qyzl
select
'黄经理' , '同志' , 1, -1
union all select
'李经理' , '同志' , 2 , -1
union all select
'李小姐' , '同志' , 3 , -1
union all select
'张启山 ' , '同志' , 4 , -1
union all select
'陈经理' , '同志' , 5 , -1
union all select
'陈经理' , '同志' , 5 , -1
union all select
'黄经理' , '同志' , 1, -1
--1
select FR, FRZW,id,DZ from tbl_info_qyzl group by FR, FRZW ,id,DZ having count (id)>1--2
select distinct FR, FRZW,id,DZ from tbl_info_qyzl a where id in
(
select id from tbl_info_qyzl group by id having count(id) >1
)
--3
select distinct FR, FRZW,id,DZ from tbl_info_qyzl a
where not exists (select 1 from tbl_info_qyzl b where a.id=b.id having count(id)=1 )
--删除环境
drop table tbl_info_qyzl
select * from tbl_info_qyzl where id in (
select id from tbl_info_qyzl
group by id
having count(*)>1
ID有重复的记录
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([FR] nvarchar(3),[FRZW] nvarchar(2),[ID] int,[DZ] int)
Insert #T
select '黄经理','同志',1,-1 union all
select '李经理','同志',2,-1 union all
select '李小姐','同志',3,-1 union all
select '张启山','同志',4,-1 union all
select '陈经理','同志',5,-1 union all
select '陈经理','同志',5,-1 union all
select '黄经理','同志',1,-1
Go
Select * from #T
--我只想查按照ID重复的查询 其他重复的无所谓 回答完别走啊 我还会留言
--也就是说 只要ID重复的 都会显示出来
select * from #T a where (select count(*) from #T where id=a.id )>1
order by id