select * from table a where a.index not in(select top 1 index from table where name in (select name from table group by name having count(1)>1) and a.NAME=NAME)
create table t ( index1 int, name char(1), code char(1), im char(1) )insert into t values(1,'A','B','S') insert into t values(2,'A','4','1') insert into t values(3,'B','5','S') insert into t values(4,'S','F','E') insert into t values(5,'S','S','D') insert into t values(6,'S','0','A') select * from t a where a.index1 not in(select top 1 index1 from t where name in (select name from t group by name having count(1)>1) and a.name=name) -------------------------- 2 A 4 1 3 B 5 S 5 S S D 6 S 0 A
這個意思?? Create Table TEST ([INDEX] Int, NAME Varchar(10), CODE Varchar(10), IM Varchar(10)) Insert TEST Select 1, 'A', 'B', 'S' Union All Select 2, 'A', '4', '1' Union All Select 3, 'B', '8', 'S' Union All Select 4, 'S', 'F', 'E' Union All Select 5, 'S', 'S', 'D' Union All Select 6, 'S', '0', 'A' GO --方法一: Select * From TEST A Where Exists(Select * From TEST Where NAME = A.NAME And [INDEX] != A.[INDEX])--方法二: Select Distinct A.* From TEST A Inner Join TEST B On A.NAME = B.NAME And A.[INDEX] != B.[INDEX]GO Drop Table TEST --Result /* INDEX NAME CODE IM 1 A B S 2 A 4 1 4 S F E 5 S S D 6 S 0 A */
得到结果是NAME相同字段第一个INDEX值,谢谢两位。
改正一下!select * from t a where a.index1 not in(select top 1 index1 from t where name in (select name from t group by name having count(1)>1) and a.name=name) and name not in(select name from t group by name having count(1)<=1) ---------------------- 2 A 4 1 5 S S D 6 S 0 A
ocean2005(海洋之星) ( ) 信誉:100 Blog 2007-03-16 11:47:02 得分: 0
得到结果是NAME相同字段第一个INDEX值,谢谢两位。
-------------- 這個意思?Create Table TEST ([INDEX] Int, NAME Varchar(10), CODE Varchar(10), IM Varchar(10)) Insert TEST Select 1, 'A', 'B', 'S' Union All Select 2, 'A', '4', '1' Union All Select 3, 'B', '8', 'S' Union All Select 4, 'S', 'F', 'E' Union All Select 5, 'S', 'S', 'D' Union All Select 6, 'S', '0', 'A' GO Select Min([INDEX]) As [INDEX] From TEST Group By NAME Having Count(*) > 1 GO Drop Table TEST --Result /* INDEX 1 4 */
得到结果是NAME相同字段第一个INDEX值select * from t a where index1 not in(select index1 from t a where a.index1 not in(select top 1 index1 from t where name in (select name from t group by name having count(1)>1) and a.name=name)) ----------- 1 A B S 4 S F E
where a.index not in(select top 1 index from table where name in (select name from table group by name having count(1)>1) and a.NAME=NAME)
(
index1 int,
name char(1),
code char(1),
im char(1)
)insert into t values(1,'A','B','S')
insert into t values(2,'A','4','1')
insert into t values(3,'B','5','S')
insert into t values(4,'S','F','E')
insert into t values(5,'S','S','D')
insert into t values(6,'S','0','A')
select * from t a
where a.index1 not in(select top 1 index1 from t where name in (select name from t group by name having count(1)>1) and a.name=name)
--------------------------
2 A 4 1
3 B 5 S
5 S S D
6 S 0 A
Create Table TEST
([INDEX] Int,
NAME Varchar(10),
CODE Varchar(10),
IM Varchar(10))
Insert TEST Select 1, 'A', 'B', 'S'
Union All Select 2, 'A', '4', '1'
Union All Select 3, 'B', '8', 'S'
Union All Select 4, 'S', 'F', 'E'
Union All Select 5, 'S', 'S', 'D'
Union All Select 6, 'S', '0', 'A'
GO
--方法一:
Select * From TEST A
Where Exists(Select * From TEST Where NAME = A.NAME And [INDEX] != A.[INDEX])--方法二:
Select
Distinct A.*
From
TEST A
Inner Join
TEST B
On A.NAME = B.NAME And A.[INDEX] != B.[INDEX]GO
Drop Table TEST
--Result
/*
INDEX NAME CODE IM
1 A B S
2 A 4 1
4 S F E
5 S S D
6 S 0 A
*/
where a.index1 not in(select top 1 index1 from t where name in (select name from t group by name having count(1)>1) and a.name=name)
and name not in(select name from t group by name having count(1)<=1)
----------------------
2 A 4 1
5 S S D
6 S 0 A
得到结果是NAME相同字段第一个INDEX值,谢谢两位。
--------------
這個意思?Create Table TEST
([INDEX] Int,
NAME Varchar(10),
CODE Varchar(10),
IM Varchar(10))
Insert TEST Select 1, 'A', 'B', 'S'
Union All Select 2, 'A', '4', '1'
Union All Select 3, 'B', '8', 'S'
Union All Select 4, 'S', 'F', 'E'
Union All Select 5, 'S', 'S', 'D'
Union All Select 6, 'S', '0', 'A'
GO
Select
Min([INDEX]) As [INDEX]
From
TEST
Group By NAME
Having Count(*) > 1
GO
Drop Table TEST
--Result
/*
INDEX
1
4
*/
where a.index1 not in(select top 1 index1 from t where name in (select name from t group by name having count(1)>1) and a.name=name))
-----------
1 A B S
4 S F E