问一个sql!
table1:
id name addrss
1 huang wuhan
1 huang wuhan
2 li beijing
2 zhang nanjing
2 hu guangzhou
3 wang shenzhen
=========
分别查出ID相同其他字段不同 （几个人ID重复了）
2 li beijing
2 zhang nanjing
2 hu guangzhou
id相同 其他字段也相同（一条记录重复录入了）
1 huang wuhan
谢谢
[解决办法]
--是這個嗎?
分别查出ID相同其他字段不同 （几个人ID重复了）
select * from t
where id in(select id from t group by id having count(*)> 1)
group by id,name,addrss
having count(*)=1
id相同 其他字段也相同（一条记录重复录入了）
select * from t
group by id,name,addrss
having count(*)> 1
[解决办法]
CREATE TABLE [dbo].[table1](
[id] [int]NULL,
[name] [varchar](50) NULL,
[addrss] [varchar](50) NULL
)
INSERT INTO [dbo].[table1]
SELECT 1, 'huang ', 'wuhan ' UNION ALL
SELECT 1, 'huang ', 'wuhan ' UNION ALL
SELECT 2, 'li ', 'beijing ' UNION ALL
SELECT 2, 'zhang ', 'nanjing ' UNION ALL
SELECT 2, 'hu ', 'guangzhou ' UNION ALL
SELECT 3, 'wang ', 'shenzhen '
--分别查出ID相同其他字段不同
SELECT DISTINCT A.* FROM [dbo].[table1] A INNER JOIN [dbo].[table1] B
ON
A.ID=B.ID AND A.[NAME]+A.[addrss] <> B.[NAME]+B.[addrss]
--结果
2huguangzhou
2libeijing
2zhangnanjing
--id相同 其他字段也相同
SELECT * FROM [dbo].[table1] GROUP BY [id],[name],[addrss] HAVING COUNT(1)> 1
--结果
1huangwuhan
DROP TABLE [dbo].[table1]
[解决办法]
playwarcraft(时间就像乳沟,挤挤还是有的) 正解
create table table11(id int,name varchar(20),addrss varchar(50))
insert into table11 select 1, 'huang ', 'wuhan '
union all select 1, 'huang ', 'wuhan '
union all select 2, 'li ', 'beijing '
union all select 2, 'zhang ', 'nanjing '
union all select 2, 'hu ', 'guangzhou '
union all select 3, 'wang ', 'shenzhen '
--分别查出ID相同其他字段不同 （几个人ID重复了）
select * from table11
where id in(select id from table11 group by id having count(*)> 1)
group by id,name,addrss
having count(*)=1
go
--id相同 其他字段也相同（一条记录重复录入了）
select id,name,addrss from table11 group by id,name,addrss having count(*)> 1
[解决办法]
--drop table t,#t
create table t(id int, name varchar(200),address varchar(200))
insert t select 1 , 'huang ' , 'wuhan '
union all select 1 , 'huang ' , 'wuhan '
union all select 2 , 'li ' , 'beijing '
union all select 2 , 'zhang ' , 'nanjing '
union all select 2 , 'hu ' , 'guangzhou '
union all select 3 , 'wang ' , 'shenzhen '
select id1=identity(int,1,1), * into #t from t
--分别查出ID相同其他字段不同 （几个人ID重复了）
select id,name,address from #t a where exists( select 1 from #t b
where a.name=b.name and a.address=b.address and a.id=b.id and b.id1> a.id1)
---id相同 其他字段也相同（一条记录重复录入了）
select id,name,address from #t a where exists( select 1 from #t b
where a.name!=b.name and a.address!=b.address and a.id=b.id
)
drop table t,#t
[解决办法]
create table T(id int, name varchar(10), addrss varchar(10))
insert T select 1, 'huang ', 'wuhan '
union all select 1, 'huang ', 'wuhan '
union all select 2, 'li ', 'beijing '
union all select 2, 'zhang ', 'nanjing '
union all select 2, 'hu ', 'guangzhou '
union all select 3, 'wang ', 'shenzhen '
--1
select * from T tmp
where (select count(*) from T where id=tmp.id and name <> tmp.name and addrss <> tmp.addrss)> 1
--result
id name addrss
----------- ---------- ----------
2 li beijing
2 zhang nanjing
2 hu guangzhou
(3 row(s) affected)
--2
select id, name, addrss from T
group by id, name, addrss
having count(*)> 1
--result
id name addrss
----------- ---------- ----------
1 huang wuhan
(1 row(s) affected)
