15.6.8-sql小技巧－互聯網 - 大數據

文章出處

取月頭月尾：

declare @someDay datetime,@firstDay datetime,@endDay datetime
set @someDay='2015.2.2'
set @firstDay=dateadd(month,datediff(month,0,@someDay),0)
set @endDay=dateadd(month,datediff(month,-1,@someDay),-1)

select @someDay,@firstDay,@endDay

亮點在這里：

declare @someDay datetime, @someDay2 datetime
set @someDay =0
set @someDay2=-1
select @someDay,@someDay2

不用游標，插入和升級數據：

create table #AAA(id int, AAA int, BBB int,CCC int)
create table #BBB(id int, CCC int)

insert into #AAA select　1,1,1,1 union select 2,2,2,2 union select 3,3,3,3 union select 4,4,4,4
insert into #BBB select 1,10 union select 2,6 union select 4,7-- union select 5,10 union select 8,100

update #AAA set CCC=(select CCC from #BBB where #BBB.id=#AAA.id) where exists(select id from #BBB where id=#AAA.id)
insert into #AAA select id,0,0,CCC from #BBB where not exists (select Id from #AAA where id=#BBB.id)
select * from #AAA

drop table #AAA
drop table #BBB

在#AAA的基礎上升級#BBB的數據，有則改無則加

在上面的基礎上，繼續升級：

create table #AAA(id int,aaa int,bbb int,ccc int)
create table #BBB(id int,ddd int,eee int)

insert into #AAA select 1,1,1,1 union select 2,2,2,2 union select 3,3,3,3 union select 4,4,4,4
insert into #BBB select 1,2,3 union select 2,3,4 union select 5,6,7

update #AAA    
    set aaa=isnull((select top 1 ddd from #BBB where #BBB.id=#AAA.id),0),
        bbb=isnull((select top 1 eee from #BBB where #BBB.id=#AAA.id),0)
    --where exists (select id from #BBB where #BBB.id=#AAA.id)

update #AAA    
    set aaa=isnull(b.ddd,0),
        bbb=isnull(b.eee,0)
    from #AAA a,#BBB b
    where a.id=b.id

insert into #AAA
    select id,ddd,eee,0 from #BBB
    where not exists (select id from #AAA where #AAA.id=#BBB.id)


select * from #AAA
drop table #AAA
drop table #BBB