1/有一个表
MCR# FTY# DESCRIPTION ACCESSED
10000 DFER3456 瓷器 20006-10-10 20:20:12
10001 DFER3456 瓷器 20006-10-10 20:21:12
10002 GFER3456 家私 20006-10-10 20:22:12
10003 GFER3456 家私 20006-10-10 20:23:12
10004 GFER3456 家私 20006-10-10 20:24:12
10005 TFER3456 餐具 20006-10-10 20:25:12
10006 TFER3456 餐具 20006-10-10 20:26:12
10007 TFER3456 餐具 20006-10-10 20:27:12
10008 BFER3456 浴具 20006-10-10 20:28:12
10009 BFER3456 浴具 20006-10-10 20:29:12
10010 BFER3456 浴具 20006-10-10 20:30:12
10011 BFER3456 浴具 20006-10-10 20:31:122/要求
相同FTY#的只出来ACCESSED时间最靠后的一条记录。结果如下:
MCR# FTY# DESCRIPTION ACCESSED
10001 DFER3456 瓷器 20006-10-10 20:21:12
10004 GFER3456 家私 20006-10-10 20:24:12
10007 TFER3456 餐具 20006-10-10 20:27:12
10011 BFER3456 浴具 20006-10-10 20:31:12
MCR# FTY# DESCRIPTION ACCESSED
10000 DFER3456 瓷器 20006-10-10 20:20:12
10001 DFER3456 瓷器 20006-10-10 20:21:12
10002 GFER3456 家私 20006-10-10 20:22:12
10003 GFER3456 家私 20006-10-10 20:23:12
10004 GFER3456 家私 20006-10-10 20:24:12
10005 TFER3456 餐具 20006-10-10 20:25:12
10006 TFER3456 餐具 20006-10-10 20:26:12
10007 TFER3456 餐具 20006-10-10 20:27:12
10008 BFER3456 浴具 20006-10-10 20:28:12
10009 BFER3456 浴具 20006-10-10 20:29:12
10010 BFER3456 浴具 20006-10-10 20:30:12
10011 BFER3456 浴具 20006-10-10 20:31:122/要求
相同FTY#的只出来ACCESSED时间最靠后的一条记录。结果如下:
MCR# FTY# DESCRIPTION ACCESSED
10001 DFER3456 瓷器 20006-10-10 20:21:12
10004 GFER3456 家私 20006-10-10 20:24:12
10007 TFER3456 餐具 20006-10-10 20:27:12
10011 BFER3456 浴具 20006-10-10 20:31:12
(
NAME VARCHAR(10) NULL,
iDATETIMETIME DATETIME,
)
INSERT INTO TABLE1(NAME,iDATETIME) SELECT 'A1',GETDATE()
WAITFOR DELAY '000:00:10'
INSERT INTO TABLE1(NAME,iDATETIME) SELECT 'A1',GETDATE()
WAITFOR DELAY '000:00:10'
INSERT INTO TABLE1(NAME,iDATETIME) SELECT 'A1',GETDATE()
WAITFOR DELAY '000:00:10'
INSERT INTO TABLE1(NAME,iDATETIME) SELECT 'A1',GETDATE()
WAITFOR DELAY '000:00:10'
INSERT INTO TABLE1(NAME,iDATETIME) SELECT 'A1',GETDATE()
WAITFOR DELAY '000:00:10'
INSERT INTO TABLE1(NAME,iDATETIME) SELECT 'B1',GETDATE()
WAITFOR DELAY '000:00:10'
INSERT INTO TABLE1(NAME,iDATETIME) SELECT 'B1',GETDATE()
WAITFOR DELAY '000:00:10'
INSERT INTO TABLE1(NAME,iDATETIME) SELECT 'B1',GETDATE()
WAITFOR DELAY '000:00:10'
INSERT INTO TABLE1(NAME,iDATETIME) SELECT 'B1',GETDATE()
WAITFOR DELAY '000:00:10'
INSERT INTO TABLE1(NAME,iDATETIME) SELECT 'B1',GETDATE()
WAITFOR DELAY '000:00:10'
INSERT INTO TABLE1(NAME,iDATETIME) SELECT 'B1',GETDATE()
SELECT T.* FROM TABLE1 T,(SELECT NAME,MAX(iDATETIME) AS iDATETIME FROM TABLE1 GROUP BY NAME)A WHERE T.NAME=A.NAME AND T.iDATETIME=A.iDATETIME
DROP TABLE TABLE1
(select fty#,max(ACCESSED) as ACCESSED from tb group by fty#) b
where a.fty# = b.fty# and a.ACCESSED = b.ACCESSED