如何从两个选择查询中加入结果 [英] How to join result from two select query

问题描述

我有两个表table1和table2 ItemId表格的关键表2



表1

| itemsId |项目|数量|

----------------------------

1 |项目| 20



表2

| ItemId |状态|数量

---------------------------

1 |项目输出| 5



我在table1 - table2中减去数量，并在New Column Called Remain中分配值现在问题是我想要将结果列加入表1和table2



ItemId |项目|数量|剩余的|

---------------------------------------

1 |项目| 20 | 15



我尝试过：

i have two table table1 and table2 ItemId foreing key to Table 2

Table 1
|itemsId | Item | Quantity|
----------------------------
1 | item | 20

Table 2
|ItemId | Status | Quantity
---------------------------
1 |Item Out| 5

I substract quantity in table1 - table2 and assign the value in New Column Called Remain now the problem is i want join the result column with table 1 and table2

ItemId | Item | Quantity | Remaining |
---------------------------------------
1 | item | 20 | 15

What I have tried:

SELECT 
* FROM 	
		(select i.ItemsId,i.Date,s.[Item Name],i.Quantity,i.[Recieved By],i.[From],i.[Reference No]
		from Store s inner join ItemsMovement i on s.ItemsId = i.ItemsId group by i.ItemsId) T1
	INNER JOIN 
		(select SUM(a.Quantity - b.Quantity) AS Remaining
		FROM Store a inner join ItemsMovement b on a.ItemsId = b.ItemsId 
		where b.Status ='Items In' group by a.ItemsId ) T2

	ON T1.ItemsId = T2

推荐答案

SELECT DISTINCT i.ItemsId,
               i.Date,s.[Item Name],
             i.Quantity,
             i.[Recieved By],
             i.[From],
             i.[Reference No],
             SUM(S.Quantity - i.Quantity)OVER(PARTITION BY i.ItemsId ) AS Remaining
      FROM Store s inner join ItemsMovement i ON s.ItemsId = i.ItemsId 

这篇关于如何从两个选择查询中加入结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！