当前位置: 代码迷 >> Sql Server >> 表连接查询
  详细解决方案

表连接查询

热度:49   发布时间:2016-04-24 09:45:10.0
表联接查询
有2张表,分别是进货表和消耗表,每次只从一个供应商取货,消耗完库存在新进货
进货表是
MaterialName     Specision     weight       Time                    Supplier
柴油            0#                10             2014-09-03           港口
柴油            0#                10             2014-09-12           港口 
柴油            0#                20             2014-09-26           中石化
柴油            0#                10             2014-10-11           中石化
柴油            0#                15             2014-10-26           港口
消耗表是
OilDate                Oilstate              Oil               Specision
2014-09-08            柴油          5                    0#  
2014-09-09            柴油          5                    0# 
2014-09-14            柴油          10                  0# 
2014-09-28            柴油          18                  0#  
2014-10-09            柴油          2                    0#  
2014-10-15            柴油          10                  0#  
2014-10-27            柴油          10                  0# 
我想得到的是表(WEIGHT表示进货量,OIL表示消耗量)
DATE                 SPECISION             WEIGHT            OIL               SUPPLIER
2014-09                   0#                            20                  20                       港口    
2014-09                   0#                            20                  18                        中石化
2014-10                   0#                            15                  10                        港口
2014-10                   0#                            10                  12                        中石化
请问这个应该怎么实现
------解决思路----------------------
if OBJECT_ID('inweight') is not null 
drop table inweight
create table inweight 
(
  materialName  varchar(20),
  Specision     varchar(20),
  weight        int ,
  [time]          date,
  Supplier      varchar(20)
)

if OBJECT_ID('outweight') is not null 
drop table outweight
create table outweight 
(
  OilDate  date,
  Oilstate     varchar(20),
  Oil        int , 
  Specision      varchar(20)
)

insert into inweight 
select '柴油','0#',10,'2014-09-03','港口'  union 
select '柴油','0#',10,'2014-09-12','港口'  union 
select '柴油','0#',20,'2014-09-26','中石化'  union 
select '柴油','0#',10,'2014-10-11','中石化'  union 
select '柴油','0#',15,'2014-10-26','港口'



insert into outweight 
select '2014-09-08','柴油',5,'0# '  union  
select '2014-09-09','柴油',5,'0# '  union 
select '2014-09-14','柴油',10,'0# '  union 
select '2014-09-28','柴油',18,'0#  '  union 
select '2014-10-09','柴油',2,'0#  '  union 
select '2014-10-15','柴油',10,'0#  '  union 
select '2014-10-27','柴油',10,'0# '   


默默的帮顶  
------解决思路----------------------
只跟你把这个求出来了


select t1.*,t2.lastday from
(
select CONVERT(varchar(7),[Time],121) as yearmonth,Supplier,SUM([weight]) as weight
from inweight group by CONVERT(varchar(7),[Time],121),Supplier
) t1
inner join (
select CONVERT(varchar(7),[Time],121) yearmonth,Supplier,MAX([Time]) as lastday 
from inweight group by CONVERT(varchar(7),[Time],121),Supplier
) t2 on t1.yearmonth=t2.yearmonth and t1.Supplier=t2.Supplier


结果


------解决思路----------------------
你的业务都已经实际发生了,没有按进出明细算的必要,直接按期望结果统计好了。
-- 进货按规格、月份、供应商统计
-- 又:specision 没这个单词,只有 specification
WITH indetail AS (
    SELECT Convert(varchar(7),time,120) date,
           specision,
           weight,
           supplier
      FROM inweight
)
,insum AS (
    SELECT date,
           specision,
           SUM(weight) weight,
           supplier,
           ROW_NUMBER() OVER(PARTITION BY specision ORDER BY date, supplier) specNo,
           ROW_NUMBER() OVER(PARTITION BY specision,date ORDER BY supplier) monthNo
      FROM indetail
  GROUP BY specision,date,supplier
)
-- 消耗按规格、月份统计
,outdetail AS (
    SELECT Convert(varchar(7),oildate,120) date,
           specision,
           oil
      FROM outweight
)
,outsum AS (
    SELECT date,
           specision,
           SUM(oil) oil,
           1 monthNo
      FROM outdetail
  GROUP BY specision,date
)
-- 因为下面result递归部分不能用 LEFT JOIN,所以进货、消耗要先拼起来
,allsum AS (
    SELECT i.date,
           i.specision,
           i.weight,
           ISNULL(o.oil,0) oil,
           i.supplier,
           i.specNo,
           i.monthNo
      FROM insum i
 LEFT JOIN outsum o
        ON o.specision = i.specision
       AND o.date = i.date
       AND o.monthNo = i.monthNo
)
--SELECT * FROM allsum
,result AS (
    -- 每个规格的第一条
    SELECT s.date,
           s.specision,
           s.weight,
           CASE WHEN s.weight - s.oil < 0  THEN
                s.weight
           ELSE
                s.oil
           END oil,
           s.supplier,
           s.specNo,
           s.monthNo,
           s.weight - s.oil stock, -- 负数表示剩余消耗量
           Convert(nvarchar(15),NULL) msg
      FROM allsum s
     WHERE s.specNo = 1

    -- 同规格、下月,进货、消耗一同算
    UNION ALL 
    SELECT s.date,
           s.specision,
           s.weight,
           CASE WHEN (r.stock + s.weight - s.oil) < 0 THEN
               r.stock + s.weight
           ELSE
               ABS(r.stock) + s.oil
           END oil,
           s.supplier,
           s.specNo,
           s.monthNo,
           r.stock + s.weight - s.oil stock,
           CASE WHEN (r.stock < 0) and (s.monthNo = 1) THEN
                Convert(nvarchar(15),N'上月底不应该有剩余消耗量')
           ELSE
                Convert(nvarchar(15),NULL)
           END msg
      FROM result r
      JOIN allsum s
        ON s.specision = r.specision
       AND s.specNo = r.specNo + 1

)
SELECT * FROM result

date    specision                 weight         oil supplier                           specNo              monthNo       stock msg
------- -------------------- ----------- ----------- -------------------- -------------------- -------------------- ----------- ---------------
2014-09 0#                            20          20 港口                                    1                    1         -18 NULL
2014-09 0#                            20          18 中石化                                  2                    2           2 NULL
2014-10 0#                            15          17 港口                                    3                    1          -5 NULL
2014-10 0#                            10           5 中石化                                  4                    2           5 NULL
  相关解决方案