时间戳

首页 > 解决方案 > 访问交叉表查询

sql - 访问交叉表查询

问题描述

我有两张桌子。

在此处输入图像描述

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

我可以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

标签： sqlms-access

解决方案

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

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

如果你使用LEFT JOIN SUB2.ORDEREDcolumn 可能是 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

推荐阅读