查询时过滤部分不符合要求的数据
我现在可以查询出某时间段内某张的卡所有流水,我的sql如下:
您的我还没测。
我的电脑快卡死了。。。
你的表[QTKCenter].[dbo].[SpendHistory]的数据量有多大呢。
另外,加上了过滤条件: a.[CardNo]='6212262409000028515' and a.[ExTime] between '2013-3-1' and '2013-3-31'
后大概还剩下多少呢
[解决办法]
create index idx_SpendHistory_cc on SpendHistory(CardNo,ExTime)
select [CardNo],[CardID],[CardSave],[TollAmount],[ExTime],[ExRoad],[ExStation]
,[ExShiftDate],[EnTime],[EnRoad],[EnStation],[ExVehPlate],[VerifyCode]
,[DealStatus],[SendTime]
from
(
select *,
row_number() over(partition by Extime,ExStantion
order by sendTime desc) rownum
from
(
SELECT [CardNo]
,[CardID]
,[CardSave]
,[TollAmount]
,[ExTime]
,[ExRoad]
,[ExStation]
,[ExShiftDate]
,[EnTime]
,[EnRoad]
,[EnStation]
,[ExVehPlate]
,[VerifyCode]
,[DealStatus]
,[SendTime]
FROM [QTKCenter].[dbo].[SpendHistory]
where [CardNo] ='6212262409000028515' and
[ExTime] between '2013-3-1' and '2013-3-31'
) t
)t
where rownum=1