--"找到sxrq靠前三的商品,是每个spid的前三名",不知道sxrq的前三是按從小到大的順序還是從大到小的順序
--從小到大的順序
--方法一:
Select * From sphwph A Where (Select Count(*) From sphwph Where spid=A.spid And sxrq<A.sxrq)<3
--方法二:
Select * From sphwph A Where Not Exists(Select * from sphwph Where spid=A.spid And sxrq<A.sxrq Having Count(*)>2)
--方法三:
Select * From sphwph A Where sxrq In(Select Top 3 sxrq From sphwph Where spid=A.spid Order By sxrq)
--從大到小的順序
--方法一:
Select * From sphwph A Where (Select Count(*) From sphwph Where spid=A.spid And sxrq>A.sxrq)<3
--方法二:
Select * From sphwph A Where Not Exists(Select * from sphwph Where spid=A.spid And sxrq>A.sxrq Having Count(*)>2)
--方法三:
Select * From sphwph A Where sxrq In(Select Top 3 sxrq From sphwph Where spid=A.spid Order By sxrq Desc)Top
例:
Select * From honlivhp.drug_dict A
Where
( Select Count(*) From honlivhp.drug_dict
Where drug_name=A.drug_name and drug_spec=A.drug_spec and units=a.units And charges<A.charges
)<3
and charges is not null
and drug_name='泛昔洛韦片'
order by drug_name,drug_from,drug_spec,units,charges,charges