34,576
社区成员
发帖
与我相关
我的任务
分享
declare @tbA table (GPDM int,ZWMC varchar(8),SSD varchar(2),NBBM int)
insert into @tbA
select 10000,'05华能01','上',1 union all
select 20000,'05华能01','下',2 union all
select 30000,'02华能01','上',3 union all
select 40000,'02华能01','下',4
declare @tbB table (NBBM int,QSR datetime)
insert into @tbB
select 1,'2011-01-01' union all
select 2,'2011-01-01' union all
select 3,'2010-01-01' union all
select 4,'2010-01-02'
select GPDM=max(GPDM),ZWMC,QSR=
convert(varchar(10),QSR,120) from @tbA a left join
@tbB b on a.NBBM=b.NBBM group by ZWMC,QSR
having(count(*)=1)
/*
GPDM ZWMC QSR
----------- -------- ----------
30000 02华能01 2010-01-01
40000 02华能01 2010-01-02
*/
declare @tbA table (GPDM int,ZWMC varchar(8),SSD varchar(2),NBBM int)
insert into @tbA
select 10000,'05华能01','上',1 union all
select 20000,'05华能01','下',2 union all
select 30000,'02华能01','上',3 union all
select 40000,'02华能01','下',4 union all
select 50000,'07华能01','上',5 union all
select 60000,'08华能01','下',6
declare @tbB table (NBBM int,QSR datetime)
insert into @tbB
select 1,'2011-01-01' union all
select 2,'2011-01-01' union all
select 3,'2010-01-01' union all
select 4,'2010-01-02' union all
select 5,'2010-12-31' union all
select 6,'2010-12-31'
select GPDM=max(GPDM),a.ZWMC,QSR=convert(varchar(10),c.QSR,120)
from @tbA a left join (
select ZWMC from @tbA group by ZWMC having(count(1)>1)
) b on a.ZWMC=b.ZWMC
left join @tbB c on a.NBBM=c.NBBM
where b.ZWMC is not null
group by a.ZWMC,c.QSR having(count(*)=1)
/*
GPDM ZWMC QSR
----------- -------- ----------
30000 02华能01 2010-01-01
40000 02华能01 2010-01-02
*/
select gpdm,zwmc,qsr from a left join b on a.nbbm=b.nbbm where zwmc='02华能01'