有一个用户点卡使用记录表,结构和数据大致如下。用户ID, 使用日期, 使用时间, 有效期, 消费点数, 剩余点数
10001 2009-02-01 10:21:32 2010-01-31 0 1000
10001 2009-02-03 18:30:15 2010-01-31 50 950
10001 2009-03-01 14:06:28 2010-01-31 60 890
10001 2009-03-01 16:12:08 2010-01-31 40 85010002 2009-04-01 10:21:32 2010-03-31 0 500
10002 2009-04-10 20:15:02 2010-03-31 80 420
10002 2009-04-20 17:26:18 2010-03-31 20 40010003 2009-02-01 10:21:32 2009-01-31 0 200现在想找出用户最后一次使用的那条记录,就是上面红色的数据,功能是实现了,可是效率不高,有没有更优化的查询方法??select memberno, use_date, use_time, valid_end_date, cost_point, rest_point
from point_hist A
where use_date = ( SELECT max(B.use_date)
FROM point_hist B
WHERE A.memberno = B.memberno )
AND use_time = ( SELECT max(C.point_hist)
FROM point_hist C
WHERE A.memberno = C.memberno
AND C.use_date = ( SELECT max(D.use_date)
FROM point_hist D
WHERE C.memberno = D.memberno)
)
10001 2009-02-01 10:21:32 2010-01-31 0 1000
10001 2009-02-03 18:30:15 2010-01-31 50 950
10001 2009-03-01 14:06:28 2010-01-31 60 890
10001 2009-03-01 16:12:08 2010-01-31 40 85010002 2009-04-01 10:21:32 2010-03-31 0 500
10002 2009-04-10 20:15:02 2010-03-31 80 420
10002 2009-04-20 17:26:18 2010-03-31 20 40010003 2009-02-01 10:21:32 2009-01-31 0 200现在想找出用户最后一次使用的那条记录,就是上面红色的数据,功能是实现了,可是效率不高,有没有更优化的查询方法??select memberno, use_date, use_time, valid_end_date, cost_point, rest_point
from point_hist A
where use_date = ( SELECT max(B.use_date)
FROM point_hist B
WHERE A.memberno = B.memberno )
AND use_time = ( SELECT max(C.point_hist)
FROM point_hist C
WHERE A.memberno = C.memberno
AND C.use_date = ( SELECT max(D.use_date)
FROM point_hist D
WHERE C.memberno = D.memberno)
)
select memberno, use_date, use_time, valid_end_date, cost_point, rest_point
from point_hist A
where use_date = ( SELECT max(B.use_date)
FROM point_hist B
WHERE A.memberno = B.memberno )
AND use_time = ( SELECT max(C.point_hist)
FROM point_hist C
WHERE A.memberno = C.memberno
AND C.use_date = ( SELECT max(D.use_date)
FROM point_hist D
WHERE C.memberno = D.memberno)
)拿回去研究一下
*
from
point_hist t
where
not exists(select 1 from point_hist where memberno=t.memberno and use_date>t.use_date)
INSERT @TB
SELECT '10001', '2009-02-01 10:21:32', '2010-01-31', 0, 1000 UNION ALL
SELECT '10001', '2009-02-03 18:30:15', '2010-01-31', 50, 950 UNION ALL
SELECT '10001', '2009-03-01 14:06:28', '2010-01-31', 60, 890 UNION ALL
SELECT '10001', '2009-03-01 16:12:08', '2010-01-31', 40, 850 UNION ALL
SELECT '10002', '2009-04-01 10:21:32', '2010-03-31', 0, 500 UNION ALL
SELECT '10002', '2009-04-10 20:15:02', '2010-03-31', 80, 420 UNION ALL
SELECT '10002', '2009-04-20 17:26:18', '2010-03-31', 20, 400 UNION ALL
SELECT '10003', '2009-02-01 10:21:32', '2009-01-31', 0, 200SELECT * FROM @TB T
WHERE NOT EXISTS(SELECT 1 FROM @TB WHERE [用户ID]=T.[用户ID] AND [使用日期]>T.[使用日期])
/*
用户ID 使用日期 使用时间 有效期 消费点数
----- ----------------------- ----------------------- ----------- -----------
10001 2009-03-01 16:12:08.000 2010-01-31 00:00:00.000 40 850
10002 2009-04-20 17:26:18.000 2010-03-31 00:00:00.000 20 400
10003 2009-02-01 10:21:32.000 2009-01-31 00:00:00.000 0 200
*/
from point_hist a
where not exists(select 1 from point_hist where memberno=a.memberno and use_date>a.use_date )
select * From tb a where not exists(select 1 From tb where 用戶id=a.用戶id and cast(使用日期+' '+使用時間 as smalldatetime)>cast(a.使用日期+' '+a.使用時間 as smalldatetime)
from point_hist t WHERE NOT EXISTS(SELECT 1 FROM TB WHERE 用户ID=T.用户ID AND 使用时间>T.使用时间)
from point_hist t where not exists(select 1 from point_hist where 用户ID=T.用户ID and 使用时间>t.使用时间)
SELECT * FROM 表 WHERE NOT EXISTS(SELECT * FROM TB T WHERE 用户ID=T.用户ID AND 使用时间<T.使用时间)