SQL求解

Question

根据申请的数量-使用数量求剩余数量,表1的date_from,date_to为申请有效期,超过有效期未使用的数量则为失效,不计在申请数量之内,两表均无主键
表1结构及数据:
sid     sq_name     date_from   date_to        sq_qty
001     AAAA        2009-01-01  2010-01-01     20
002     BBBB        2010-01-01  2011-01-01     30
001     AAAA        2009-10-01  2010-10-01     10
001     AAAA        2011-01-01  2012-01-01     10
003     CCCC        2007-01-01  2008-01-01     10表2结构及数据:
sid     sq_name     qty_used    date_used
001     AAAA        5           2009-05-01
001     AAAA        3           2010-02-01
003     CCCC        5           2008-01-01要求的结果根据查询日期可以变化,假如现在日期是2010-05-01
sid     sq_name     sy_qty      date1(这个为未最早的未失效的申请开始日(date_from))
001     AAAA        17          2009-10-01
002     BBBB        30          2010-01-01
003     CCCC        0           这个日期随便取,可以为空要求的结果根据查询日期可以变化,假如现在日期是2099-01-01
sid     sq_name     sy_qty(剩余数量)      date1(这个为未最早的未失效的申请开始日(date_from))
001     AAAA        0                     这个日期随便取,可以为空
002     BBBB        0                     这个日期随便取,可以为空
003     CCCC        0                     这个日期随便取,可以为空大概的业务也就是查询的日期,计算剩余量,如果对应的申请记录已过期则不计算在剩余数量内
盼各位帮忙,谢谢!

Answer 1

select t1.sid,t1.sq_name,t1.sq_qty - isnull(sum(t2.qty_used),0) sy_qty,t1.date_from date1 from
(
select t.sid,t.sq_name,isnull(sum(a.sq_qty),0) as sq_qty,min(a.date_from) date_from
from (select distinct sid, sq_name from 表1) t left join 表1 a on t.sq_name=a.sq_name
and a.date_to>'2010-05-01'
group by t.sid,t.sq_name
) t1 left join 
(
select t.sid,t.sq_name,isnull(a.qty_used,0) as qty_used,a.date_used
from (select distinct sid, sq_name from 表2) t left join 表2 a on t.sq_name=a.sq_name) t2 on t1.sq_name=t2.sq_name and t2.date_used>=t1.date_from
group by t1.sid,t1.sq_name,t1.date_from,t1.sq_qty

Answer 2

select t1.sid,t1.sq_name,t1.sq_qty - isnull(sum(t2.qty_used),0) sy_qty,t1.date_from date1 from
(
select t.sid,t.sq_name,isnull(sum(a.sq_qty),0) as sq_qty,min(a.date_from) date_from
from (select distinct sid, sq_name from 表1) t left join 表1 a on t.sq_name=a.sq_name
and a.date_to>'2010-05-01'
group by t.sid,t.sq_name
) t1 left join 
(
select t.sid,t.sq_name,isnull(a.qty_used,0) as qty_used,a.date_used
from (select distinct sid, sq_name from 表2) t left join 表2 a on t.sq_name=a.sq_name) t2 on t1.sq_name=t2.sq_name and t2.date_used>=t1.date_from
group by t1.sid,t1.sq_name,t1.date_from,t1.sq_qty/*sid  sq_name sy_qty      date1                                                  
---- ------- ----------- ------------------------------------------------------ 
001  AAAA    17          2009-10-01 00:00:00.000
002  BBBB    30          2010-01-01 00:00:00.000
003  CCCC    0           NULL（所影响的行数为 3 行）*/
select t1.sid,t1.sq_name,t1.sq_qty - isnull(sum(t2.qty_used),0) sy_qty,t1.date_from date1 from
(
select t.sid,t.sq_name,isnull(sum(a.sq_qty),0) as sq_qty,min(a.date_from) date_from
from (select distinct sid, sq_name from 表1) t left join 表1 a on t.sq_name=a.sq_name
and a.date_to>'2099-01-01'
group by t.sid,t.sq_name
) t1 left join 
(
select t.sid,t.sq_name,isnull(a.qty_used,0) as qty_used,a.date_used
from (select distinct sid, sq_name from 表2) t left join 表2 a on t.sq_name=a.sq_name) t2 on t1.sq_name=t2.sq_name and t2.date_used>=t1.date_from
group by t1.sid,t1.sq_name,t1.date_from,t1.sq_qty/*sid  sq_name sy_qty      date1                                                  
---- ------- ----------- ------------------------------------------------------ 
001  AAAA    0           NULL
002  BBBB    0           NULL
003  CCCC    0           NULL（所影响的行数为 3 行）*/

Answer 3

if OBJECT_ID('ta') is not null
drop table ta
go
create table ta(
sid varchar(10),
sq_name varchar(20),
date_from date,
date_to date,
sq_qty int
)
insert into ta
select '001','AAAA','2009-01-01','2010-01-01',20 union all
select '002','BBBB','2010-01-01','2011-01-01',30 union all
select '001','AAAA','2009-10-01','2010-10-01',10 union all
select '001','AAAA','2011-01-01','2012-01-01',10 union all
select '003','CCCC','2007-01-01','2008-01-01',10 union all
select '004','DDDD','2010-01-01','2011-01-01',10
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb(
sid varchar(10),
sq_name varchar(20),
qty_used int,
date_used date
)
insert into tb
select '001','AAAA',5,'2009-05-01' union all
select '001','AAAA',3,'2010-02-01' union all
select '003','CCCC',5,'2008-01-01' union all
select '002','BBBB',5,'2010-05-01'select * from ta
select * from tbdeclare @q_date date
set @q_date='2010-05-01'
;with t_valid as
(
select * from ta where DATEDIFF(DAY,@q_date,date_to)>0
)
,t_left as (
select tl.sid,tl.sq_name,tl.sq_qty-isnull(tb.qty_used,0) as qty_left,tl.date_from,tl.date_to 
from t_valid tl left join tb on tl.sid=tb.sid and tl.sq_name=tb.sq_name 
and tb.date_used between tl.date_from and tl.date_to
)
,t_result as (
select sid,sq_name,SUM(qty_left) as sq_left,MIN(date_from) as date_first 
from t_left 
group by sid,sq_name
)
select tt.*,isnull(tr.sq_left,0) as sq_left,tr.date_first 
from (select distinct sid,sq_name from ta) tt left join t_result tr 
on tt.sid=tr.sid and tt.sq_name=tr.sq_name
楼主试试看 用了三个CTE

Answer 4

use PracticeDB
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION FN_TEST
(
    @NOWDATE DATE
)
RETURNS
@TEMP TABLE
(
    SID VARCHAR(10),
    SQ_NAME VARCHAR(10),
    SY_QTY INT,
    DATE1 DATE
)
AS
BEGIN
        
        
        ;WITH T1
        AS
        (
select t.sid,t.sq_name,isnull(sum(a.sq_qty),0) as sq_qty,min(a.date_from) date_from
from (select distinct sid, sq_name from ta) t left join ta a on t.sq_name=a.sq_name
and a.date_to>@NOWDATE
group by t.sid,t.sq_name
        )
 
        INSERT INTO @TEMP
         select t1.sid,t1.sq_name,t1.sq_qty - isnull(sum(tb.qty_used),0) sy_qty,t1.date_from date1 
         from T1 left join tb on t1.sq_name=tb.sq_name and tb.date_used>=t1.date_from
         group by t1.sid,t1.sq_name,t1.date_from,t1.sq_qty
      RETURN 
  END 
 GO
 
select * from  dbo.FN_TEST('2010-05-01') 
SID SQ_NAME SY_QTY DATE1
001 AAAA 17 2009-10-01
002 BBBB 30 2010-01-01
003 CCCC 0 NULLselect * from  dbo.FN_TEST('2099-01-01') 
SID SQ_NAME SY_QTY DATE1
001 AAAA 0 NULL
002 BBBB 0 NULL
003 CCCC 0 NULL

Answer 5

嗯，估计jaydom 的效率应该高很多