sql - Access 交叉表查询

我有两张桌子。



我需要从当前数量中减去订购的项目数量
记录下来。

我可以像这样获得每个项目的销售的count():

SELECT SALES.PRODUCT_ID AS ORDERED_ID
         ,COUNT(SALES.PRODUCT_ID) AS ORDERED
   FROM SALES
   GROUP BY SALES.PRODUCT_ID

这给了我:

ORDERED_ID  ORDERED
1201    2
1202    2
1204    2
1205    3
1206    1
1207    2
1208    1
1209    1
1210    3

获取数量只是一个问题

SELECT PRODUCT.PRODUCT_ID AS INVEN_ID
         ,PRODUCT.QUANTITY AS INVEN
    FROM PRODUCT

这给了我:

INVEN_ID    INVEN
1199    5
1200    2
1201    33
1202    44
1203    55
1204    66
1205    77
1206    88
1207    99
1208    110
1209    121
1210    132

我花了几个小时解决这个问题，并放弃了我认为应该解决的方法:

SELECT SUB1.INVEN - SUB2.ORDERED
FROM
 (SELECT PRODUCT.PRODUCT_ID AS INVEN_ID
         ,PRODUCT.QUANTITY AS INVEN
    FROM PRODUCT
 )AS SUB1
,(SELECT SALES.PRODUCT_ID AS ORDERED_ID
         ,COUNT(SALES.PRODUCT_ID) AS ORDERED
   FROM SALES
   GROUP BY SALES.PRODUCT_ID
  )AS SUB2
  INNER JOIN SUB1 ON SUB1.INVEN_ID = SUB2.ORDERED_ID

但是，访问不能将最后一个联接识别为有效联接，没有它，我只能得到笛卡尔积。如果我尝试在没有子查询的情况下检索quantity，而只是尝试对SELECT product.quantity - SUB2.ORDERED进行访问，则我将product.quantity - SUB2.ORDERED放入了聚合函数。当我按照它说的去做时，它告诉我product.quantity - SUB2.ORDERED不能在聚合函数中。我很茫然。

编辑:

Final Solution:

SELECT SUB1.INVEN_ID AS PRODUCT_ID
       ,SUB1.PRODUCT_NAME AS PRODUCT_NAME
       ,SUB1.PRICE AS PRICE
       ,SUB1.INVEN - NZ(SUB2.ORDERED,0) AS AVAILABLE
FROM
 (SELECT PRODUCT.PRODUCT_ID AS INVEN_ID
         ,PRODUCT.PRODUCT_NAME AS PRODUCT_NAME
         ,PRODUCT.PRICE AS PRICE
         ,PRODUCT.QUANTITY AS INVEN
    FROM PRODUCT
 )AS SUB1
 LEFT  JOIN
(SELECT SALES.PRODUCT_ID AS ORDERED_ID
         ,COUNT(SALES.PRODUCT_ID) AS ORDERED
   FROM SALES
   GROUP BY SALES.PRODUCT_ID
  )AS SUB2 ON SUB1.INVEN_ID = SUB2.ORDERED_ID

最佳答案

您的INNER JOIN应该放在第一个子查询之后。

我认为您正在寻找LEFT JOIN，因为PRODUCT表应该是主表。

如果您使用LEFT JOIN，那么SUB2.ORDERED列可能为NULL，因此请使用NZ函数
或IIF(ISNULL(SUB2.ORDERED),0,SUB2.ORDERED)检查。

你可以试试看

SELECT SUB1.INVEN - NZ(SUB2.ORDERED,0)
FROM
(SELECT PRODUCT.PRODUCT_ID AS INVEN_ID
         ,PRODUCT.QUANTITY AS INVEN
    FROM PRODUCT
)AS SUB1
LEFT JOIN
(SELECT SALES.PRODUCT_ID AS ORDERED_ID
         ,COUNT(SALES.PRODUCT_ID) AS ORDERED
   FROM SALES
   GROUP BY SALES.PRODUCT_ID
)AS SUB2 ON SUB1.INVEN_ID = SUB2.ORDERED_ID