题目：
表名 TEST
数据：
TID VID VName VNumber
1 1 AA 10
2 1 AA 8
3 1 AA 2
4 2 BB 90
5 2 BB 70
6 2 BB 20
要求按照VID 和 VName分组，取出数据：
TID VID Vname Vnumber
2 1 AA 8
5 2 BB 70
也就是取出每组除了开始一条和最后一条的其他所有信息。
------解决方案--------------------
- SQL code
SELECT *FROM TB T1WHERE EXISTS(SELECT 1 FROM TB T2 WHERE T2.VID=T1.VID AND T2.VNAME=T1.VNAME AND T2.TID<T1.TID) AND EXISTS(SELECT 1 FROM TB T2 WHERE T2.VID=T1.VID AND T2.VNAME=T1.VNAME AND T2.TID>T1.TID)
------解决方案--------------------
- SQL code
SELECT *FROM TB tWHERE TID != ( SELECT MIN(Tid) FROM TB WHERE Vid = t.VID AND [VName] = t.[VName] ) AND Tid != ( SELECT MAX(Tid) FROM TB WHERE Vid = t.VID AND [VName] = t.[VName] )
------解决方案--------------------
- SQL code
SELECT *FROM TB tWHERE EXISTS ( SELECT 1 FROM TB WHERE T.TID>MIN(TID) AND T.TID<MAX(T.TID) AND Vid = t.VID AND [VName] = t.[VName] GROUP BY VNAME,VID)
------解决方案--------------------
- SQL code
select * from(select px=row_number()over(partition by VID ,VName order by TID,* from tb)t where px<>1union allselect * from(select px=row_number()over(partition by VID ,VName order by TID desc),* from tb)t where px<>1