none
求助,查询商品销售 RRS feed

  • 问题

  • sales_master
    SID sales_id sales_date
    s001 A01 18/10/2016
    s002 A01 19/11/2016
    s003 A02 10/12/2016
    S004 A03 10/12/2016
    sales_detail
    sid pid amount sales_date
    s001 p01 10 18/10/2016
    s001 p02 20 18/10/2016
    s002 p01 10 19/11/2016
    s003 p03 30 10/12/2016
    s003 p01 10 10/12/2016
    s004 p01 10 10/12/2016
    Goods_department
    pid dept_id amend_date
    p01 A1 1/10/2016
    p01 A2 5/11/2016
    p01 B2 1/12/2016
    p02 A1 1/10/2016
    p03 A2 11/12/2016
    p03 B2 11/12/2016

    Goods_department是商品所屬部門的表,如修改p01这个商品的dept_id,就会在表插入一条新记录,但查询商品销售的时候要取商品销售日期对应的dept_id,
    如:2016-11-5修改了P01的dept_id等于A2,那么2016-11-5至2016-12-1这段时间内,p01这个商品的dept_id都是A2

    请问用什么语句查询出下面的结果?

    SID PID Sales_date dept_id amount sales_id
    S001 P01 18/10/2016   A1 10   A01
    S002 P01 18/10/2016   A2 10   A01
    S004 p01 10/12/2016    B2 10   A03

    2016年12月13日 3:54

全部回复

  • Hi Shing Ip,

    由于不清楚为何在你的最后结果中没有s003,我做了如下的示例实现你要的结果你可以看下。如果不是你想要的结果,你可以自己按需求尝试修改。或者,你可以阐明如下几个问题。

    1.  为什么s003不出现在最终结果;

    2. 为社么s002sales_date会变成“18/10/2016”, 而不是“19/11/2016”。

    declare @sales_master table
    (SID varchar(20), sales_id varchar(20), sales_date date)
    insert into @sales_master values 
    ('s001','A01','2016-10-18')
    ,('s002','A01','2016-11-19')
    ,('s003','A02','2016-12-10')
    ,('s004','A03','2016-12-10')
    
    
    declare @sales_detail table 
    (sid varchar(20), pid varchar(20), amount int,  sales_date date)
    insert into @sales_detail values
    ('s001','p01',10,'2016-10-18')
    ,('s001','p02',20,'2016-10-18')
    ,('s002','p01',10,'2016-11-19')
    ,('s003','p03',30,'2016-12-10')
    ,('s003','p01',10,'2016-12-10')
    ,('s004','p01',10,'2016-12-10')
    
    
    declare @Goods_department table
    (pid varchar(20),  dept_id varchar(20),  amend_date date)
    insert into @Goods_department values
    ('p01','A1','2016-10-01')
    ,('p01','A2','2016-11-05')
    ,('p01','B2','2016-12-01')
    ,('p02','A1','2016-10-01')
    ,('p03','A2','2016-12-11')
    ,('p03','B2','2016-12-11')
    
    
    --select * from @sales_master 
    --select * from @sales_detail 
    --select * from @Goods_department 
    
    
    select sm.SID, sd.pid, sm.sales_date, gd.dept_id,  sd.amount, sm.sales_id, gd.amend_date , ROW_NUMBER() OVER(ORDER BY sm.SID,gd.amend_date) RN 
    from @sales_master sm
    inner join @sales_detail sd on sm.SID = sd.sid and sm.sales_date = sd.sales_date
    left join @Goods_department gd on sd.pid = gd.pid and sd.sales_date > gd.amend_date  
    ;with cte as
    (
    	select sm.SID, sd.pid, sm.sales_date, gd.dept_id,  sd.amount, sm.sales_id, gd.amend_date, ROW_NUMBER() OVER(ORDER BY sm.SID,gd.amend_date) RN 
    	from @sales_master sm
    	inner join @sales_detail sd on sm.SID = sd.sid and sm.sales_date = sd.sales_date
    	left join @Goods_department gd on sd.pid = gd.pid and sd.sales_date > gd.amend_date  
    )
    ,ctee AS
    (
    	select SID, MAX(amend_date) as maxamedn_date
    	from cte 
    	group by SID
    )
    ,cteee as
    (
    	select SID,amend_date, min(RN) as MINRN
    	from cte 
    	group by SID, amend_date
    )
    ,ctee1 as
    ( 
    	SELECT ce.SID, ce.maxamedn_date, cee.MINRN
    	FROM cteee cee 
    	join ctee ce on cee.SID = ce.SID and cee.amend_date = ce.maxamedn_date 
    )
    
    --select * from ctee1 
    
    SELECT c.SID, c.pid,c.sales_date,c.dept_id,c.amount,c.sales_id 
    FROM cte c
    join ctee1 ce on c.RN = ce.MINRN 

    Best Regards,

    Albert Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2016年12月14日 9:07