CARDCODE CARDTYPE LOGINSTANC UPDATEDATE
15750076 L 1 2010-5-3
15750076 L 2 2010-5-3
15750076 C 3 2010-5-4
15750076 C 4 2010-5-14
15750076 C 5 2010-7-12
15790086 L 1 2010-6-28
15790086 C 2 2010-7-6
157A0001 L 1 2010-5-1
157A0001 C 2 2010-5-3
15700102 C 1 2010-7-1
15730084 C 1 2010-5-3
有以上表,条件是CARDCODE相同的情况下,LOGINSTANC是1的,CARDTYPE是L的,并且是在7月份的时候才第一次CARDTYPE是C(这句话可能不好理解,就打15750076来说,第一次CARDTYPE是C的时候UPDATE是5月份,这个不算。)结果是
CARDCODE CARDTYPE LOGINSTANC UPDATEDATE
15790086 C 2 2010-7-6
15750076 L 1 2010-5-3
15750076 L 2 2010-5-3
15750076 C 3 2010-5-4
15750076 C 4 2010-5-14
15750076 C 5 2010-7-12
15790086 L 1 2010-6-28
15790086 C 2 2010-7-6
157A0001 L 1 2010-5-1
157A0001 C 2 2010-5-3
15700102 C 1 2010-7-1
15730084 C 1 2010-5-3
有以上表,条件是CARDCODE相同的情况下,LOGINSTANC是1的,CARDTYPE是L的,并且是在7月份的时候才第一次CARDTYPE是C(这句话可能不好理解,就打15750076来说,第一次CARDTYPE是C的时候UPDATE是5月份,这个不算。)结果是
CARDCODE CARDTYPE LOGINSTANC UPDATEDATE
15790086 C 2 2010-7-6
and exists(Select 1 from tabA where CARDCODE =a.CARDCODE and CARDTYPE ='L' and LOGINSTANC=1)
order by UPDATEDATE
select top 1 * from tabA a where CARDTYPE ='C'
and exists(Select 1 from tabA where CARDCODE =a.CARDCODE and CARDTYPE ='L' and LOGINSTANC=1)
order by UPDATEDATE) x
where convert(varchar(7),UPDATEDATE,120)='2010-07'
15790086 C 2 2010-7-6
if object_id('[tb]') is not null drop table [tb]
create table [tb] (CARDCODE varchar(8),CARDTYPE varchar(1),LOGINSTANC int,UPDATEDATE datetime)
insert into [tb]
select '15750076','L',1,'2010-5-3' union all
select '15750076','L',2,'2010-5-3' union all
select '15750076','C',3,'2010-5-4' union all
select '15750076','C',4,'2010-5-14' union all
select '15750076','C',5,'2010-7-12' union all
select '15790086','L',1,'2010-6-28' union all
select '15790086','C',2,'2010-7-6' union all
select '157A0001','L',1,'2010-5-1' union all
select '157A0001','C',2,'2010-5-3' union all
select '15700102','C',1,'2010-7-1' union all
select '15730084','C',1,'2010-5-3'
goselect * from [tb] a
where cardtype='c' and datepart(mm,updatedate)=7 and LOGINSTANC=1
and not exists(select 1 from tb where cardcode=a.cardcode and cardtype=a.cardtype and loginstanc<a.loginstanc)--结果:
CARDCODE CARDTYPE LOGINSTANC UPDATEDATE
-------- -------- ----------- -----------------------
15700102 C 1 2010-07-01 00:00:00.000
--楼主给的结果和规则对不上吧
--猜测下if object_id('[tb]') is not null drop table [tb]
create table [tb] (CARDCODE varchar(8),CARDTYPE varchar(1),LOGINSTANC int,UPDATEDATE datetime)
insert into [tb]
select '15750076','L',1,'2010-5-3' union all
select '15750076','L',2,'2010-5-3' union all
select '15750076','C',3,'2010-5-4' union all
select '15750076','C',4,'2010-5-14' union all
select '15750076','C',5,'2010-7-12' union all
select '15790086','L',1,'2010-6-28' union all
select '15790086','C',2,'2010-7-6' union all
select '157A0001','L',1,'2010-5-1' union all
select '157A0001','C',2,'2010-5-3' union all
select '15700102','C',1,'2010-7-1' union all
select '15730084','C',1,'2010-5-3'
goselect *
from tb t
where CARDTYPE = 'C'
and datepart(mm,UPDATEDATE) = 7
and exists(select 1 from tb where CARDCODE = t.CARDCODE and CARDTYPE = 'L' and LOGINSTANC = 1)
and not exists (select 1 from tb where CARDCODE = t.CARDCODE and CARDTYPE = 'C' and UPDATEDATE< t.UPDATEDATE)