现有表A
ID TEMP TAXIS
-----------------
1 00SW66 6
2 02SW55 5
3 01SW66 4
4 00SW55 7
5 05SW66 3
6 06SW44 9
7 00SW66 2
8 03SW44 1
9 07SW66 8我想得到如下结果:
ID TEMP TAXIS
-----------------
8 SW44 1
5 SW66 2
2 SW55 5请问应该怎么写??
ID TEMP TAXIS
-----------------
1 00SW66 6
2 02SW55 5
3 01SW66 4
4 00SW55 7
5 05SW66 3
6 06SW44 9
7 00SW66 2
8 03SW44 1
9 07SW66 8我想得到如下结果:
ID TEMP TAXIS
-----------------
8 SW44 1
5 SW66 2
2 SW55 5请问应该怎么写??
NOT EXISTS (SELECT 1 FROM TableName AS B ON RIGHT(A.[TEMP],4)=RIGHT(B.[TEMP],4)
AND B.TAXIS>A.TAXIS)大致的意思就这样,要是语法错误,请大家纠正。
SELECT [ID],RIGHT(A.[TEMP],4),TAXIS FROM tableName AS A WHERE
NOT EXISTS (SELECT 1 FROM TableName AS B ON RIGHT(A.[TEMP],4)=RIGHT(B.[TEMP],4)
AND B.TAXIS>A.TAXIS)
A.*
From
A
Inner Join
(Select Right(TEMP, 4) As TEMP, Min(TAXIS) As TAXIS From A Group By Right(TEMP, 4)) B
On Right(A.TEMP, 4) = B.TEMP And A.TAXIS = B.TAXIS
Order By TAXIS
Select
A.*
From
A
Inner Join
(Select Right(TEMP, 4) As TEMP, Min(TAXIS) As TAXIS From A Group By Right(TEMP, 4)) B
On Right(A.TEMP, 4) = B.TEMP And A.TAXIS = B.TAXIS
Order By A.TAXIS--方法二
Select
T.*
From
A T
Where
TAXIS = (Select Min(TAXIS) From A Where Right(TEMP, 4) = Right(T.TEMP, 4))
Order By TAXIS--方法三
Select
T.*
From
A T
Where
Not Exists (Select TAXIS From A Where Right(TEMP, 4) = Right(T.TEMP, 4) And TAXIS < T.TAXIS)
Order By TAXIS
Select
A.*
From
A
Inner Join
(Select Right(TEMP, 4) As TEMP, Min(TAXIS) As TAXIS From A Group By Right(TEMP, 4)) B
On Right(A.TEMP, 4) = B.TEMP And A.TAXIS = B.TAXIS
Order By A.TAXIS