如何创建复杂的VIEW并加入此表 [英] How to create complex VIEW and join this tables
问题描述
首先我很抱歉，如果我不能写好标题并欢迎任何建议
i有 st_items 那个有 stitems_Type 字段，此字段确定该商品是库存还是带有自定义序列的库存。
st_items数据行示例： -
first i'm sorry if i cant write a good title and welcome to any recommendations
i have "st_items" that have "stitems_Type" field , this field determine if the item is "stock" or "stock with custom serial".
st_items data rows examples :-
----------------------------------------------------
stitems_ID | stitems_Type | stitems_MinBalance | ---
1 | 0 | 10 | ---
2 | 0 | 20 | ---
3 | 1 | 5 | ---
4 | 1 | 15 | ---
----------------------------------------------------
如果stitems_Type = 0，则该项目为stock
在这种情况下，与此项目相关的所有股票信息将存储在名为 stock_noserials 的表格中，并带有以下行示例：
if "stitems_Type = 0" , the item is "stock"
in this case all stocks information that related to this item will stored in table called "stock_noserials" with this rows example :
---------------------------------
id | stitems_ID | StockQnty | ---
1 | 1 | 5 | ---
2 | 1 | 3 | ---
3 | 2 | 500 | ---
4 | 2 | 150 | ---
---------------------------------
作为stock_noserials表，项目1的股票= 8 （StockQnty colmun的总和）和项目2的股票= 650 （StockQnty的总和） colmun）
如果stitems_Type = 1，则该项目为连续剧库存
在这种情况下，与此项目相关的所有股票信息都将存储在名为 purchases_item_seriels 的表格中，并带有以下行示例：
as "stock_noserials " table , the stock of item 1 = 8 (sum of StockQnty colmun) and the stock of item 2 = 650 (sum of StockQnty colmun)
if "stitems_Type = 1" , the item is "stock with serials"
in this case all stocks information that related to this item will stored in table called "purchases_item_seriels" with this rows example :
-------------------------------------
pis_ID| stitems_ID | pis_Statues| ---
1 | 3 | 1 | ---
2 | 3 | 2 | ---
3 | 3 | 4 | ---
4 | 3 | 5 | ---
5 | 4 | 2 | ---
-------------------------------------
pis_Statues（1 =有货，2 =已售出，4 =已丢失，5 =已退回销售）因此，获取库存将计算具有此pis_Statues值的项目（1， 5,6）
作为purchases_item_seriels表，项目3的股票= 2 （pis_Statues记录的数量） colmun值是（1,5,6））和第4项的库存= 0
i希望创建将为我提供的VIEWS以及短缺项目结果：
pis_Statues (1 = in stock , 2 = sold , 4 = lost , 5 = returned sales) so , to get the stock will count the item that have this pis_Statues values (1,5,6)
as "purchases_item_seriels" table , the stock of item 3 = 2 (count of records where pis_Statues colmun value is (1,5,6)) and the stock of item 4 = 0
i want to create VIEWS that will provide me with the Shortages Items as this results :
--------------------------------------------------
stitems_ID | stitems_MinBalance | stock | ---
1 | 10 | 8 | ---
3 | 5 | 2 | ---
4 | 15 | 0 | ---
--------------------------------------------------
所以我也可以创建基于此视图的存储过程..
我尝试了什么：
我正在考虑创建一个 View1 使用 stock_noserials 加入 st_items 并创建 View2 以 purchases_item_seriels <加入 st_items / b>。但我不知道我如何创建视图来结合两个视图
现在我得到了实体框架代码的短缺但它非常慢..
so i can also create Stored Procedure that will based on this VIEW ..
What I have tried:
I'm thinking of creating a View1 to join st_itemswith stock_noserials and creating a View2 to join st_itemswith purchases_item_seriels . but i don't know how i can create view to combine the two views
now i get shortages with Entity Framework code but it is very slow ..
st_itemsBindingSource.DataSource = nDB01.st_items
.GroupBy(x => x.stitems_ID)
.Select(grp => new
{
itemID = grp.Key,
sumNoSerial = nDB01.stock_noserials.Where(u => u.stitems_ID == grp.Key).Sum(s => s.StockQnty),
sumWithSerial = nDB01.purchases_item_seriels.Where(u => (u.stitems_ID == grp.Key && u.pis_Statues == 0) ||
(u.stitems_ID == grp.Key && u.pis_Statues == 5) ||
(u.stitems_ID == grp.Key && u.pis_Statues == 6))
.Count(),
minBalance = grp.Select(s => s.stitems_MinBalance).FirstOrDefault()
})
.Where(u => u.minBalance >= (u.sumNoSerial+u.sumWithSerial)).ToList();
我也计算了这段代码：
also i get its count with this code :
//Shortages النواقص
int Shortages_StockNoSer = nDB01.stock_noserials
.GroupBy(x => x.stitems_ID)
.Select(grp => new
{
itemID = grp.Key,
sum = grp.Sum(x => x.StockQnty),
min = grp.Select(s => s.st_items.stitems_MinBalance).FirstOrDefault()
}).Where(u => u.sum < u.min && u.min != 0)
.Count();
int Shortages_StockWithSer = nDB01.purchases_item_seriels
.GroupBy(x => x.stitems_ID)
.Select(grp => new
{
itemID = grp.Key,
sum = grp.Count(),
min = grp.Select(s => s.st_items.stitems_MinBalance).FirstOrDefault()
}).Where(u => u.sum < u.min && u.min != 0)
.Count();
int CountShortagesItems = Convert.ToInt32( Shortages_StockNoSer) + Convert.ToInt32(Shortages_StockWithSer);
推荐答案
Not确定我是否正确理解了这个问题，但你可以尝试类似
Not sure if I understood the question correctly, but you could try something like
WITH CurrentAmounts AS (
SELECT sns.stitems_ID,
SUM(sns.StockQnty) AS Amount
FROM stock_noserials sns
GROUP BY sns.stitems_ID
)
SELECT si.stitems_ID,
si.stitems_MinBalance,
COALESCE(ca.Amount,0) AS Stock
FROM st_items si,
LEFT JOIN CurrentAmounts ca ON ca.stitems_ID = si.stitems_ID
WHERE COALESCE(ca.Amount,0) < si.stitems_MinBalance
要为上面的查询创建一个视图，只需将其放在 CREATE VIEW语句 [ ^ ]
附加：
没有CTE
And to create a view for the query above, just place it inside a CREATE VIEW statement[^]
ADDITION:
Without CTE
SELECT si.stitems_ID,
si.stitems_MinBalance,
COALESCE(ca.Amount,0) AS Stock
FROM st_items si,
LEFT JOIN (SELECT sns.stitems_ID,
SUM(sns.StockQnty) AS Amount
FROM stock_noserials sns
GROUP BY sns.stitems_ID) ca
ON ca.stitems_ID = si.stitems_ID
WHERE COALESCE(ca.Amount,0) < si.stitems_MinBalance
如果需要加入金额，比如
If needs to be added into amount, something like
SELECT si.stitems_ID,
si.stitems_MinBalance,
COALESCE(ca.Amount + pa.Amount,0) AS Stock
FROM st_items si,
LEFT JOIN (SELECT sns.stitems_ID,
SUM(sns.StockQnty) AS Amount
FROM stock_noserials sns
GROUP BY sns.stitems_ID) ca
ON ca.stitems_ID = si.stitems_ID
LEFT JOIN (SELECT pis.stitems_ID,
COUNT(*) AS Amount
FROM purchases_item_seriels pis
WHERE pis.pis_Statues IN (1, 5, 6)
GROUP BY pis.stitems_ID) pa
ON pa.stitems_ID = si.stitems_ID
WHERE COALESCE(ca.Amount + pa.Amount, 0) < si.stitems_MinBalance
你应该创建视图，以利用索引和数据库服务器性能。如果LINQ被设计用来处理像这样的复杂情节，即使你可以在理论上（或实践）制定它们，我会感到惊讶。
You should create the View on the database server to take advantage of indexing and database server performance. I would be surprised if LINQ was designed to handle complex scenarious like this even though you can formulate them in theory (or practice).
这篇关于如何创建复杂的VIEW并加入此表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
