select * from A where
and A.tr_Date>='2012-05-04' and A.tr_Date<='2012-05-04' order by tr_Date desc,create_time desc当前结果 如下
CREATE_TIME TR_DATE ORG_CODE TELLER_CODE
2012-05-08 02:07:17 2012-05-04 0180 01488
2012-05-08 01:44:11 2012-05-04 0180 01522
2012-05-07 11:09:43 2012-05-04 0180 01522
2012-05-07 10:56:54 2012-05-04 0180 01488
2012-05-07 10:49:23 2012-05-04 0180 01488
2012-05-07 10:49:23 2012-05-04 0180 01488
2012-05-07 10:49:23 2012-05-04 0180 01488
2012-05-07 10:37:51 2012-05-04 0180 01351
2012-05-05 09:03:58 2012-05-04 0202 02015现在想 把TELLER_CODE 取唯一行,条件为CREATE_TIME最新时间。 请问如何在上面的SQL语句基础上进行修改。理想结果 如下
CREATE_TIME TR_DATE ORG_CODE TELLER_CODE
2012-05-08 02:07:17 2012-05-04 0180 01488
2012-05-08 01:44:11 2012-05-04 0180 01522
2012-05-07 10:37:51 2012-05-04 0180 01351
2012-05-05 09:03:58 2012-05-04 0202 02015
谢谢,在线等。
and A.tr_Date>='2012-05-04' and A.tr_Date<='2012-05-04' order by tr_Date desc,create_time desc当前结果 如下
CREATE_TIME TR_DATE ORG_CODE TELLER_CODE
2012-05-08 02:07:17 2012-05-04 0180 01488
2012-05-08 01:44:11 2012-05-04 0180 01522
2012-05-07 11:09:43 2012-05-04 0180 01522
2012-05-07 10:56:54 2012-05-04 0180 01488
2012-05-07 10:49:23 2012-05-04 0180 01488
2012-05-07 10:49:23 2012-05-04 0180 01488
2012-05-07 10:49:23 2012-05-04 0180 01488
2012-05-07 10:37:51 2012-05-04 0180 01351
2012-05-05 09:03:58 2012-05-04 0202 02015现在想 把TELLER_CODE 取唯一行,条件为CREATE_TIME最新时间。 请问如何在上面的SQL语句基础上进行修改。理想结果 如下
CREATE_TIME TR_DATE ORG_CODE TELLER_CODE
2012-05-08 02:07:17 2012-05-04 0180 01488
2012-05-08 01:44:11 2012-05-04 0180 01522
2012-05-07 10:37:51 2012-05-04 0180 01351
2012-05-05 09:03:58 2012-05-04 0202 02015
谢谢,在线等。
(
select * from A where
and A.tr_Date>='2012-05-04' and A.tr_Date<='2012-05-04' order by tr_Date desc,create_time desc
)
select * from f t where CREATE_TIME=(select max(CREATE_TIME) from f where TELLER_CODE=t.TELLER_CODE )
go
create table [TB] (CREATE_TIME datetime,TR_DATE datetime,ORG_CODE nvarchar(8),TELLER_CODE nvarchar(10))
insert into [TB]
select '2012-05-08 02:07:17','2012-05-04','0180','01488' union all
select '2012-05-08 01:44:11','2012-05-04','0180','01522' union all
select '2012-05-07 11:09:43','2012-05-04','0180','01522' union all
select '2012-05-07 10:56:54','2012-05-04','0180','01488' union all
select '2012-05-07 10:49:23','2012-05-04','0180','01488' union all
select '2012-05-07 10:49:23','2012-05-04','0180','01488' union all
select '2012-05-07 10:49:23','2012-05-04','0180','01488' union all
select '2012-05-07 10:37:51','2012-05-04','0180','01351' union all
select '2012-05-05 09:03:58','2012-05-04','0202','02015'select * from [TB]
select distinct B.CREATE_TIME,B.TR_DATE,B.ORG_CODE,B.TELLER_CODE
from TB A
cross apply (select top 1 CREATE_TIME,TR_DATE,ORG_CODE,TELLER_CODE from TB where A.TELLER_CODE =TELLER_CODE order by CREATE_TIME desc) B
/*
2012-05-05 09:03:58.000 2012-05-04 00:00:00.000 0202 02015
2012-05-07 10:37:51.000 2012-05-04 00:00:00.000 0180 01351
2012-05-08 01:44:11.000 2012-05-04 00:00:00.000 0180 01522
2012-05-08 02:07:17.000 2012-05-04 00:00:00.000 0180 01488*/
请问前面不变,后面变条件的语句有什么方法实现吗?
select * from A where
and A.tr_Date>='2012-05-04' and A.tr_Date<='2012-05-04' order by tr_Date desc,create_time desc)f t where CREATE_TIME=(select max(CREATE_TIME) from (select * from A where
and A.tr_Date>='2012-05-04' and A.tr_Date<='2012-05-04' order by tr_Date desc,create_time desc)n where TELLER_CODE=t.TELLER_CODE )