交叉表查询访问

Question

我有两个表格。

我需要从当前记录的数量中减去订购物品的数量。

我可以像这样获取每个单独物品销售数量的 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

然而，Access并不将最后一个连接识别为有效的连接，如果没有它，我就只能得到笛卡尔乘积。如果我尝试检索quantity而不使用子查询，并且只尝试选择SELECT product.quantity - SUB2.ORDERED，Access要求我将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

- Conner Sams

Answer 1

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

我认为你要使用LEFT JOIN，因为PRODUCT表应该是主表。

如果你使用LEFT JOIN，SUB2.ORDERED列可能为空，所以使用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

- D-Shih

---

根据此文，MS Access的isnull函数只有一个参数。 - Conner Sams

除此之外，这个工作得相当不错。我会再试试看，因为我需要在表单中添加更多的字段来依赖查询，但你已经为我打破了一个巨大的障碍。谢谢你。 - Conner Sams

抱歉，我的失误。你可以使用NZ函数代替。谢谢你的提醒 :) - D-Shih