2表JOIN和显示到DATAGRIDVIEW .. [英] 2 Table JOIN and Show To DATAGRIDVIEW..

问题描述

我有两张桌子

I have Two Table

FeesMaster

REGNO		CLASS		SECTION	        NAME    FEETYPE  FEEAMOUNT
A600      	I         	A         	AAAA	Team-1	750
A700      	I         	A         	DDDDD	Team-1	750
A900      	I         	A         	EEEEE	Team-1	750
A600      	I         	A         	BBBB	Jan	650
A700      	I         	A         	DSDSDSDSDJan	650
A900      	I         	A         	EEEEE	Jan	650

Fees_Transaction	

REGNO           NAME    CLASS          SECTION         AMOUNT_PAID FEETYPE				
A600           	AAAA	I              	A              	150	Team-1         
A600           	AAAA	I              	A              	500	Team-1         
A700           	DDDDD	I              	A              	500	Team-1         
A700           	DDDDD	I              	A              	250	Team-1         
A700           	DDDDD	I              	A              	300	Jan            
A700           	DDDDD	I              	A              	350	Jan            
A900           	EEEEE	I              	A              	200	Jan            
A900           	EEEEE	I              	A              	750	Team-1 

如何加入表格之上（使用条件Regno和类匹配）我想要Diaply到DATAGRIDVIEW..Same时间Amount_Paid和FeeAmount计算并显示到DataGridView总金额..

我想要这样..

How can I JOIN above the Table (Using Condition Regno and Class is Matching) I want Diaply to DATAGRIDVIEW..Same Time Amount_Paid and FeeAmount Calculate and Display To DataGridView TOTAL AMOUNT..
I Want Like This..

Reg no	ST_Name	Amount_Paid	FeeAmount	FeeType	Class	Section
						
A600           	AAAA	650	1400	TOTAL	I	A
A700           	DDDDD  1400	1400	TOTAL	I	A
A900           	EEEEE	950	1400	TOTAL	I	A

我正在使用代码这个

I am Using Code This

Dim strSQL4 As String
    con.Open()

    strSQL4 = "Select Fees_Transaction.Regno, Fees_Transaction.ST_Name, SUM(Fees_Transaction.Amount_Paid) as Amount_Paid, Fees_Transaction.Class, Fees_Transaction.Section, SUM(FeesMaster.FeeAmount) as FeeAmount  FROM Fees_Transaction LEFT OUTER JOIN FeesMaster ON Fees_Transaction.Regno=FeesMaster.Regno AND and Fees_Transaction.FeeType=FeesMaster.FeeType WHERE Fees_Transaction.class='" & cboClass.Text & "'  and FeesMaster.class='" & cboClass.Text & "' GROUP By Fees_Transaction.Regno, Fees_Transaction.ST_Name, Fees_Transaction.Class, Fees_Transaction.Section"
    Dim DaAp4 As New SqlDataAdapter(strSQL4, con)
    Dim DSet4 As New DataTable
    DaAp4.Fill(DSet4)
    DataGridView2.DataSource = DSet4.DefaultView
    con.Close()

现在DataGRIDVIEW显示就像这样.. FEEAMOUNT计算错误...请告诉我我在哪里找到代码..为什么FeeAmount会这样来......

Reg no ST_Name Amount_Paid FeeAmount FeeType Class Section



A600 AAAA 650 1500 TOTAL I A

A700 DDDDD 1400 2800 TOTAL I A

A900 EEEEE 950 1400 TOTAL I A

Now DataGRIDVIEW SHOW Like THIS.. FEEAMOUNT CALCULATE SOME WRONG... PLEASE TELL ME Where I DID WORNG CODE..Why FeeAmount Come like this..
Reg no ST_Name Amount_Paid FeeAmount FeeType Class Section

A600 AAAA 650 1500 TOTAL I A
A700 DDDDD 1400 2800 TOTAL I A
A900 EEEEE 950 1400 TOTAL I A

推荐答案

复合查询的情况是在sql中创建一个视图并从视图中获取数据。



所以你要创建一个这样的视图：

What you can do in the cases of compound queries is create a view in sql and fetch the data from view.

So you cat create a view like this:

create view V1 as (Select Fees_Transaction.Regno, Fees_Transaction.ST_Name, SUM(Fees_Transaction.Amount_Paid) as Amount_Paid, Fees_Transaction.Class, Fees_Transaction.Section, SUM(FeesMaster.FeeAmount) as FeeAmount  FROM Fees_Transaction LEFT OUTER JOIN FeesMaster ON (Fees_Transaction.Regno=FeesMaster.Regno AND and Fees_Transaction.FeeType=FeesMaster.FeeType) GROUP By (Fees_Transaction.Regno, Fees_Transaction.ST_Name, Fees_Transaction.Class, Fees_Transaction.Section))

用a测试结果查询。说



从V1中选择*。



很容易在视图中调试问题而不是调试他们通过代码。正确显示数据后，从具有条件的视图中进行选择。

test the result with a query. say

select * from V1.

It is easy to debug the problems in view rather than debugging them through code. Once your data is shown appropriately, select from view with a condition.

这篇关于2表JOIN和显示到DATAGRIDVIEW ..的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！