重要的sql

一  根据当前时间来得到某些记录

 

 SELECT a.LineId, U_LineName FROM [@FSCXFRUNORDERL]
a inner join [@FSCXFRUNORDER] c
on a.docEntry=c.DocEntry AND IsNull(c.U_IsActive,'N')='Y' inner join
[@FSCXFSHOPTURN] b on c.DocEntry=b.U_RunEntry WHERE IsNull(a.U_IsActive,'N')='Y'
AND U_ShopCode=N'XYBM' AND IsNull(U_PineCode,'')=N'XYCJ' and
(replace(convert(varchar(8),getdate(),108),'00:','24:') between U_StartTime+':00' and
replace(U_EndTime,'00:','24:')+':00' or
(convert(varchar(5),getdate(),108) between U_StartTime and U_EndTime))

 

 

二、分记录

  declare @test table(
title int,--标题
num decimal(18,6), --数量
unitqty decimal(18,6), --单位
qty decimal(18,6)
)
insert into @test values(1,100,50,500)
insert into @test values(2,123,50,3000)
insert into @test values(3,234,50,1111)
insert into @test values(4,120,50,2222)
insert into @test values(5,230,50,5555)

declare @out int
set @out=234

select *
from
(
select
  title,
  case
    when 20-(select sum(num) from @test where title<=t.title)<0
      then 20-isnull((select sum(num) from @test where title<t.title),0)
    else num
  end as num,
  unitqty,
    case
    when 20-(select sum(num) from @test where title<=t.title)<0
      then qty/num*(20-isnull((select sum(num) from @test where title<t.title),0))
    else qty
  end as qty
from
  @test t
) tt
where num>0