求一个实例多表查询问题的SQL~~~~

Question

表1  zz_Stock_Barcode

Barcode          PName                           PresentQTY
SILCNNOKE71PIK Nokia E71 Pink Silicone Case  30
SILCNNOKE71BLK Nokia E71 Black Silicone Case  28
SILCNNOKE71WHE Nokia E71 White Silicone Case  17
SILCNNOK5000PIK Nokia 5000 Pink Silicone Case  61
HSGLGKE970PIK LG KE970 Pink Housing           15
表2  zz_Stock_inProduct

Barcode         NewQTY ReceiveQTY
SILCNNOKE71PIK 20 18
SILCNNOKE71BLK 30 30
SILCNNOKE71WHE 50 0要求输出后的显示为：
Barcode             PName                          PresentQTY            Standby
SILCNNOKE71PIK Nokia E71 Pink Silicone Case 30               2
SILCNNOKE71BLK Nokia E71 Black Silicone Case 28               0
SILCNNOKE71WHE Nokia E71 White Silicone Case 17               50
SILCNNOK5000PIK Nokia 5000 Pink Silicone Case 61
HSGLGKE970PIK LG KE970 Pink Housing          15输出后的Standby就是表2的NewQTY-ReceiveQTY得出来的值。要SQL语句，不要存储过程。如果解决顺利再加分!

Answer 1

select 
  a.*,
  Standby=isnull((b.NewQTY-b.ReceiveQTY),0)
from
  zz_Stock_Barcode a
left join 
  zz_Stock_inProduct b
on 
  a.Barcode=b.Barcode

Answer 2

select
    a.Barcode,a.PName,a.PresentQTY,b.NewQTY-b.ReceiveQTY as Standby
from
    zz_Stock_Barcode a
left join
    zz_Stock_inProduct b
on
    a.Barcode=b.Barcode

Answer 3

-------------------------------------
--  Author : liangCK 梁爱兰
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-06-25 14:16:22
-------------------------------------
 
--> 生成测试数据: @zz_Stock_Barcode
DECLARE @zz_Stock_Barcode TABLE (Barcode VARCHAR(15),PName VARCHAR(5),PresentQTY VARCHAR(5),c4 VARCHAR(5),c5 VARCHAR(8),c6 VARCHAR(4),c7 INT)
INSERT INTO @zz_Stock_Barcode
SELECT 'SILCNNOKE71PIK','Nokia','E71','Pink','Silicone','Case',30 UNION ALL
SELECT 'SILCNNOKE71BLK','Nokia','E71','Black','Silicone','Case',28 UNION ALL
SELECT 'SILCNNOKE71WHE','Nokia','E71','White','Silicone','Case',17 UNION ALL
SELECT 'SILCNNOK5000PIK','Nokia','5000','Pink','Silicone','Case',61 UNION ALL
SELECT 'HSGLGKE970PIK','LG','KE970','Pink','Housing','15',null
 
--> 生成测试数据: @zz_Stock_inProduct
DECLARE @zz_Stock_inProduct TABLE (Barcode VARCHAR(14),NewQTY INT,ReceiveQTY INT)
INSERT INTO @zz_Stock_inProduct
SELECT 'SILCNNOKE71PIK',20,18 UNION ALL
SELECT 'SILCNNOKE71BLK',30,30 UNION ALL
SELECT 'SILCNNOKE71WHE',50,0--SQL查询如下:
SELECT
    A.*,
    Standby = B.NewQTY - B.ReceiveQTY
FROM @zz_Stock_Barcode AS A
    LEFT JOIN @zz_Stock_inProduct AS B
ON A.Barcode = B.Barcode/*
Barcode         PName PresentQTY c4    c5       c6   c7          Standby
--------------- ----- ---------- ----- -------- ---- ----------- -----------
SILCNNOKE71PIK  Nokia E71        Pink  Silicone Case 30          2
SILCNNOKE71BLK  Nokia E71        Black Silicone Case 28          0
SILCNNOKE71WHE  Nokia E71        White Silicone Case 17          50
SILCNNOK5000PIK Nokia 5000       Pink  Silicone Case 61          NULL
HSGLGKE970PIK   LG    KE970      Pink  Housing  15   NULL        NULL(5 行受影响)
*/

Answer 4

SELECT A.* ,ISNULL(B.NewQTY-B.ReceiveQTY,'') AS Standby  
FROM zz_Stock_Barcode AS A LEFT JOIN zz_Stock_inProduct AS B
ON A.Barcode =B.Barcode

Answer 5

select
    a.Barcode,a.PName,a.PresentQTY,b.NewQTY-b.ReceiveQTY as Standby
from
    zz_Stock_Barcode a
left join
    zz_Stock_inProduct b
on
    a.Barcode=b.Barcode

Answer 6

SQL版兵强马壮，从回帖的速度都看得出来。当版主和钻一起出手，结局就是这样，一个贴，5分钟之后，就已经有这么多回复。唉。连汤都喝不上。

Answer 7

-- =========================================
-- -----------t_mac 小编-------------
   ---希望有天成为大虾---- 
-- =========================================
DECLARE @zz_Stock_Barcode TABLE (Barcode VARCHAR(15),PName VARCHAR(5),PresentQTY VARCHAR(5),c4 VARCHAR(5),c5 VARCHAR(8),c6 VARCHAR(4),c7 INT)
INSERT INTO @zz_Stock_Barcode
SELECT 'SILCNNOKE71PIK','Nokia','E71','Pink','Silicone','Case',30 UNION ALL
SELECT 'SILCNNOKE71BLK','Nokia','E71','Black','Silicone','Case',28 UNION ALL
SELECT 'SILCNNOKE71WHE','Nokia','E71','White','Silicone','Case',17 UNION ALL
SELECT 'SILCNNOK5000PIK','Nokia','5000','Pink','Silicone','Case',61 UNION ALL
SELECT 'HSGLGKE970PIK','LG','KE970','Pink','Housing','15',null
 DECLARE @zz_Stock_inProduct TABLE (Barcode VARCHAR(14),NewQTY INT,ReceiveQTY INT)
INSERT INTO @zz_Stock_inProduct
SELECT 'SILCNNOKE71PIK',20,18 UNION ALL
SELECT 'SILCNNOKE71BLK',30,30 UNION ALL
SELECT 'SILCNNOKE71WHE',50,0select z.*,[standby]=isnull(zz.NewQTY-zz.ReceiveQTY ,null)
from @zz_Stock_Barcode z left join  @zz_Stock_inProduct zz
on z.Barcode =zz.Barcode 
/*------------
SILCNNOKE71PIK Nokia E71 Pink Silicone Case 30 2
SILCNNOKE71BLK Nokia E71 Black Silicone Case 28 0
SILCNNOKE71WHE Nokia E71 White Silicone Case 17 50
SILCNNOK5000PIK Nokia 5000 Pink Silicone Case 61 NULL
HSGLGKE970PIK LG KE970 Pink Housing 15 NULL NULL-------*/

Answer 8

--> table  @zz_Stock_Barcode
DECLARE @zz_Stock_Barcode TABLE (Barcode VARCHAR(15),PName VARCHAR(50),PresentQTY int)
INSERT INTO @zz_Stock_Barcode
SELECT 'SILCNNOKE71PIK','Nokia E71 Pink Silicone Case',30 UNION ALL
SELECT 'SILCNNOKE71BLK','Nokia E71 BlackSilicone Case',28 UNION ALL
SELECT 'SILCNNOKE71WHE','Nokia E71 White Silicone Case',17 UNION ALL
SELECT 'SILCNNOK5000PIK','Nokia 5000 Pink Silicone Case',61 UNION ALL
SELECT 'HSGLGKE970PIK','LG KE970 Pink Housing',15
 
--> table: zz_Stock_inProduct 
DECLARE @zz_Stock_inProduct TABLE (Barcode VARCHAR(14),NewQTY INT,ReceiveQTY INT)
INSERT INTO @zz_Stock_inProduct
SELECT 'SILCNNOKE71PIK',20,18 UNION ALL
SELECT 'SILCNNOKE71BLK',30,30 UNION ALL
SELECT 'SILCNNOKE71WHE',50,0--SQL查询如下:
SELECT
    a.*,
    Standby = isnull(b.NewQTY - b.ReceiveQTY,0)
FROM @zz_Stock_Barcode a
    LEFT JOIN @zz_Stock_inProduct b
ON a.Barcode = b.Barcode/*
Barcode         PName                                              PresentQTY  Standby     
--------------- -------------------------------------------------- ----------- ----------- 
SILCNNOKE71PIK  Nokia E71 Pink Silicone Case                       30          2
SILCNNOKE71BLK  Nokia E71 BlackSilicone Case                       28          0
SILCNNOKE71WHE  Nokia E71 White Silicone Case                      17          50
SILCNNOK5000PIK Nokia 5000 Pink Silicone Case                      61          0
HSGLGKE970PIK   LG KE970 Pink Housing                              15          0(影響 5 個資料列)
*/