sql - 尝试从不同的表中添加数量
问题描述
我的任务是在 SQL 服务器上创建一个数据库,以反映我们在 Access 上的内容(由于内存容量增加)。我正在尝试编写一个查询来比较三个表中产品库存水平的数量,并找出差异(如果有)。我还想写一个查询,让我可以提取出连续 7 天以上出现偏差的产品的报告。这个等式是表 B + C = 表 A。这是我到目前为止的代码:
CREATE TABLE EWM
(
Date_loaded date,
Sap_code bigint,
Product_description varchar(100),
Location ntext,
Storage_type varchar(50),
Quantity int,
Sap_batch ntext,
Expiry_date date,
Stock_type varchar(50)
);
CREATE TABLE USOR
(
Date_Loaded date,
Sap_Code bigint,
Product_description varchar(100),
Pack text,
Cost_price float,
Trade_price float,
Stock int,
Location text,
);
CREATE TABLE Wamas
(
Date_Loaded date,
Tu_barcode bigint,
Sap_code bigint,
Product_description varchar(100),
Quantity int,
LG_code varchar(100),
Geocode text,
Lot text,
Expiry_date date
)
CREATE TABLE DSOR
(
Date_Loaded datetime,
Sap_code bigint,
Product_description varchar(100),
Bin_location text,
Location varchar(50),
Pack text,
Units int,
Total int,
);
查询尝试:
select
USOR.Sap_code, USOR.Product_description, USOR.Pack, USOR.Location,
sum(USOR.Stock+DSOR.Total) as Total_quantity
from
USOR
join
DSOR on USOR.Sap_Code = DSOR.Sap_code
where
USOR.Sap_Code = DSOR.Sap_code
group by
USOR.Sap_code
目前收到一条错误消息,其中加入说
'USOR.Product_description' 在选择列表中无效,因为它不包含在聚合函数或 GROUP BY 子句中。
任何帮助将不胜感激。
***更新。谢谢大家的反馈。正如我之前提到的,我是 SQL 的完整初学者。通过使用内部连接将两个表中的数量组合起来,我设法得到了我正在寻找的结果。我现在正在尝试将该查询的结果转换为一个单独的表,我将在另一个查询中使用该表,但这样做时遇到了麻烦。这是我的尝试:
(
select USOR.Sap_Code AS Sap_code, USOR.Product_description as Product_description, USOR.Pack as Pack, USOR.Location as Location, sum(USOR.Stock+DSOR.Total) as Total_quantity
from USOR
join DSOR
on USOR.Sap_Code = DSOR.Sap_code as Total_quantity
group by USOR.Sap_code, USOR.Product_description, USOR.Pack, USOR.Location
); ````
I am getting the following error message:
'Incorrect syntax near the keyword 'select'.
Msg 156, Level 15, State 1, Line 56
Incorrect syntax near the keyword 'as'
解决方案
select USOR.Sap_code, USOR.Product_description, USOR.Pack, USOR.Location,
sum(USOR.Stock+DSOR.Total) as Total_quantity
from USOR
join DSOR on USOR.Sap_Code = DSOR.Sap_code
group by USOR.Sap_code, USOR.Product_description, USOR.Pack, USOR.Location