sql - declare table inserted values duplicated
问题描述
I try to get two different table values to insert one declare table but the employee ID is duplicate,
DECLARE @WSotable TABLE
(
Employee int,
ChargedQty int,
ReturnQty int
)
--insert first table data
INSERT INTO @WSotable
SELECT DISTINCT
MD_Employee.ID,
SUM(TR_InvoiceDetails.InvoiceQuantity) AS ChargedQty,
0 AS ChargedQty
FROM
TR_SalesOrderHeader
INNER JOIN
TR_InvoiceHeader ON TR_InvoiceHeader.SalesOrderID = TR_SalesOrderHeader.ID
INNER JOIN
TR_InvoiceDetails ON TR_InvoiceDetails.InvoiceID = TR_InvoiceHeader.ID
INNER JOIN
MD_ItemMaster ON TR_InvoiceDetails.ItemID = MD_ItemMaster.ID
INNER JOIN
MD_Supplier ON MD_ItemMaster.SupplierID= MD_Supplier.ID
INNER JOIN
MD_Employee ON TR_SalesOrderHeader.CreatedBy = MD_Employee.ID
WHERE
MD_Supplier.ID = 3
AND MD_ItemMaster.ID = 572
GROUP BY
MD_Employee.ID
--insert second table data
INSERT INTO @WSotable
SELECT DISTINCT
MD_Employee.ID,
0 AS ChargedQty,
SUM(TR_SalesReturnDetails.ReturnQuantity) AS ReturnQty
FROM
TR_SalesReturnHeader
INNER JOIN
TR_SalesReturnDetails ON TR_SalesReturnDetails.SalesReturnID = TR_SalesReturnDetails.ID
INNER JOIN
MD_ItemMaster ON TR_SalesReturnDetails.ItemID = MD_ItemMaster.ID
INNER JOIN
MD_Supplier ON MD_ItemMaster.SupplierID= MD_Supplier.ID
INNER JOIN
MD_Employee ON TR_SalesReturnHeader.CreatedBy = MD_Employee.ID
WHERE
MD_Supplier.ID = 3
AND MD_ItemMaster.ID = 572
GROUP BY
MD_Employee.ID
Result:
I need to stop EmployeeID
duplicates
解决方案
Return the data with a final aggregation:
select Employee, sum(ChargedQty) ChargedQty, sum(ReturnQty) ReturnQty
from @WSotable
group by Employee
推荐阅读
- java - 排序在本地工作但不在 kubernetes 上的文件
- django - 在 Django 中创建一个可以选择性地应用于超级用户的权限
- python - 在 mpi 编译的 fortran 可执行文件上从 python 调用 mpirun
- ruby-on-rails - Rails 6 HasManyThroughOrderError 与 Rolify
- javascript - 如何为嵌入 Discord.js 制作随机颜色
- algorithm - 几乎均匀数据的压缩算法
- python - 我收到错误 TypeError: 'int' object is not callable
- google-apps-script - Google Apps 脚本的分页
- java - 我怎样才能优化这个功能(初学者)JAVA
- javascript - 使用 NodeJS 从 MySQL 中删除记录