select * from A,
(select 卡号, max(收银时间) as MaxTime group by 卡号) B
where A.卡号=B.卡号 and A.收银时间=B.MaxTime
解决方案 »
- 可以通过sqlserver直接修改mdb文件吗 或者创建空白mdb文件??
- [求助]学习数据库编程应从哪里入手
- 請問 SQLSERVER 2000 TABLE 個數有沒有限制?
- 如何设置sql server 身份认证
- 救命啊!!!求mysql截取字符串语句
- MSDE在WIN7家庭版中的问题
- 请教,在线等!谢谢
- 制造企业的生产调度设计?
- 在下初学,请各位不要见笑!请问什么是unicode数据类型?(无内容)
- 新手请教!开发sql server2000客户/服务器应用程序!
- 关于SetRecordSource()的应用 会的人请告诉怎么用 应该怎么定义它和用它
- ERP系统运行速度突然变慢,SQL服务器磁盘IO变得很大
INSERT @TB
SELECT '002000062638', 427, '2006-10-26 08:30:00.000' UNION ALL
SELECT '002000062638', 300, '2007-11-20 09:20:00.000' UNION ALL
SELECT '010000077585', 230, '2007-02-25 10:53:00.000' UNION ALL
SELECT '010000077585', 530, '2006-02-25 10:53:00.000' UNION ALL
SELECT '008000003509', 370, '2004-03-28 16:13:00.000'SELECT * FROM @TB AS A WHERE NOT EXISTS(SELECT 1 FROM @TB WHERE 卡号=A.卡号 AND 收银时间>A. 收银时间)
/*
卡号 余额 收银时间
--------------- ----------- ------------------------------------------------------
002000062638 300 2007-11-20 09:20:00
010000077585 230 2007-02-25 10:53:00
008000003509 370 2004-03-28 16:13:00
*/
from
(select max(convert(datetime,收银时间)) As 收银时间 ,卡号
from table
) As AA,
(select 卡号,余额,convert(datetime,收银时间) As 收银时间
from table
) As BBwhere AA.卡号=BB.卡号
and AA.收银时间=BB.收银时间
---Test select BB.Tdate,BB.ID,BB.YUE
from
(select Tdate,ID,YUE
from
(SELECT '002000062638' aS ID , 427 AS YUE, CONVERT(DATETIME,'2006-10-26 08:30:00.000') AS Tdate
union
SELECT '002000062638' aS ID , 300 AS YUE, CONVERT(DATETIME,'2007-11-20 09:20:00.000') AS Tdate
union
SELECT '010000077585' aS ID , 230 AS YUE, CONVERT(DATETIME,'2007-02-25 10:53:00.000') AS Tdate
union
SELECT '010000077585' aS ID , 530 AS YUE, CONVERT(DATETIME,'2006-02-25 10:53:00.000') AS Tdate
union
SELECT '008000003509' aS ID , 370 AS YUE, CONVERT(DATETIME,'2004-03-28 16:13:00.000') AS Tdate
) AS MM
) AS BB,
(select max(Tdate) As TDate ,ID
from
(
SELECT '002000062638' aS ID , 427 AS YUE, CONVERT(DATETIME,'2006-10-26 08:30:00.000') AS Tdate
union
SELECT '002000062638' aS ID , 300 AS YUE, CONVERT(DATETIME,'2007-11-20 09:20:00.000') AS Tdate
union
SELECT '010000077585' aS ID , 230 AS YUE, CONVERT(DATETIME,'2007-02-25 10:53:00.000') AS Tdate
union
SELECT '010000077585' aS ID , 530 AS YUE, CONVERT(DATETIME,'2006-02-25 10:53:00.000') AS Tdate
union
SELECT '008000003509' aS ID , 370 AS YUE, CONVERT(DATETIME,'2004-03-28 16:13:00.000') AS Tdate
) AS AA
group by ID
) As NN
where BB.Tdate=nn.Tdate
and BB.ID=nn.ID