--如果ID是連續的Select Distinct A.* From TableName A Inner Join TableName B On A.ID = B.ID - 1 And A.Name = B.Name
--表ta(id,name)select * from ta as a where exists(select 1 from ta as b where name=a.name and id=(select min(id) from ta where id>a.id))
--建立測試環境 Create Table TEST (ID Int, Name Varchar(10)) --插入數據 Insert TEST Select 1, 'A' Union All Select 2, 'A' Union All Select 3, 'B' Union All Select 4, 'C' Union All Select 5, 'D' Union All Select 6, 'E' Union All Select 7, 'E' Union All Select 8, 'E' Union All Select 9, 'F' GO --測試 --如果ID是連續的 Select A.* From TEST A Inner Join TEST B On A.ID = B.ID - 1 And A.Name = B.Name Union Select A.* From TEST A Inner Join TEST B On A.ID = B.ID + 1 And A.Name = B.Name GO --刪除測試環境 Drop Table TEST --結果 /* ID Name 1 A 2 A 6 E 7 E 8 E */
select * from ta as a where exists (select 1 from ta where name=a.name and id=a.id+1)
--再一種方法 --建立測試環境 Create Table TEST (ID Int, Name Varchar(10)) --插入數據 Insert TEST Select 1, 'A' Union All Select 2, 'A' Union All Select 3, 'B' Union All Select 4, 'C' Union All Select 5, 'D' Union All Select 6, 'E' Union All Select 7, 'E' Union All Select 8, 'E' Union All Select 9, 'F' GO --測試 --如果ID是連續的 Select A.* From TEST A Where Exists(Select ID From TEST Where ID = A.ID + 1 And Name = A.Name) Or Exists(Select ID From TEST Where ID = A.ID - 1 And Name = A.Name) GO --刪除測試環境 Drop Table TEST --結果 /* ID Name 1 A 2 A 6 E 7 E 8 E */
select a.* from table1 a,table1 b where a.NAME=b.NAME AND ((a.id=b.id-1) or (b.id=a.id-1))
To roy_88: 语句好像不对。To paoluo: 同上
--第一種方法可以簡化下,但是要加Distinct去掉重復。 --建立測試環境 Create Table TEST (ID Int, Name Varchar(10)) --插入數據 Insert TEST Select 1, 'A' Union All Select 2, 'A' Union All Select 3, 'B' Union All Select 4, 'C' Union All Select 5, 'D' Union All Select 6, 'E' Union All Select 7, 'E' Union All Select 8, 'E' Union All Select 9, 'F' GO --測試 --如果ID是連續的 Select Distinct A.* From TEST A Inner Join TEST B On (A.ID = B.ID - 1 Or A.ID = B.ID + 1) And A.Name = B.Name GO --刪除測試環境 Drop Table TEST --結果 /* ID Name 1 A 2 A 6 E 7 E 8 E */
--建立測試環境 Create Table TEST (ID Int, Name Varchar(10)) --插入數據 Insert TEST Select 1, 'A' Union All Select 2, 'A' Union All Select 3, 'B' Union All Select 4, 'C' Union All Select 5, 'D' Union All Select 6, 'E' Union All Select 7, 'E' Union All Select 8, 'E' Union All Select 9, 'F' GO --測試 --方法一 Select Distinct A.* From TEST A Inner Join TEST B On (A.ID = B.ID - 1 Or A.ID = B.ID + 1) And A.Name = B.Name --方法二 Select A.* From TEST A Where Exists(Select ID From TEST Where ID = A.ID + 1 And Name = A.Name) Or Exists(Select ID From TEST Where ID = A.ID - 1 And Name = A.Name)--刪除測試環境 Drop Table TEST --結果 /* ID Name 1 A 2 A 6 E 7 E 8 E */
tompkins2000(AirHunter) ( ) 信誉:100 Blog 2007-03-29 09:57:57 得分: 0 To paoluo: 同上
---------我寫的兩種方法,哪條錯誤?
以上偶的分开发加OR就行了,借用鱼的例子 --表ta(id,name) Create Table ta (ID Int, Name Varchar(10)) --插入數據 Insert ta Select 1, 'A' Union All Select 2, 'A' Union All Select 3, 'B' Union All Select 4, 'C' Union All Select 5, 'D' Union All Select 6, 'E' Union All Select 7, 'E' Union All Select 8, 'E' Union All Select 9, 'F' select * from ta as awhere exists(select 1 from ta as b where name=a.name and id=(select min(id) from ta where id>a.id)) or exists(select 1 from ta as b where name=a.name and id=(select max(id) from ta where id<a.id)) ID Name ----------- ---------- 1 A 2 A 6 E 7 E 8 E(5 行受影响)
Distinct A.*
From
TableName A
Inner Join
TableName B
On A.ID = B.ID - 1 And A.Name = B.Name
where
exists(select 1 from ta as b where name=a.name
and id=(select min(id) from ta where id>a.id))
Create Table TEST
(ID Int,
Name Varchar(10))
--插入數據
Insert TEST Select 1, 'A'
Union All Select 2, 'A'
Union All Select 3, 'B'
Union All Select 4, 'C'
Union All Select 5, 'D'
Union All Select 6, 'E'
Union All Select 7, 'E'
Union All Select 8, 'E'
Union All Select 9, 'F'
GO
--測試
--如果ID是連續的
Select
A.*
From
TEST A
Inner Join
TEST B
On A.ID = B.ID - 1 And A.Name = B.Name
Union
Select
A.*
From
TEST A
Inner Join
TEST B
On A.ID = B.ID + 1 And A.Name = B.Name
GO
--刪除測試環境
Drop Table TEST
--結果
/*
ID Name
1 A
2 A
6 E
7 E
8 E
*/
where exists
(select 1 from ta where name=a.name
and id=a.id+1)
--建立測試環境
Create Table TEST
(ID Int,
Name Varchar(10))
--插入數據
Insert TEST Select 1, 'A'
Union All Select 2, 'A'
Union All Select 3, 'B'
Union All Select 4, 'C'
Union All Select 5, 'D'
Union All Select 6, 'E'
Union All Select 7, 'E'
Union All Select 8, 'E'
Union All Select 9, 'F'
GO
--測試
--如果ID是連續的
Select
A.*
From
TEST A
Where Exists(Select ID From TEST Where ID = A.ID + 1 And Name = A.Name)
Or Exists(Select ID From TEST Where ID = A.ID - 1 And Name = A.Name)
GO
--刪除測試環境
Drop Table TEST
--結果
/*
ID Name
1 A
2 A
6 E
7 E
8 E
*/
where a.NAME=b.NAME AND ((a.id=b.id-1) or (b.id=a.id-1))
语句好像不对。To paoluo:
同上
--建立測試環境
Create Table TEST
(ID Int,
Name Varchar(10))
--插入數據
Insert TEST Select 1, 'A'
Union All Select 2, 'A'
Union All Select 3, 'B'
Union All Select 4, 'C'
Union All Select 5, 'D'
Union All Select 6, 'E'
Union All Select 7, 'E'
Union All Select 8, 'E'
Union All Select 9, 'F'
GO
--測試
--如果ID是連續的
Select
Distinct A.*
From
TEST A
Inner Join
TEST B
On (A.ID = B.ID - 1 Or A.ID = B.ID + 1) And A.Name = B.Name
GO
--刪除測試環境
Drop Table TEST
--結果
/*
ID Name
1 A
2 A
6 E
7 E
8 E
*/
Create Table TEST
(ID Int,
Name Varchar(10))
--插入數據
Insert TEST Select 1, 'A'
Union All Select 2, 'A'
Union All Select 3, 'B'
Union All Select 4, 'C'
Union All Select 5, 'D'
Union All Select 6, 'E'
Union All Select 7, 'E'
Union All Select 8, 'E'
Union All Select 9, 'F'
GO
--測試
--方法一
Select
Distinct A.*
From
TEST A
Inner Join
TEST B
On (A.ID = B.ID - 1 Or A.ID = B.ID + 1) And A.Name = B.Name
--方法二
Select
A.*
From
TEST A
Where Exists(Select ID From TEST Where ID = A.ID + 1 And Name = A.Name)
Or Exists(Select ID From TEST Where ID = A.ID - 1 And Name = A.Name)--刪除測試環境
Drop Table TEST
--結果
/*
ID Name
1 A
2 A
6 E
7 E
8 E
*/
同上
---------我寫的兩種方法,哪條錯誤?
--表ta(id,name)
Create Table ta
(ID Int,
Name Varchar(10))
--插入數據
Insert ta Select 1, 'A'
Union All Select 2, 'A'
Union All Select 3, 'B'
Union All Select 4, 'C'
Union All Select 5, 'D'
Union All Select 6, 'E'
Union All Select 7, 'E'
Union All Select 8, 'E'
Union All Select 9, 'F'
select * from ta as awhere
exists(select 1 from ta as b where name=a.name
and id=(select min(id) from ta where id>a.id))
or
exists(select 1 from ta as b where name=a.name
and id=(select max(id) from ta where id<a.id))
ID Name
----------- ----------
1 A
2 A
6 E
7 E
8 E(5 行受影响)