如何获得具有相同服务标签的库存 [英] How do I get available stock with same servicetags

问题描述

表1

 Table_1 
  Stockno | StartDate | ServiceTag | Modelno  
  123  2015-08-01 ABC Inspiron 
  123  2015-06-01 DEF Inspiron 
  123  2015-08-01 GHI Inspiron 
  123  2015-08-01 JKL Inspiron 
  456  2015-08-01 MNO Galaxy 
  456  2015-07-01 PQR Galaxy 
 < span class =code-digit> 456  2015-08-01 STU Galaxy 
  456  2015-08-01 VWX Galaxy 
  456  2015-08-01 ABC Galaxy 
 
 Table_2 
  Stockno | TransDate | TransType | ServiceTag | Modelno 
  
  123  2015-08-04  2100  ABC Inspiron 
  123  2015-08-19  2100  GHI Inspiron 
  456  2015-08-25  2100  STU Galaxy 
  123  2015-07-25  2100  DEF Inspiron 

我有两张桌子.Table_1是可用的股票。表_2是售出的库存。我希望剩余库存售后。我写了下面的查询，它工作正常，但问题是它没有返回Galaxy与服务标签= ABC（未售出），因为Inspiron（servicetag = ABC）被出售。所以在剩余库存中我应该得到5个记录而不是我只得到4.如何更正此查询？

 选择 P.Stockno，P.Modelno，P.ServiceTag，Count（P.Stockno） as  ClosingBal 
 从表_ 作为 P 
 其中 P.ServiceTag 中的代码关键字>不 （选择 ServiceTag 来自表2）
 组  by  P.Stockno，P.Modelno ，P.ServiceTag 

解决方案

将 NOT IN 更改为外部联接将其他字段添加到缺少的条件

  SELECT  P.Stockno 
，P.Modelno 
，P.ServiceTag 
，C ount（P.Stockno） as  ClosingBal 
  FROM 表1 P 
  left   OUTER  
  JOIN 表2 t2 
  ON  p.servicetag = t2.servicetag 
  AND  p.stockno = t2.stockno 
  AND  p.Modelno = t2.Modelno 
  WHERE  t2.servicetag  IS   NULL  
  AND  t2.stockno  IS   NULL  
  AND  t2 .Modelno  IS   NULL  
  GROUP   BY  P.Stockno，P.Modelno，P.ServiceTag 

Table_1

Table_1
Stockno|StartDate|ServiceTag|Modelno
123	2015-08-01	ABC	Inspiron
123	2015-06-01	DEF	Inspiron
123	2015-08-01	GHI	Inspiron
123	2015-08-01	JKL	Inspiron
456	2015-08-01	MNO	Galaxy
456	2015-07-01	PQR	Galaxy
456	2015-08-01	STU	Galaxy
456	2015-08-01	VWX	Galaxy
456	2015-08-01	ABC	Galaxy

Table_2
Stockno|TransDate|TransType|ServiceTag|Modelno
                                            
123	2015-08-04	2100	  ABC	  Inspiron
123	2015-08-19	2100	  GHI	  Inspiron
456	2015-08-25	2100	  STU	  Galaxy
123	2015-07-25	2100	  DEF	  Inspiron

I have two tables.Table_1 is available stock. Table_2 is sold stock. I want remaining stock after sales. I wrote the following query,it works fine but the problem is it doesnt return Galaxy with service tag= ABC(not sold) since Inspiron(servicetag=ABC) is sold. So in remaining stock I should get totally 5 records instead I get only 4. How to correct this query?

 Select P.Stockno,P.Modelno,P.ServiceTag, Count(P.Stockno) as ClosingBal
From Table_1 as P
Where  P.ServiceTag  Not in (Select ServiceTag from Table_2 )
Group by P.Stockno,P.Modelno,P.ServiceTag

解决方案

Change the NOT IN to an outer join and add the other fields to the missing conditions

SELECT  P.Stockno
       ,P.Modelno
       ,P.ServiceTag
       ,Count(P.Stockno) as ClosingBal
FROM    Table1 P
left OUTER
JOIN    Table2 t2
    ON  p.servicetag = t2.servicetag
    AND p.stockno = t2.stockno
    AND p.Modelno = t2.Modelno
WHERE   t2.servicetag IS NULL
    AND t2.stockno IS NULL
    AND t2.Modelno IS NULL
GROUP BY P.Stockno,P.Modelno,P.ServiceTag

这篇关于如何获得具有相同服务标签的库存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！