C＃的SQL复杂查询 [英] SQL Complex Query for C#

问题描述

您好专家，请仔细阅读我的关注。下面是我的C＃程序的SQL查询。一旦运行了sql查询，我就会得到datagridview的结果。在下面你可以看到，我有项目表和Tran2表，我的问题是，我想为每个凭证号码的每个项目的实际数量乘以权重（不是我现在正在做的错误方式，它会变得不同价值观见下文）。根据下面的示例表值，我有两个项目A和B.我需要将来自项目表权重的A乘以Trans2表ActualQty并对休息做同样的事情，我需要得到它的和值。请参阅下面的正确方法。



请从一条SQL语句中帮助我如何做到这一点？



< pre lang =vb>正确方式（我需要 学习）
21 。 31 * 31 = 660 。 61
31 .22 * 200 = 6244
660 .61 + 6244 = 6904 。 61 =重量

错误的方式（SUM（i.Weight）* SUM（t2.ActualQty）） as 权重
  21  .31 + 31.  22  =  52 。 53  
 31 + 200 =  231  
  52  .53 * 231 =  12134 。 43  =重量

项目表
项目名称权重
 A  21 。< span class =code-digit> 31  
 B  31 。 22  

 Tran2表
 ItemName VoucherNumber ActualQty 
 A  001   31  
 B  001   200  

 选择  distinct  t1.PartyName  as  Customer_Name，SUM（t2.Amount） as  Bill_Amount，MAX（ t2.VoucherNumber） as  Invoice_Number，SUM（i.Weight）* SUM（t2.ActualQty） as 重量，SUM（i.CMB），MAX（p.RouteNo），MAX（t1.Adress3） as 城市
 来自项目i，Party p，Tran1 t1，Tran2 t2 
 其中 t1.VoucherNumber = t2.VoucherNumber 和 t2.ItemName = i.Itemname 和 p.PartyName = t1.PartyName 和 p.RouteNo = ' + comboBox1.Text +' 和 t1.LoadingStatus  IS   NULL   GROUP   BY  t1.PartyName 

解决方案

不应该只是 SUM（i.Weight * t2.ActualQty）？

 wq 
 2 3 
 4 5 
 

 SUM（ w）* SUM（q）=（2 + 4）*（3 + 5）= 6 * 8 = 48 
 SUM（w * q）=（2 * 3）+（4 * 5）= 6 + 20 = 26 

Hello Experts, please read my concern carefully. below is my sql query for my C# program. Once run the sql query, I'm getting results to the datagridview. In below you can see, I have Item table and Tran2 table, my problem is, I want to do weight multiply by ActualQty of each items for the each voucher number (not the wrong way which I'm doing now, it will get different values, see below). As per below example table value, I have two items A and B. I need to multiply A from Item table weight from Trans2 table ActualQty and do the same for rest and I need to get it's sum value. See below correct way.

Please help me how to do it from one SQL statement?

Correct Way (Which I need to learn)
21.31*31 = 660.61
31.22*200 = 6244
660.61+6244 = 6904.61 = Weight

Wrong Way (SUM(i.Weight)*SUM(t2.ActualQty)) as Weight
21.31+31.22 = 52.53
31+200 = 231
52.53*231 = 12134.43 = Weight

Item Table
Itemname Weight
A        21.31
B        31.22

Tran2 Table
ItemName   VoucherNumber    ActualQty
A              001             31
B              001             200

select distinct t1.PartyName as Customer_Name, SUM(t2.Amount) as Bill_Amount, MAX(t2.VoucherNumber) as Invoice_Number, SUM(i.Weight)*SUM(t2.ActualQty) as Weight, SUM(i.CMB), MAX(p.RouteNo), MAX(t1.Adress3) as City
from Item i, Party p, Tran1 t1, Tran2 t2
Where t1.VoucherNumber=t2.VoucherNumber and t2.ItemName=i.Itemname and p.PartyName=t1.PartyName and p.RouteNo='" + comboBox1.Text + "' and t1.LoadingStatus IS NULL GROUP BY t1.PartyName

解决方案

Shouldn't that just be SUM(i.Weight* t2.ActualQty)?

w     q
2     3
4     5

SUM(w) * SUM(q) = (2 + 4) * ( 3 + 5) = 6 * 8  = 48
SUM(w * q)      = (2 * 3) + (4 * 5)  = 6 + 20 = 26

这篇关于C＃的SQL复杂查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！