如何加入和求和表（MS SQL）

本文介绍了如何加入和求和表（MS SQL）的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有两个表Tbl_Device和Tbl_ReceivedQty

我想要内部加入设备的记录总和

i尝试加入此查询但是失败了

i have two table Tbl_Device and Tbl_ReceivedQty
I want the sum of record with inner join Device

i tried to join with this query but failed

SELECT     Tbl_Device.Device_Name, Tbl_ReceivedQty.Qty_Device_ID, Tbl_ReceivedQty.Qty_User, Tbl_ReceivedQty.Qty_Date
 FROM         Tbl_Device INNER JOIN  Tbl_ReceivedQty ON Tbl_Device.Device_ID = Tbl_ReceivedQty.Qty_Device_ID
 where Qty_Date  between '2013-07-21' and '2013-07-23'  (select Qty_Device_ID, sum(Qty_Received) from Tbl_ReceivedQty group by Qty_Device_ID)

我如何加入和总结表

how can i join and sum table

推荐答案

SELECT distinct Tbl_Device.Device_Name,
temp1.Qty_Device_ID,
Tbl_ReceivedQty.Qty_User,
Tbl_ReceivedQty.Qty_Date,
temp1.sumOfQty
FROM Tbl_Device
INNER JOIN Tbl_ReceivedQty
ON Tbl_Device.Device_ID = Tbl_ReceivedQty.Qty_Device_ID
inner join (
select Qty_Device_ID as 'Qty_Device_ID', sum(Qty_Received) as 'sumOfQty' 
from Tbl_ReceivedQty where Qty_Date between '2013-07-21' and '2013-07-23'
group by Qty_Device_ID) temp1
on Tbl_Device.Device_ID = temp1.Qty_Device_ID

SELECT Qty_Device_ID, sum(Qty_Received)
FROM Tbl_ReceivedQty
WHERE Qty_Date  between '2013-07-21' and '2013-07-23'
GROUP BY Qty_Device_ID

SELECT T1.Device_Name, T2.Qty_Device_ID, T2.Qty_User, T2.Qty_Date, SUM(T3.Qty_Received) AS SubTotal
FROM Tbl_Device AS T1 INNER JOIN  Tbl_ReceivedQty AS T2 ON T1.Device_ID = T2.Qty_Device_ID 
    INNER JOIN Tbl_ReceivedQty AS T3 ON T1.Qty_Device_ID = T3.Qty_Device_ID
WHERE Qty_Date  BETWEEN '2013-07-21' AND '2013-07-23'
GROUP BY T1.Device_Name, T2.Qty_Device_ID, T2.Qty_User, T2.Qty_Date

 SELECT     Tbl_Device.Device_Name, Tbl_ReceivedQty.Qty_Device_ID, Tbl_ReceivedQty.Qty_User, Tbl_ReceivedQty.Qty_Date,
 (select sum(Qty_Received) From Tbl_ReceivedQty where Qty_Device_ID = Tbl_Device.Device_ID) as SubTotal
FROM         Tbl_Device INNER JOIN  Tbl_ReceivedQty ON Tbl_Device.Device_ID = Tbl_ReceivedQty.Qty_Device_ID
where Qty_Date  between '2013-07-21' and '2013-07-23'

这篇关于如何加入和求和表（MS SQL）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！